Opening an Excel file of the format "Oct data.xls"

Hi everyone,

I am writing a spread*** to compare two months worth of data from a
mainframe generated dataset.

The program works fine at the moment but I have to rename the two files
being compared to:

"lastMonthsData.xls" and "thisMonthsData.xls"

I would like to be able to use the files as they arrive in the format:

"Oct Data.xls" and "Nov Data.xls"

The problem is they dont always arrive at the same time of the month.

For this reason I would like to check to see if there is a file for the
current month and if not to use the file from the month before as the
current file and the file from two months before as the previous.

Hope that makes sense!

I have included my attempt at the code below but with no success (I stole
the bFileExists function from the web but I can't find the site again now).

If you can modify my code to make it work /write a simpler code or even just
point me in the right direction I would be very grateful.

Thanks in advance,

John

------------------------------------------



thedate = Date

tester = 0

Do While tester = 0
WorkbookToOpen = "G:\Production Control\Core Logs 6G3\KP\Cyborg
reports\Functional changes\Res Man and Finance (Air Systems) " &
Left(Format(thedate, "mmm"), 3) & " 2006.xls"
If bFileExists(WorkbookToOpen) = False Then
thedate = thedate - 30
Else: tester = 1
End If
Loop

Workbooks.Open Filename:=WorkbookToOpen

Function bFileExists(rsFullPath As String) As Boolean
bFileExists = CBool(Len(Dir$(rsFullPath)) > 0)
End Function


.

Quantcast