Check Folder For Files

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Dave Peterson]

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

 

[Guest]

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]

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