I'm new to excel and have had a hang up with the MkDir feature. I would like to check if a folder exists based on a certain cell value, and then create the directory if it does not exist. This is what I have so far.
Dim newFile As String Dim Path As String newFile = Range("D5").Value & " " & "op" & Range("B200").Value & " " & Format$(Date, "yyyy-mm-dd") Path = Range("A210").Value
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
I'm sure this will be an easy question, but I've been searching for awhile and can't seem to find an answer. I'm trying to write code in VBA that will check if a directory within c:documents and settings...my documents exists, and then save a file to that directory. The problem is, I'm having trouble finding the my documents directory. Is there some object or property that I could use to find the my documents path? Note that the worksheet that I'll be working with is in a separate directory, and therefore neither activeworkbook.path nor thisworkbook.path would be an option.
i would like to know whether a worksheet exists or not... say, the worksheet name is sheet1, i need to check whether the worksheet exists... the point is, i need to delete the worksheet if it exists and execute a code... if the worksheet doesn't exist also, i need to execute the code... so im getting a little confused with this... i need something like this...
if sheet1_exists then delete the sheet end if my_code.......
I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!
I have a huge database consisting of zipped file names which i created by importing the directory listing into excel. The zipped files contain two files each an mp3 and a correspondent cdg file (karaoke files).
I continue to add new disks (as I acquire them) to my collection. the trouble is that new disks have some songs already in my collection and I do not want to waste space in hard drive with duplicates. The existing data is arranged in a worksheet by columns as follows:
Discnumber | track number| artist | song name | path | complete file name
what I would like to do is to check the ** listing after import it to excel in the same format (i can do this part ) and then check it against the 10,000 plus entries that i have if the artist-song name combination already exists in the data. If so return the row in a separate worksheet or pivot table so that i can check it. this way I would only save the songs I do not have in my hard drive thus avoiding duplicates and saving precious space.
I have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?
I am writing a macro for a my team. I will distribute the .bas file then have them run it. I don't know what their individual sheets in their workbook are called so I need to ask them. Grab various columns from that sheet, check if they have a sheet called data import, then put in the columns in order in data import.
Code: Private Sub checkForSheet() Dim sh On Error Resume Next sh = Worksheets("Data Import").Name If Not Err.Number = 0 Then Sheets.Add.Name = "Data Import" On Error GoTo 0 End Sub
to check if Data import is made
Code: Sub prepareData() Dim SCMsheet As String, TSE As String Dim DISh As Worksheets SCMsheet = InputBox("Enter your SCM Sheet name in entirety.") If SCMsheet vbNullString Then
But it doesnt stop scmsheet is not in the workbook, and the values arent put in.
I have written a routine that automatically saves the file to a specified folder as the name of a cell from one of the worksheets. I am trying to use the following code to see if that filename already exists and stop the routine if it does. If I manually enter an existing filename the routine works well, however I cannot get it to recognise the automatically created filename, here is my .....
I am writing a macro which first needs to look and see if a certain worksheet exists. If it doesn't then I want it to create it but if it does I first want it to delete the sheet then create a blank one with the same name. At trhe moment I have this but it is scrappy and doesn't work very well:
On Error Goto AddSheet If Sheets("MONEYSHEET").Activate Then Goto Data
I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories? Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs"
Dim AppPath As String: AppPath = ActiveWorkbook.Path 'other code in here - activeworkbook changed, hence previous line If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _ "" & NameString & ".xls" Else MkDir AppPath & "" & StoredJobsSubdirectory ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _ "" & NameString & ".xls" End If............................
I know this is my third thread, but I have made sure that I have trawled through other threads before posting. I have created some VBA to collect data from multiple workbooks and paste them into one workbook, Basically the copy and paste script runs according to how many tabs are in the summary workbook because each tab ( sheet) is essentially a condensed version of a workbook. Unfortunately some workbooks I am getting the info from don't have the right data that the copy and paste program is looking for or sometimes the document doesn't exist, is there anyway of telling my VBA to pass this tab if the document doesn't exist instead of throwing up a VBA debug error?
I'm looking to use the value from a series of dropdowns (made via data validation lists).
Cell AM5 is a dropdown of named ranges made form a seperate sheet Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.
I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.
I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook) When the users insert a new picture he has to set a name for each of them. A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.
But I have a bug if a shape doesn't exist when I try to select it :
Sheets("fiche de controle").Select 'select the sheet with the pictures ActiveSheet.Shapes(Item_old).Select 'select the shapes "Item_Old" Item_Old is a variable corresponding to the picture name.
If the user made a typo, I have a bug. Someone knows how to test if the shapes exist to display a meesage if not ? Or somethig to avoid this kind of bug ?