Log In
Register
Home
PDF Report Save Dialog Box reference from a cell
Steven Behr
Member
Members
Power Query
Power Query
Forum Posts: 85
Member Since:
January 30, 2020
January 30, 2020
Offline1
February 26, 2020 - 3:18 pm
Hi,
I have completed a VBA PDF report that has a save as dialog box but I need to reference a cell from excel that automatically inputs that information into the saveAS dialog box.
the PDF VBA is as follows
Sub PrintPDFIncidentReport2()
'turn off screenupdating
Application.ScreenUpdating = False
'open dialog and set file type
Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Personal Incident Report")
'if no value is added for file name
If Opendialog = False Then
MsgBox "Cancelled"
Exit Sub
End If
'set the named range for the PDF
Set Myrange = Sheet5.Range("B1:I32")
Sheet5.PageSetup.PrintArea = "B$1:$I$32"
'create the PDF
On Error Resume Next
'ActiveSheet.PrintOut
'Worksheets("Report3").PrintOut from:=1, To:=1
Myrange.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Opendialog, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
On Error GoTo 0
'clear the page breaks
ActiveSheet.DisplayPageBreaks = False
Application.ScreenUpdating = False
RefID_Indicator
Ref
End Sub
'turn off screenupdating
Application.ScreenUpdating = False
'open dialog and set file type
Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Personal Incident Report")
'if no value is added for file name
If Opendialog = False Then
MsgBox "Cancelled"
Exit Sub
End If
'set the named range for the PDF
Set Myrange = Sheet5.Range("B1:I32")
Sheet5.PageSetup.PrintArea = "B$1:$I$32"
'create the PDF
On Error Resume Next
'ActiveSheet.PrintOut
'Worksheets("Report3").PrintOut from:=1, To:=1
Myrange.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Opendialog, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
On Error GoTo 0
'clear the page breaks
ActiveSheet.DisplayPageBreaks = False
Application.ScreenUpdating = False
RefID_Indicator
Ref
End Sub
Philip Treacy
Admin
Forum Posts: 1548
Member Since:
October 5, 2010
October 5, 2010
Offline2
February 26, 2020 - 5:12 pm
Hi Steve,
If you are reading the filename from a cell, why do you need to display a dialog box asking for the filename?
I'd just check if the cell in question has something in it, and if it doesn't then display the dialog box to ask for the filename.
Regards
Phil
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mynda Treacy, QSolutions Group
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Herbie Key
Trevor Pindling
Stevan Kanjo
Erin Sheldon
Nikita Bhatia
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Young You
Forum Stats:
Groups: 3
Forums: 24
Topics: 6526
Posts: 28575
Member Stats:
Guest Posters: 49
Members: 32809
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —