Missing files  
Author Message
avalancheseah





PostPosted: Visual Basic for Applications (VBA), Missing files Top

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



Microsoft ISV Community Center Forums1  
 
 
ChasAA





PostPosted: Visual Basic for Applications (VBA), Missing files Top

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


 
 
avalancheseah





PostPosted: Visual Basic for Applications (VBA), Missing files Top

HI,

thank you veri much. This is helpful.