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
Missing files
NeederOfVBHelp
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
KarthikNarasimhan
HI,
thank you veri much. This is helpful.