vba complie/syntax error

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,397
Office Version
  1. 2007
I'm getting a compile/syntax error when i try to run this macro. Can you see what i've done wrong please?

Sub filename_cellvalue()
Dim Path As String
Dim filename As String
filename = Range("B31")
ActiveWorkbook.SaveAs Filename:=filename,
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

The code is stored in sheet 3 where its run from and sheet 3 B31 contains a string withe the full path i.e.:

C:\Users\john\Documents\joe_bloggs_VN287447_EPL Man Utd vs Chelsea.xlsm


I'm trying to save the workbook with a custom filename based on its contents.

Thanks!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try
Code:
Sub filename_cellvalue()
Dim filename As String
filename = Range("B31")
ActiveWorkbook.SaveAs filename:=filename, FileFormat:=52
End Sub
 
Upvote 0
The only thing that appears wrong is that the code for SaveAs should be all on one line.

Code:
Sub filename_cellvalue()
Dim Path As String
Dim filename As String
filename = Range("B31")
ActiveWorkbook.SaveAs Filename:=filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

If you did want it over multiple lines you could use the underscore line continuation character.

There is another 'mistake' but it wouldn't actually cause a 'compile' error, that's the use of 'filename' for a variable name.

That kind of clashes with the named argument Filename of SaveAs but shouldn't cause a problem

You could always use another name for the variable, e.g. strFilename
Code:
Sub filename_cellvalue()
Dim strPath As String
Dim strFilename As String

    strFilename = Range("B31")

    ActiveWorkbook.SaveAs filename:=strFilename, _
                      FileFormat:=xlOpenXMLWorkbookMacroEnabled
                      
End Sub

PS I notice you are declaring Path but not using it, that could also be a potential problem.
 
Upvote 0
Thanks guys.

The only thing that appears wrong is that the code for SaveAs should be all on one line.

Code:
Sub filename_cellvalue()
Dim Path As String
Dim filename As String
filename = Range("B31")
ActiveWorkbook.SaveAs Filename:=filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

If you did want it over multiple lines you could use the underscore line continuation character.

There is another 'mistake' but it wouldn't actually cause a 'compile' error, that's the use of 'filename' for a variable name.

That kind of clashes with the named argument Filename of SaveAs but shouldn't cause a problem

You could always use another name for the variable, e.g. strFilename
Code:
Sub filename_cellvalue()
Dim strPath As String
Dim strFilename As String

    strFilename = Range("B31")

    ActiveWorkbook.SaveAs filename:=strFilename, _
                      FileFormat:=xlOpenXMLWorkbookMacroEnabled
                      
End Sub

PS I notice you are declaring Path but not using it, that could also be a potential problem.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top