Trouble in writing a code for a macro.

[samrat]

I am not able to write a code for a macro prepared for my project. In the macro I have to refer the file name e.g "20110805 - External - Clients.xls". But the file name changes every week, thus next friday the file name will be "20110812 - External - Clients.xls" so then the macro will show a debug. Could you please help me to cope with this.

Thanks
Sam

 

[Weaver]

Welcome to the board

This might help - it illustrates how you can compose a file name using date info

Sub test()
    myFileName = Format(Now(), "yyyymmdd") & " - External - Clients.xls"
    Debug.Print myFileName
End Sub

 

[wigi]

For instance, ask the filename from the user executing the macro, by:

- input in a cell
- input in a simple userform
- input in an inputbox (use Application.Inputbox)
- calculating the day (is it the day of today?)
- and so on

 

[samrat]

Thanks for your greeting and help, but the code is still not working for me. Could you please check this out whther I have quoted it in the right way or not (I am putting the code for the concerned part only):

Sub Cash_Report()
'
' Cash_Report Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
myFileName = Format(Now(), "yyyymmdd") & " - Div ID - Impayés clients.xls"
Debug.Print myFileName
Windows(" - Div ID - Impayés clients.xls").Activate
Sheets("data").Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
Range("L3").Select
ActiveWindow.FreezePanes = False
..............

 

[wigi]

but the code is still not working for me.

What line of code is not working? (Use F8 to step through the code line by line)

I suspect that you forget to use myFileName on line 3 - what for do you create that variable if you don't use it?

In line 1, Date would be slightly better than Now since you're not interested in times.

 

[samrat]

Line 3 of the code is showing as debug. And the date somehow got deleted from the Line 3 when I posted the reply, I am interested in times. Could you please write down the code for activating the workbook (i.e Line 3). Also you have used the NOw() command, I think that picks up the current date. Is it possible that the code can be developed for working in files named in any date in 'yyymmdd' format. And lastly I must admit I am really trying to learn VBA script in macros so please bear with my ignorance.
........ Samrat

 

[wigi]

IF line 1 is correct and you have such files, then line 3 should simple be:

Windows(myFileName).Activate

 

[samrat]

Thanks a ton! it worked perfectly.
It's pleasure joining the forum and will surely continue be a part or rather be a seeker for answers in the forum . And will definitely try to help with what little knowledge of mine.

Best Regards....Sam