CODEWITHSUNDEEP
vbaexcel
luke
luke

Reputation: 482

Save copies as .PDF & .xlsx

I'm trying to save copies of the workbook but don't know how to set the file type when saving, this code makes the files but they're corrupt and cannot be opened.

Sub Saves1()

'Store Answers
Dim SavePdfAnswer As String
Dim SaveXlsxAnswer As String
SavePdfAnswer = VBA_CS.Range("C2")
SaveXlsxAnswer = VBA_CS.Range("C3")

'Store File Path And Names
PdfFilePath = VBA_CS.Range("M2") & "\" & ActiveSheet.Range("F9") & ".pdf" 'File path for pdf file
ExcelFilePath = VBA_CS.Range("M2") & "\" & ActiveSheet.Range("F9") & ".xlsx" 'File path for excel xlsx file

'Save as pdf
If SavePdfAnswer = "Yes" Then
  ActiveWorkbook.SaveCopyAs PdfFilePath
End If

'Save as excel xlsx
If SaveXlsxAnswer = "Yes" Then
  ActiveWorkbook.SaveCopyAs ExcelFilePath
End If

End Sub

Upvotes: 0

Views: 20764

Answers (3)

Mohammed
Mohammed

Reputation: 313

for pdf:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="path\pdf_from_excel.pdf" _
    , Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=True

for xlsx:

ActiveWorkbook.SaveAs Filename:= _
    "path\excel_file_name.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Hope this works whats needed

Upvotes: 2

Paweł Piwowar
Paweł Piwowar

Reputation: 174

if You want to know how system do this you can register macros. Ms Word generated this code :

ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        "C:\Users\Administrator\Desktop\fileName.pdf", ExportFormat:=wdExportFormatPDF, _
         OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
        wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
        IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
        wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
        True, UseISO19005_1:=False

Upvotes: 0

tretom
tretom

Reputation: 625

A quick example:

Sub SaveFile()
Dim tmpPth As String

On Error GoTo errorhandle

tmpPth = FilePath & FileName


ThisWorkbook.Sheets("Sheetname").ExportAsFixedFormat Type:=xlTypePDF, FileName:=tmpPth & ".pdf", Quality:=xlQualityStandard, openAfterPublish:=False

ActiveWorkbook.SaveCopyAs tmpPth & ".xlsm"

Exit Sub

errorhandle:
MsgBox ("Something went wrong")
End Sub

Upvotes: 1

Related Questions