How to save the excel file with vba coding without opening it?

Question

I have added a button in excel sheet and added following codes in vba window of that button. Now when I click this button i.e. when I run the codes it saves the excel sheet in pdf form whose name it takes from cell no H8 and saves it at M:\formats. Moreover it also saves the same excel sheet in .xlsx format at M:\formats\excels. But here the problem is when I run the codes it closes the excel sheet in which I have added the codes and opens the file which is saved by the codes. For example I made abc.xlsm excel sheet and added the codes in vb window, now xyz is written in cell no h8 in abc.xlsm excel sheet, now when I will run the codes it closes abc.xlsm and all codes are shown in xyz.xlsx excel sheet. I want it should only save the file in xlsx format it requisite location. It should not close the base file (which is abc.xlsx in the above example) and should not open the saved file (which is xyz.xlsx in the above example). Moreover I want that the saved file (xyz.xlsx in the above example) should not contain any vba coding. In another words it should be just like the backup copy for the base file (which is abc.xlsx in the above example). Kindly help me in to modify these codes to them as I want. I will be highly obliged to you. Thanks

Sub ExportAPDF_and_SaveAsXLSX()

Dim wsThisWorkSheet As Worksheet
Dim objFileSystemObject As New Scripting.FileSystemObject

Dim strFileName As String
Dim strBasePath As String

strBasePath = "M:\formats\"
strFileName = Range("H8")

On Error GoTo errHandler

Set wsThisWorkSheet = ActiveSheet

wsThisWorkSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strBasePath & strFileName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

MsgBox "PDF file has been created."
strBasePath = "M:\formats\excels\"
strFileName = Range("H8")

Application.DisplayAlerts = False

strFileName = objFileSystemObject.GetBaseName(strFileName) & ".xlsx"

wsThisWorkSheet.SaveAs Filename:=strBasePath & strFileName, 

FileFormat:=xlOpenXMLWorkbook

Application.DisplayAlerts = False

MsgBox "Workbook now saved in XLSX format."


    exitHandler:
    Exit Sub
    errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler

    End Sub

@SiddharthRout - Although SaveCopyAs is a neat solution in some circumstances, it doesn't meet the OPs requirements. He's running VBA, but wants the file saved as an XLSX file. You can do this with SaveCopyAs, but that file will then error when you open it. That's why I suggested the (longer / more convoluted) Open-Original and Close-Current approach. Regards, Ian — EyePeaSea

EyePeaSea EyePeaSea · Accepted Answer · 2015-09-29T10:18:46

Here is the code, with just two small changes. Both new sets of lines have the comment "New" in front of them.

Also just tidied up the error handling routine a little bit.

The way it works is this:

Store the filename of the current workbook in the variable 'strMasterWorkbookFilename'
The PDF file is created by 'exporting' the worksheet.
The Excel worksheet is then saved as an XLSX. This effectively 'closes' the original workbook.

3.1 The Button ("Button 8") is removed from the new XLSX worksheet and the workbook is saved again.

The code then re-opens the original workbook ('strMasterWorkbookFilename') and closes the current workbook.

Notes - Saving as the XLSX will remove the Macro code from the saved file. The Macro will remain in the main 'master' file.

Sub ExportAPDF_and_SaveAsXLSX()

    Dim wsThisWorkSheet As Worksheet
    Dim objFileSystemObject As New Scripting.FileSystemObject

    Dim strFileName As String
    Dim strBasePath As String

    ' NEW
    Dim strMasterWorkbookFilename As String
    strMasterWorkbookFilename = ThisWorkbook.FullName

    strBasePath = "M:\formats\"
    strFileName = Range("H8")

    On Error GoTo errHandler

    Set wsThisWorkSheet = ActiveSheet

    wsThisWorkSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strBasePath & strFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

        MsgBox "PDF file has been created."

        Application.DisplayAlerts = False
        strFileName = objFileSystemObject.GetBaseName(strFileName) & ".xlsx"
        wsThisWorkSheet.SaveAs Filename:=strBasePath & strFileName, FileFormat:=xlOpenXMLWorkbook
        wsThisWorkSheet.Shapes("Button 8").Delete
        ActiveWorkbook.Save

        Application.DisplayAlerts = False

        MsgBox "Workbook now saved in XLSX format."

        ' NEW
        Workbooks.Open strMasterWorkbookFilename
        Workbooks(strFileName).Close SaveChanges:=False


    exitHandler:
            Exit Sub
    errHandler:
            MsgBox "Error Saving file.  The error is " & vbCrLf & Chr(34) & Err.Description & Chr(34)

            Resume exitHandler

End Sub

Thanks for posting this as a new question. If I'd carried on modifying the original code in the first question, it would not have been useful for anyone else reading your original post.

How to save the excel file with vba coding without opening it?

1 Answers