Saving as a macro enabled workbook using VBA code

[Psiduke]

right ho,

may be incredibly straightforward this (I hope it is) I'm using excel 2007 - i've created a macro enabled template file, and I'm wanting to include some code that will allow me to automatically save in a folder location using the name from a cell, but I still want to be able to use macros in the saved spreadsheet, i assumed it would be a simple case of changing the file extension to .xlsm, but this returns an error, i can get the file to save as a standard workbook, so i'm a bit lost - below is the code i've been using

Sub SaveBook()
Dim sFile As String
sFile = Range("J8") & ".xlsx"
ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile
End Sub


any help most appreciated!

Cheers

 

[VoG]

Try

Sub SaveBook()
Dim sFile As String
sFile = Range("J8").Value & ".xlsm"
ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile, FileFormat:=52
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile, FileFormat:=52

In Excel 2007-2010, SaveAs requires both the FileFormat parameter and the correct file extension.

 

[Psiduke]

Both of the FileFormat:=52 return a Run-time error '1004'

method 'saveAs' of object '_Workbook' failed

at a loss??

 

[VoG]

What is in J8?

 

[Psiduke]

J8 is the name that I want the spreadsheet to save as, it's Week commencing and then the date 13.10.11

 

[VoG]

Then maybe

Sub SaveBook()
Dim sFile As String
sFile = "Week commencing " & Format(Date, "dd-mm-yy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile, FileFormat:=52
End Sub

 

[Psiduke]

that does work thanks, but i need it to refer to the cell reference as part of the save name,

sorry to be a pain....

 

[Andrew Poulsom]

Replace the dots in the date in J8 with dashes.

 

[Psiduke]

that's sorted it thanks, although i'm also using the other coding VoG now too on a different sheet!

Cheers guys