98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Export to PDF and prompting user for filename and save location
Export to PDF and prompting user for filename and save location
Resolved · High Priority · Version 2010
Mon 23 Jan 2017, 15:54Delegate David said...
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?
Fri 27 Jan 2017, 10:12Trainer Jens said...
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
Fri 27 Jan 2017, 10:16Delegate David said...
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?)
Fri 27 Jan 2017, 10:23Trainer Jens said...
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
Fri 27 Jan 2017, 10:27Delegate David said...
RE: Export to PDF and prompting user for filename and save locat
Oh thanks so much you are fantastic!! :)
Happy Friday!
Fri 27 Jan 2017, 10:40Trainer Jens said...
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
Fri 27 Jan 2017, 10:45Delegate David said...
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?
Fri 27 Jan 2017, 11:15Trainer Jens said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quickly copy a formula across sheetsSuppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets. |
