vba complie/syntax error

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
Try
Code:
Sub filename_cellvalue()
Dim filename As String
filename = Range("B31")
ActiveWorkbook.SaveAs filename:=filename, FileFormat:=52
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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.
 

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
You're welcome
 

Forum statistics

Threads
1,085,488
Messages
5,383,956
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top