Notifications
Clear all
VBA & Macros
2
Posts
2
Users
0
Reactions
85
Views
Topic starter
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
Posted : 27/02/2020 1:18 am
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
Posted : 27/02/2020 3:12 am
