Open file

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
994
Hi All,

I have a question - I have a report that is run within the first week of the new month which the data is for the previous month.

I need to open the previous month report to get the running history. All the filenames are the same except it has the month and year at the end of it eg "xxx Monthly Report Jun 08.xls"
my code so far is:
Rich (BB code):
Sub Example()
    Workbooks.Open GetFile("S:\UW&H OF Reports\")
End Sub
 
Function GetFile(Path As String) As String
    Dim f As Object, d As Date
 
    If Customer = "xxxx" Or Customer = "xxx xxx" Then f = "xxx Monthly Report Jun 08.xls"
    For Each f In CreateObject("Scripting.FileSystemObject").GetFolder(Path).Files
        If d < f.DateLastModified Then
            d = f.DateLastModified
            GetFile = f.Path
        End If
    Next
End Function
How can I change the bold so that it picks up the previous month/year to the current month/year? hope that makes sense?
thanks
Sam
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
Hi Sam,

Bit unsure, but is this what you mean:

Code:
f = "xxx Monthly Report " & [B]Format(dateserial(year(now)-1,month(now),day(now)),"mmm yy")[/B] ".xls"

For the relevant section that you requested?
 

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
dateserial((year, month, day) is VBA for the date(year, month, day) excel function.

Funny, it didn;t work for you. I tested it and it worked for me.

If you enter this, as is in your immediate window what do you get?

?"xxx Monthly Report " & Format(dateserial(year(now)-1,month(now),day(now)),"mmm yy") ".xls"
 

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365

ADVERTISEMENT

dateserial((year, month, day) is VBA for the date(year, month, day) excel function.

Funny, it didn;t work for you. I tested it and it worked for me.

If you enter this, as is in your immediate window what do you get?

?"xxx Monthly Report " & Format(dateserial(year(now)-1,month(now),day(now)),"mmm yy") ".xls"
 

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
994
i got:
xxx Monthly Report Jul 07.xls

Does it matter if the code is uppercase and the file name is like the above?

Also I decare customer as string (which is xxx) at the begining of my code how can I bring that down into my function? I can get it in my example sub, just not the function
 

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365

ADVERTISEMENT

Mate, found the issue, try this and adapt accordingly for your purposes.

Code:
Sub test()

    MsgBox "xxx Monthly Report " & Format(DateSerial(Year(Now) - 1, Month(Now), Day(Now)), "mmm yy") [B]&[/B] ".xls"

End Sub

I'd forgotten to put in an & in my previous post. I'd tested it in the immediate window as per my previous post. Funny it didn't throw and error - it seems to concatenate strings without & symbols, useful to know.

HTH
 

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
i got:
xxx Monthly Report Jul 07.xls

Does it matter if the code is uppercase and the file name is like the above?

Sorry slightly confused on this, could you please clarify further. See if my previous post helps.

i got:
Also I decare customer as string (which is xxx) at the begining of my code how can I bring that down into my function? I can get it in my example sub, just not the function

Could you possible declare the customer as a Private variable at the top of your module like
Code:
Private Const mstr_customer As String = "Abcd"

Then in your Function, just refer to your customer as mstr_customer instead of "xxxx".

I'm not sure if this is what you were after exactly.

HTH
 

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
994
OK
the & did the trick thanks

My issue now is that I need to keep customer as a constant string through my subs.
I have customer as an inputbox so the user can use the same macro for multiple customers.

I tried your private function and mstr_customer = abcd, i need it to equal the inputbox
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,987
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top