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, Commented Sep 29, 2015 at 11:10

EyePeaSea · Accepted Answer · 2015-09-30 09:29:23Z

2

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.

edited Sep 30, 2015 at 9:29

answered Sep 29, 2015 at 10:18

EyePeaSea

1859 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

ramji Over a year ago

Thanks a lot dear EyePeaSea. You are really great at vba. I wish i could be as skillful as you are at vba. Dear EyePeaSea i wish to add one more button in excel sheet to clear the contents of some specific cells when i press it. for example i add 'clear' button in excel sheet i want that when i press it, it should clear the contents of cell no I1, H8, F10, F13, F14, H16. It should only clear the values of these cell not the formatting, formulas or conditional formatting if any. please help me...thanks...

EyePeaSea Over a year ago

Hi Ramji. Glad I could be of help again. Firstly, if you're happy with my answer, please mark it as accepted (the Tick icon by my reply). As for your new question, I'm happy to answer, but again, please post this as a question. Stackoverflow works by helping everyone find a problem/solution and that is easier when different questions are posted separately. Regards, Ian

ramji Over a year ago

Dear EyePeaSea thanks for your reply. I have already marked your answer as accepted. Keeping your advise in mind, i will ask a new question. I hope you will help me again. Thanks a lot sir...

ramji Over a year ago

Dear EyePeaSea, it is not letting me ask more questions and saying i have reached my question limit. Please help..

EyePeaSea Over a year ago

Try again now. If it doesn't work, then please read this article: meta.stackoverflow.com/questions/271542/… - You may have to wait before you can post the question. An example of the VBA line you need is Worksheets(ActiveSheet.Name).Range("h8").ClearContents - ClearContents does just that. Clear will clear contents and formatting. If you can post a 'question' again soon then I'll post a proper bit of code. However, I can't keep answering in these comments as they are very limited.

|

Collectives™ on Stack Overflow

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

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related