Open file

[Dummy Excel]

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:

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

 

[exceluser2007]

Hi Sam,

Bit unsure, but is this what you mean:

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?

 

[Dummy Excel]

it doesnt like it
what is "dateserial"?

 

[exceluser2007]

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]

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]

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]

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

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]

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

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]

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