vba complie/syntax error

[merlin777]

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]

Try

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

 

[Norie]

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

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

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]

Thanks guys.

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

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

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]

You're welcome