Results 1 to 5 of 5

Thread: vba complie/syntax error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,385
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default vba complie/syntax error

    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!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,183
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: vba complie/syntax error

    Try
    Code:
    Sub filename_cellvalue()
    Dim filename As String
    filename = Range("B31")
    ActiveWorkbook.SaveAs filename:=filename, FileFormat:=52
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: vba complie/syntax error

    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.
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,385
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba complie/syntax error

    Thanks guys.

    Quote Originally Posted by Norie View Post
    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.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,183
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: vba complie/syntax error

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •