Excel - VBA - how to make sure that the loop continues even if a file is not there (1 Viewer)

[vid]

Hello ,

I ve made a macro so that every week a master excel file gets updated with daily data. these daily data are in individual excel files named 140610 , 150610 and so on .. depending on the date .
Currently if in the folder there is the master file and 3 individual date excel files ( 140610 150610 160610 ) the macro runs and the data is copied onto the master file.
For this i give the user the option to input the starting and ending dates . But if there is a date missing in the middle then the macro does not run and gives an error message. I want the macro to run even if there are missing dates which would be the case ( most likely weekends ) .
Say . if in the folder with the master file there are individual files ( 140610 , 150610 , 170610 ) . i want to input 14 and 17 as the starting and ending dates respectively and get the data copied ..
the macro is used is below .
Kindly make changes to it where deemed feasible as i am stuck .


Sub DMI_ImportData()
Dim startDate As String
Dim endDate As String
Dim fileName As String
Dim lastRowDestFile As Integer
Dim lastRowSrcFile As Integer
Dim filePath As String
Dim myRange As Range

startDate = InputBox("Enter Start Date [Format: ddmmyy]", "Enter Start Date !", Application.WorksheetFunction.Text(Now, "ddmmyy"))
endDate = InputBox("Enter End Date [Format: ddmmyy]", "Enter End Date !", Application.WorksheetFunction.Text(Now, "ddmmyy"))

startDate = Trim(startDate)
endDate = Trim(endDate)


For i = Val(Left(startDate, 2)) To Val(Left(endDate, 2))
fileName = "CRTP(" & i & Right(startDate, Len(startDate) - 2) & ").xls"


'filePath = StrReverse(Replace(StrReverse(ThisWorkbook.Path), ThisWorkbook.Name, ""))


Workbooks.Open (ThisWorkbook.Path & "\" & fileName)
Worksheets(1).Select
Range("B2").End(xlDown).Select
lastRowSrcFile = Selection.Row
Set myRange = Range(Cells(2, 1), Cells(lastRowSrcFile, 12))
myRange.Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("CREXTP").Select
Range("B2").End(xlDown).Select
lastRowDestFile = Selection.Row
Cells(lastRowDestFile + 1, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(fileName).Close


Next i
MsgBox ("Data imported from Date " & startDate & " to Date " & endDate & " successfully !")
End Sub

 

[namliam]

How about you use the DIR function to check if the file exists before opening it.
If not exists then pick up the next...

Note also with your current code you will have a problem if the date is 09 or 9, this may break

 

[JPearson]

Public Function FolderExists(filename As String) As Boolean

If Not Dir(filename, vbNormal) = vbNullString Then FolderExists = True
End Function

 

[vid]

Hey ,
thanks a lot .
got it to work
Thanks again
Cheers