David has attended:
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Export to PDF and prompting user for filename and save location
Hello,
I have a macro which I recorded as follows:
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"*\Book1.pdf", Quality:=xlQualityStandard _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
My issue is that I want the user that runs the macro to be prompted for:
a) FileName
b) Save Location
How can i achieve this?
RE: Export to PDF and prompting user for filename and save locat
Hi David,
Thank you for the forum question and I am sorry about the late answer.
Try something like the below code:
Sub ExportPdf()
Dim strFileName As String
Dim strPath As String
strFileName = InputBox("Enter file name")
strPath = InputBox("Enter file path")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"strfilename & strpath", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
You can use variables and input boxes to do the job.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Export to PDF and prompting user for filename and save locat
Jens,
Thanks very much for this.
The file path is an issue as people rarely know the exact path and it would be laborious to type something like C:\Investment Management\Research Notes\Compan... etc etc
Is there a way I can bring up a windows navigation box? Alternatively, I suppose a simpler solution would be to put it in the current directory (ActiveWorkbook.Path?)
RE: Export to PDF and prompting user for filename and save locat
Hi David,
Yes
You can use a folder picker:
Sub ExportPdf()
Dim strFileName As String
Dim strPath As String
strFileName = InputBox("Enter file name")
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
strPath = .SelectedItems(1) & "\"
Else
MsgBox "FilePath not selected!", , "Path selecter"
Exit Sub
End If
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"strfilename & strpath", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Export to PDF and prompting user for filename and save locat
Oh thanks so much you are fantastic!! :)
Happy Friday!
RE: Export to PDF and prompting user for filename and save locat
Hi David,
I am happy to help and a happy Friday to you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Export to PDF and prompting user for filename and save locat
Is there anyway I can get the default folder when the dialog box appears to be the one the excel document is saved in?
RE: Export to PDF and prompting user for filename and save locat
Hi David,
I have made a mistake sorry in the codes above sorry.
Change this part:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"strfilename & strpath", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
To:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strfilename & strpath, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
Answer to your last question:
Sub ExportPdf()
Dim strFileName As String
Dim strThisFolderPath As String
strThisFolderPath = ThisWorkbook.Path & "\"
strFileName = InputBox("Enter file name")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strThisFolderPath & strFileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
