Sub SavePDF()
Dim vFile
vFile = UserSave1File()
If vFile <> "" Then ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFile
End Sub
'new way w Office Lib
private Function UserSave1File()
Dim strTable As String
Dim strFilePath As String
Dim sDialogMsg As String, sDecr As String, sExt As String
'Application.FileDialog(msoFileDialogSaveAs) =2
'Application.FileDialog(msoFileDialogFilePicker) =3
With Application.FileDialog(msoFileDialogSaveAs) 'MUST ADD REFERENCE : Microsoft Office X.0 Object Library
.AllowMultiSelect = True
.Title = sDialogMsg ' "Locate a file to Import"
.ButtonName = "Import"
.InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Function
End If
'Save the first file selected
UserSave1File = Trim(.SelectedItems(1))
End With
End Function
thank you so much. It works almost perfectly, except that it saves only the active page. do i need to change the sheet names of the other sheets anywhere in the script?give user a button to call SavePDF.
note: It may require (In vba, tools, references) ADD REFERENCE : Microsoft Office X.0 Object Library
Code:Sub SavePDF() Dim vFile vFile = UserSave1File() If vFile <> "" Then ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFile End Sub 'new way w Office Lib private Function UserSave1File() Dim strTable As String Dim strFilePath As String Dim sDialogMsg As String, sDecr As String, sExt As String 'Application.FileDialog(msoFileDialogSaveAs) =2 'Application.FileDialog(msoFileDialogFilePicker) =3 With Application.FileDialog(msoFileDialogSaveAs) 'MUST ADD REFERENCE : Microsoft Office X.0 Object Library .AllowMultiSelect = True .Title = sDialogMsg ' "Locate a file to Import" .ButtonName = "Import" .InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail If .Show = 0 Then 'There is a problem Exit Function End If 'Save the first file selected UserSave1File = Trim(.SelectedItems(1)) End With End Function
thank you this worked well. However after the file is saved, i noticed the excel sheets were all selected in the excel file. How can i deselect in the macro after the pdf is saved?have it select all pages before export:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select