Is this possible?

[StrangeLuck]

I have a series of file names which have the naming convention mmmddblahblah.xls, whereby mmm refers to the first 3 letters of the month and dd refers to the day of that month. For example, the file for April 7 would be named apr07blahblah.xls.

My question is: Is there a way I could write a macro to open up 7 sequential files (ie. April 27 - May 3) based upon the order of the months and their respective number of days, using the mmmdd part of their names?

Thanks!
StrangeLuck

 

[Anne Troy]

I'm sure it's possible somehow. One of the questions that you'll probably be asked is from which date we'd be starting? Would you want to be able to determine that each time or would it start with, for instance, yesterday?

 

[StrangeLuck]

On 2002-04-29 11:20, Dreamboat wrote:
I'm sure it's possible somehow. One of the questions that you'll probably be asked is from which date we'd be starting? Would you want to be able to determine that each time or would it start with, for instance, yesterday?

This would be determined by the calendar weeks. Essentially, I want to have the user input the end date for the week and have the workbooks for the entire week open.

Thanks,
StrangeLuck

 

[Mark O'Brien]

Something like this would work (note:: there's some strange stuff in this, someone might want to debug this even more, but it does what you ask):

<pre>
Public Sub main()
Dim oDate As Date
Dim i As Single
Dim sFileName As String

oDate = #4/27/2002#

For i = 1 To 7
oDate = oDate + 1
sFileName = MonthName(Month(oDate), True) & Format(Day(oDate), "dd") & "Blahblah.xls"
MsgBox sFileName 'Replace this with a "open workbook" statement using sFileName
Next
End Sub</pre>

The "strange stuff" is partly to do with the way Excel handles dates and partly to do with the "Format" statement. For some reason, the Format statement was giving my "26" for the first day.

Anyway, I hope this helps.

 

[dhoffman]

What are you storing in those files? Is it just some generic data in a particular format in each workbook? Have you ever considered storing the data in these files in a single Access database and using queries to load relevant data into a single workbook at runtime?

 

[StrangeLuck]

On 2002-04-29 11:34, Mark O'Brien wrote:
Something like this would work (note:: there's some strange stuff in this, someone might want to debug this even more, but it does what you ask):

<pre>
Public Sub main()
Dim oDate As Date
Dim i As Single
Dim sFileName As String

oDate = #4/27/2002#

For i = 1 To 7
oDate = oDate + 1
sFileName = MonthName(Month(oDate), True) & Format(Day(oDate), "dd") & "Blahblah.xls"
MsgBox sFileName 'Replace this with a "open workbook" statement using sFileName
Next
End Sub</pre>

The "strange stuff" is partly to do with the way Excel handles dates and partly to do with the "Format" statement. For some reason, the Format statement was giving my "26" for the first day.

Anyway, I hope this helps.

It appears that I cannot use the "MonthName" function, which must be a Excel 2000/2002 option. I am using Excel 97, unfortunately. Can anyone help me further?

 

[StrangeLuck]

On 2002-04-29 11:42, dhoffman wrote:
What are you storing in those files? Is it just some generic data in a particular format in each workbook? Have you ever considered storing the data in these files in a single Access database and using queries to load relevant data into a single workbook at runtime?

Thanks for your input, but I really need to keep these files in the format they currently are. It's work-related.

 

[Mark O'Brien]

Well, since you used a smiley, I've created a stunted Excel 97 version of the function copy and paste this into your module:

Private Function MonthName97(ByVal iMonth As Integer)

<pre>
Select Case iMonth

Case 1
MonthName97 = "Jan"
Case 2
MonthName97 = "Feb"
Case 3
MonthName97 = "Mar"
Case 4
MonthName97 = "Apr"
Case 5
MonthName97 = "May"
Case 6
MonthName97 = "Jun"
Case 7
MonthName97 = "Jul"
Case 8
MonthName97 = "Aug"
Case 9
MonthName97 = "Sep"
Case 10
MonthName97 = "Oct"
Case 11
MonthName97 = "Nov"
Case 12
MonthName97 = "Dec"
End Select

End Function</pre>

Then use this line of code (instead of the old one:

<pre>

sFileName = MonthName97(Month(oDate)) & Format(Day(oDate), "dd") & "Blahblah.xls"</pre>

HTH

 

[StrangeLuck]

On 2002-04-29 12:04, Mark O'Brien wrote:
Well, since you used a smiley, I've created a stunted Excel 97 version of the function copy and paste this into your module:

Private Function MonthName97(ByVal iMonth As Integer)

<pre>
Select Case iMonth

Case 1
MonthName97 = "Jan"
Case 2
MonthName97 = "Feb"
Case 3
MonthName97 = "Mar"
Case 4
MonthName97 = "Apr"
Case 5
MonthName97 = "May"
Case 6
MonthName97 = "Jun"
Case 7
MonthName97 = "Jul"
Case 8
MonthName97 = "Aug"
Case 9
MonthName97 = "Sep"
Case 10
MonthName97 = "Oct"
Case 11
MonthName97 = "Nov"
Case 12
MonthName97 = "Dec"
End Select

End Function</pre>

Then use this line of code (instead of the old one:

<pre>

sFileName = MonthName97(Month(oDate)) & Format(Day(oDate), "dd") & "Blahblah.xls"</pre>

HTH

This would work, but I wanted to be able to have the user specify oDate everytime the macro runs.

Thanks,
StrangeLuck

 

[Mark O'Brien]

Use this:

<pre>
oDate = InputBox("Enter a date, mm/dd/yyyy, e.g. 04/23/2002", "Date")</pre>