Opening file with changing monthly date extension

[Tone]

Each day, a new file is created and named with a date extension. I would like
my macro to open the last day's file from the previous month
(20060531_Volume). My problem is that, every month, this would change (due to
the date extension) and that every month the location of the file I want to
open changes as our system creates a new folder for each month as the month
is finished. This would mean 20060430_Volume would be in the 200604 Folder,
20060531_Volume would be in the 200605 Folder and so on. I would like the
macro to work so that every month it would open the correct file without me
changing anything in the macro. Is this possible? Any help would be greatly
appreciated.

 

[Karl E. Peterson]

Each day, a new file is created and named with a date extension. I
would like my macro to open the last day's file from the previous
month (20060531_Volume). My problem is that, every month, this would
change (due to the date extension) and that every month the location
of the file I want to open changes as our system creates a new folder
for each month as the month is finished. This would mean
20060430_Volume would be in the 200604 Folder, 20060531_Volume would
be in the 200605 Folder and so on. I would like the macro to work so
that every month it would open the correct file without me changing
anything in the macro. Is this possible? Any help would be greatly
appreciated.

Sure it's possible. You already have the month and year, right? So, you
can build the path like this:

Path = CStr(MyYear) & Format$(MyMonth, "00") & "_Volume"

To determine the last day of the month, take the 0-day of the next month:

Public Function LastDay(ByVal Y As Long, ByVal M As Long) As Long
LastDay = Day(DateSerial(Y, M + 1, 0)
End Function

Later... Karl

 

[Tone]

Hi Karl,

Thanks for the input. The problem is, every month, it has to open the the
last file for the previous month. So for this month it would be
20060531_Volume, next month it would have to open 20060630_Volume, and so on
(it changes every month). Also, after having the path, what's the command to
actually open the file? Thanks again.

Tone

 

[Karl E. Peterson]

Thanks for the input. The problem is, every month, it has to open the
the last file for the previous month. So for this month it would be
20060531_Volume, next month it would have to open 20060630_Volume,
and so on (it changes every month).

Why is that a problem? You can calculate that string with ease, using the
formula I provided below:

I suppose you could modify that to more closely match your exact criteria,
with something like this:

Public Function LastDayLastMonth() As Long
LastDayLastMonth = _
CStr(Year(Now)) & _
Format$(Month(Now), "00") & _
Day(DateSerial(Year(Now), Month(Now), 0))
End Function

Make sense?

Also, after having the path,
what's the command to actually open the file? Thanks again.

Depends what sort of file it is, and what you intend to do with it. If it's
a text file, and you just wanted to (for example) slurp it up all at once,
you could do something like this:

Public Function ReadFile(ByVal FileName As String) As String
Dim hFile As Long
On Error GoTo Hell
hFile = FreeFile
Open FileName For Binary As #hFile
ReadFile = Space$(LOF(hFile))
Get #hFile, , ReadFile
Close #hFile
Hell:
End Function

There are millions of variations on that theme, of course. It's up to you
to define your needs, and from that a solution will become more obvious.