Save Workbook As macro problem

D

D Zandveld

Hi, I have the following code to save the current workbook with a specific
filename.
It is linked to a button launched from a custom menu.

Sub Save_File()
' Macro to Save file as ZFPW_dd-mm-yyyy.xls
Dim path As String, filename As String, filetype As String, file As String
path = "C:\Documents and Settings\username\My Documents\SOX
Reporting\Metrics"
file_name = "ZFPW_" & Format(Date, "dd-mm-yyyy")
filetype = ".xls"
file = path & file_name & filetype
ChDir path
ActiveWorkbook.SaveAs filename:=file
End Sub

When I run the macro, I get "Compile error: Wrong number of arguments or
invalid property assignment" with Format highlighted as the error.

I'm sure it's a minor fix, but it's not giving me any love at the moment.

Thanks
 
B

Barb Reinhardt

I'm not seeing a problem here. What do you get when you do this

Debug.print Date
Debug.print Format(Date, "dd-mm-yyyy")
 
D

D Zandveld

Same error, but this time on Format in the debug code.

Do I need to declare 'Format' as anything? I didn't think so?
 
B

Barb Reinhardt

What application are you running this out of? I'm using Excel 2007. Might
you be running it from something besides Excel?
 
D

D Zandveld

Excel 2003

Barb Reinhardt said:
What application are you running this out of? I'm using Excel 2007. Might
you be running it from something besides Excel?
 
B

Barb Reinhardt

It'll take me a while to access Excel 2003, but you might try replacing
Format with WorksheetFunction.Text( )
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top