macro to save file as pdf at a location selected by the User

[skf786]

Hi,

im looking for a macro that saves my file as a PDF and allows user to select the location. File as 4 sheets 1,2,3,4 and the pdf should contain all print areas in the 4 sheets.

thank you in advance.

khalid

 

[ranman256]

give user a button to call SavePDF.
note: It may require (In vba, tools, references) ADD REFERENCE : Microsoft Office X.0 Object Library

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

 

[skf786]

give user a button to call SavePDF.
note: It may require (In vba, tools, references) ADD REFERENCE : Microsoft Office X.0 Object Library

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?

thanks

 

[ranman256]

have it select all pages before export:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

 

[skf786]

have it select all pages before export:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

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?

 

[ranman256]

Sheets(Array("Sheet1")).Select