Super User Asked by ramji on December 6, 2020
I have added a button in excel sheet and now when I click that button it saves the sheet at specific path with specific name but in pdf format.
I want to save this sheet simply in excel format (.xlsx). so
Sub PDFActiveSheet2()
Dim ws As Worksheet
Dim strFile As String
On Error GoTo errHandler
strFile = "m:formats" & Range("H8")
Set ws = ActiveSheet
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "file has been created."
exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create the file"
Resume exitHandler
End Sub
What do I need to change?
Change your following code from
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
To the following code
ActiveWorkbook.SaveAs Filename:="C:Users46506090DesktopBook1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Change the path (C:Users46506090DesktopBook1.xlsm) to what you would want and see if it works.
CreateBackup:=False
This is optional
The complete code
Option Explicit
Sub Button1_Click()
'Sub PDFActiveSheet2()
Dim ws As Worksheet
Dim strFile As String
On Error GoTo errHandler
strFile = "m:formats" & Range("H8")
Set ws = ActiveSheet
ActiveWorkbook.SaveAs Filename:="C:Users46506090DesktopBook1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Comment: Replace "C:Users46506090DesktopBook1.xlsm" to your desired filename
MsgBox "file has been created."
exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create the file"
Resume exitHandler
End Sub
Answered by Prasanna on December 6, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP