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
|