Hi,

I'm trying to consolidate data from a few files (eg 01Aug.xls, 02Aug.xls, 03Aug.xls ....etc). For dates that fall on a Sat, Sun or public holiday, there will not be any file. I have the below VBA code. I put in the dates in column A of excel sheet, and the files are saved in C:\Documents and Settings\Simon\Desktop\VBA\VBE\.

My question is, how do i make it work such that if the file is not available, the macro will select the go to the next date and carry on till it reaches the last date Thanks.

Sub Macro1()

Dim filedate As String
Dim times As Integer

Do
times = times + 1
filedate = Range("A" & times).Value
Workbooks.Open Filename:= _
"C:\Documents and Settings\Simon\Desktop\VBA\VBE\" & filedate & ".xls"
Range("B2").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("B" & times).Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
ActiveWindow.Close
Range("A1").Select
'End If
Loop While times < 10

End Sub

Hello,

You could check to see if the particular file exists before you open it.

ThisFile="C:\Documents and Settings\Simon\Desktop\VBA\VBE\" & filedate & ".xls"

fileFound=Dir(thisfile)

if filefound<> "" then

process your code

else

do your increments and make up next file name etc

endif

Basically, if filefound is "" then file does not exist

You will have to change your Do... Loop logic a bit

Chas

HI,

thank you veri much. This is helpful.