none
SaveAs issue in VBA RRS feed

  • Question

  • Hi expert,

      I wrote the code to save as worksheet. but got error message is this.

    "Run-Time error '1004', Method 'SaveAs' of object '-workbook' failed. i'm dummies in VBA, so I don't know how to fix it. 

    Code:

    Sub XLSXOutput()
        Dim wb As Workbook, InitFileName As String, fileSaveName As String
        InitFileName = "C:\Temp" & "\ - PECO_Output_ " & Format(Date, "mmddyyyy")
        Sheets("Sheet1").Range("A:K").Copy
        Set wb = ActiveWorkbook
        fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, filefilter:="Excel files , *.xlsx")
        With wb
            If fileSaveName <> "False" Then
                .SaveAs fileSaveName
                .Close
            Else
                .Close False
                Exit Sub
            End If
        End With

    ThisWorkbook.Close savechanges:=False
    End Sub


    James Liang

    Wednesday, March 11, 2015 10:00 PM

Answers

  • Hi James,

    Looks like you run this VBA method in the current workbook and want to save the current workbook to a temp folder in C drive.

    If you don't specify the format of the file you want to save, by default it is macro free workbook, you need macro-enabled workbook to save the current VBA project too. So you need to change these two lines:

    Sub XLSXOutput()
         Dim wb As Workbook, InitFileName As String, fileSaveName As String
         InitFileName = "C:\Temp" & "\ - PECO_Output_ " & Format(Date, "mmddyyyy")
         Sheets("Sheet1").Range("A:K").Copy
         Set wb = ActiveWorkbook
         fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, filefilter:="Excel files , *.xlsm")
         With wb
             If fileSaveName <> "False" Then
                 .SaveAs fileSaveName, xlOpenXMLWorkbookMacroEnabled
                 .Close
             Else
                 .Close False
                 Exit Sub
             End If
         End With
    
     ThisWorkbook.Close savechanges:=False
     End Sub

    If it still doesn't work, try another drive as Eugene said please.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, March 13, 2015 2:58 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.
    Thursday, March 12, 2015 9:22 AM
  • Hello James,

    What value do you pass to the SaveAs method?

    Try to save the Workbook to a non-system drive. The C: drive requires admin privileges for writing on latest OS.

    Thursday, March 12, 2015 9:49 AM
  • Hi James,

    Looks like you run this VBA method in the current workbook and want to save the current workbook to a temp folder in C drive.

    If you don't specify the format of the file you want to save, by default it is macro free workbook, you need macro-enabled workbook to save the current VBA project too. So you need to change these two lines:

    Sub XLSXOutput()
         Dim wb As Workbook, InitFileName As String, fileSaveName As String
         InitFileName = "C:\Temp" & "\ - PECO_Output_ " & Format(Date, "mmddyyyy")
         Sheets("Sheet1").Range("A:K").Copy
         Set wb = ActiveWorkbook
         fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, filefilter:="Excel files , *.xlsm")
         With wb
             If fileSaveName <> "False" Then
                 .SaveAs fileSaveName, xlOpenXMLWorkbookMacroEnabled
                 .Close
             Else
                 .Close False
                 Exit Sub
             End If
         End With
    
     ThisWorkbook.Close savechanges:=False
     End Sub

    If it still doesn't work, try another drive as Eugene said please.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, March 13, 2015 2:58 AM
    Moderator
  • Hi, I have an example with error handeling. Maybe This Will help: https://gallery.technet.microsoft.com/VBA-Error-Handling-17bdb38a Reshma

    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    Friday, March 13, 2015 5:10 AM