Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Hi Guys,
I'd like to do the following in a macro: Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() 'HELP HERE End Sub I want to then check the folder for files: file1.xls file245.xls file278.xls file88.xls etc. so they'll be something like foldername1path & "\" file1.xls (AM I RIGHT?) If one or more of these files do not exist, I would like a MsbBox to show with something like "File1.xls and File88.xls not found". Thanks in advance Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Dave,
Paste this code in and it does it for the named fole File1.xls. To loop through several files and check for their existence you could put the names to search for an a worksheets and loop through that. If you stuck in doing that post back. Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() If Dir(foldername1path & "book1.xls") < "" Then MsgBox "File exists" Else MsgBox "File does not exist" End If End Sub Mike "Dave" wrote: Hi Guys, I'd like to do the following in a macro: Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() 'HELP HERE End Sub I want to then check the folder for files: file1.xls file245.xls file278.xls file88.xls etc. so they'll be something like foldername1path & "\" file1.xls (AM I RIGHT?) If one or more of these files do not exist, I would like a MsbBox to show with something like "File1.xls and File88.xls not found". Thanks in advance Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Try this out, i built it as a function and put a test to it, you just need to
pass in the filenames in a loop and if it does not exist it will add it to a string Dim mystring As String Function checkForFiles(filename) Dim mystring As String Dim oFSO As Object Dim Folder As Object Dim Files As Object Dim file As Object MsgBox ("Select the Folder") Set oFSO = CreateObject("Scripting.FileSystemObject") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() 'MsgBox oFSO.fileexists(foldername1path & "\" & filename) If oFSO.fileexists(foldername1path & "\" & filename) = "False" Then checkForFiles = filename End Function Sub main() Dim myReturn As String myReturn = checkForFiles("book1.xls") If myReturn < "" Then mystring = mystring & myReturn MsgBox mystring End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Dave" wrote: Hi Guys, I'd like to do the following in a macro: Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() 'HELP HERE End Sub I want to then check the folder for files: file1.xls file245.xls file278.xls file88.xls etc. so they'll be something like foldername1path & "\" file1.xls (AM I RIGHT?) If one or more of these files do not exist, I would like a MsbBox to show with something like "File1.xls and File88.xls not found". Thanks in advance Dave |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Hi Mike H,
I don't really know how to produce a loop. I have a total of 24 files I want to find in a folder, all with set names. Could you explain how I could loop through these? Also, if a file exists, I don't want anything to show - only when a file doesn't exist do I need a popup. Thanks! Dave "Mike H" wrote: Dave, Paste this code in and it does it for the named fole File1.xls. To loop through several files and check for their existence you could put the names to search for an a worksheets and loop through that. If you stuck in doing that post back. Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() If Dir(foldername1path & "book1.xls") < "" Then MsgBox "File exists" Else MsgBox "File does not exist" End If End Sub Mike "Dave" wrote: Hi Guys, I'd like to do the following in a macro: Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() 'HELP HERE End Sub I want to then check the folder for files: file1.xls file245.xls file278.xls file88.xls etc. so they'll be something like foldername1path & "\" file1.xls (AM I RIGHT?) If one or more of these files do not exist, I would like a MsbBox to show with something like "File1.xls and File88.xls not found". Thanks in advance Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Dim myNames as variant
dim wkbk as workbook dim myPath as string dim iCtr as long mynames = array("filename1.xls", _ "filename2.xls", _ "filename24.xls") 'you'd type in all 24 of those names mypath = "C:\my documents\excel" 'or whatever you used to get the path if right(mypath,1) < "\" then mypath = mypath & "\" end if for ictr = lbound(mynames) to ubound(mynames) set wkbk = workbooks.open(filename:=mypath & mynames(ictr) 'do stuff with wkbk wkbk.close savechanges:=false 'or true?? next ictr Dave wrote: Hi Mike H, I don't really know how to produce a loop. I have a total of 24 files I want to find in a folder, all with set names. Could you explain how I could loop through these? Also, if a file exists, I don't want anything to show - only when a file doesn't exist do I need a popup. Thanks! Dave "Mike H" wrote: Dave, Paste this code in and it does it for the named fole File1.xls. To loop through several files and check for their existence you could put the names to search for an a worksheets and loop through that. If you stuck in doing that post back. Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() If Dir(foldername1path & "book1.xls") < "" Then MsgBox "File exists" Else MsgBox "File does not exist" End If End Sub Mike "Dave" wrote: Hi Guys, I'd like to do the following in a macro: Sub MACRO() MsgBox ("Select the Folder") Set foldername1 = Application.FileDialog(msoFileDialogFolderPicker) foldername1.Show foldername1path = CurDir() 'HELP HERE End Sub I want to then check the folder for files: file1.xls file245.xls file278.xls file88.xls etc. so they'll be something like foldername1path & "\" file1.xls (AM I RIGHT?) If one or more of these files do not exist, I would like a MsbBox to show with something like "File1.xls and File88.xls not found". Thanks in advance Dave -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Thanks Dave,
So far, I have this: Sub Checks() Dim myNames As Variant Dim wkbk As Workbook Dim myPath As String Dim iCtr As Long Set something = Application.FileDialog(msoFileDialogFolderPicker) MsgBox "Select the SystmOne GMS Files (Originals)", vbInformation something.Show somethingpath = CurDir() myNames = Array("WORKBOOKONE.xls", "WORKBOOKEIGHT.xls", "WORKBOOKNINE.xls") 'you'd type in all 24 of those names myPath = somethingpath 'or whatever you used to get the path If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If For iCtr = LBound(myNames) To UBound(myNames) Set wkbk = Workbooks.Open(Filename:=myPath & myNames(iCtr)) 'do stuff with wkbk wkbk.Close savechanges:=False 'or true?? Next iCtr End Sub When a file is found, I guess it skips to the next. However when a file is not present, it just stops the Macro with an error. I want it to show a message e.g. "WORKBOOKNINE.xls Not Found" and then once the user clicks ok it will continue. Can you (or anyone) please help? Thanks again!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check Folder For Files
Option Explicit
Sub Checks() Dim myNames As Variant Dim wkbk As Workbook Dim myPath As String Dim iCtr As Long MsgBox "Select the SystmOne GMS Files (Originals)", vbInformation With Application.FileDialog(msoFileDialogFolderPicker) ' Optional: set folder to start in .InitialFileName = "C:\my documents\excel\" .Title = "Select the folder to process" If .Show = True Then myPath = .SelectedItems(1) 'add trailing backslash myPath = myPath & "\" Else MsgBox "Try later!" Exit Sub End If End With myNames = Array("WORKBOOKONE.xls", _ "WORKBOOKEIGHT.xls", _ "WORKBOOKNINE.xls") For iCtr = LBound(myNames) To UBound(myNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myPath & myNames(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox myPath & myNames(iCtr) & " was not opened!" & vbLf & _ "Maybe it doesn't exist???" Else 'do stuff with wkbk MsgBox wkbk.Worksheets(1).Range("a1").Text wkbk.Close savechanges:=False 'or true?? End If Next iCtr End Sub Dave wrote: Thanks Dave, So far, I have this: Sub Checks() Dim myNames As Variant Dim wkbk As Workbook Dim myPath As String Dim iCtr As Long Set something = Application.FileDialog(msoFileDialogFolderPicker) MsgBox "Select the SystmOne GMS Files (Originals)", vbInformation something.Show somethingpath = CurDir() myNames = Array("WORKBOOKONE.xls", "WORKBOOKEIGHT.xls", "WORKBOOKNINE.xls") 'you'd type in all 24 of those names myPath = somethingpath 'or whatever you used to get the path If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If For iCtr = LBound(myNames) To UBound(myNames) Set wkbk = Workbooks.Open(Filename:=myPath & myNames(iCtr)) 'do stuff with wkbk wkbk.Close savechanges:=False 'or true?? Next iCtr End Sub When a file is found, I guess it skips to the next. However when a file is not present, it just stops the Macro with an error. I want it to show a message e.g. "WORKBOOKNINE.xls Not Found" and then once the user clicks ok it will continue. Can you (or anyone) please help? Thanks again!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if a folder has x files in it. | Excel Discussion (Misc queries) | |||
Copying all files in a folder to new folder | Excel Discussion (Misc queries) | |||
How to check if a file exists in an ftp folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
Files and folder library | Excel Discussion (Misc queries) |