VBA Macro to generate a PDF without saving it
Solved
Looping38
Posted messages
91
Registration date
Status
Member
Last intervention
-
Looping38 Posted messages 91 Registration date Status Member Last intervention -
Looping38 Posted messages 91 Registration date Status Member Last intervention -
Bonjour,
I am stuck on a macro. I want to generate a PDF from a selection in a workbook and name it according to certain cells, but only open it using the OpenAfterPublish:= _True function and not necessarily save it. It is up to the user to save it as needed from Adobe Reader.
Indeed, I do not necessarily know the destination of the file for the user.
An alternative would be to offer a dialog box to choose the destination, already suggesting a file name. (This solution I wish to pursue only if no other option is possible).
Currently, I am using the following code:
Thank you for your help.
Best regards, Martin
Configuration: Windows / Chrome 66.0.3359.181
I am stuck on a macro. I want to generate a PDF from a selection in a workbook and name it according to certain cells, but only open it using the OpenAfterPublish:= _True function and not necessarily save it. It is up to the user to save it as needed from Adobe Reader.
Indeed, I do not necessarily know the destination of the file for the user.
An alternative would be to offer a dialog box to choose the destination, already suggesting a file name. (This solution I wish to pursue only if no other option is possible).
Currently, I am using the following code:
Sub ExportPDFnomvariable()
'Selection of the area to print
CV = 6
With Worksheets("Panorama FM")
For n = 8 To 115
If .Columns(n).Hidden = False Then CV = CV + 1
If CV = 11 Then Exit For
Next n
.Range(Cells(8, 2), Cells(114, n)).Select
' Macro to export as PDF with variable name based on cell content.
'Access path for saving
ChDir "C:\Users\Martin GIVELET\Desktop\Devis Santé"
selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Martin GIVELET\Desktop\Devis Santé\DEVIS " & " " & Sheets("Données Client").Cells(1, 10).Value & " " & Sheets("Données Client").Cells(5, 5).Value & " " & Sheets("Données Client").Cells(4, 5).Value, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End With
End Sub
Thank you for your help.
Best regards, Martin
Configuration: Windows / Chrome 66.0.3359.181
2 answers
-
I made progress and I’m sharing my solution: I save the file in the same directory as the Excel file.
Ideally, it should check if a folder "Quotes" exists to use it; otherwise, create it.
Sub ExportPDFVariableName()
'incrementing number for each print
Sheets("Client Data").[C25] = Sheets("Client Data").[C25] + 1
'Selecting the area to print
CV = 6
With Worksheets("Panorama FM")
For n = 8 To 123
If .Columns(n).Hidden = False Then CV = CV + 1
If CV = 11 Then Exit For
Next n
Range(Cells(8, 2), Cells(121, n)).Select
' Macro to export as PDF with variable name based on cell content.
'File path for saving
ChDir (ThisWorkbook.Path)
'File path + desired document name
'Attention ==> Sheets("Route Sheet").Cells(LineNumber, ColumnNumber).Value
'For spaces, copy ==> " "&
'To allow or not the document to open in PDF after each save, change OpenAfterPublish:= _False/True
selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
" Quotes " & Sheets("Client Data").Cells(1, 10).Value & " " & Sheets("Client Data").Cells(5, 5).Value & " " & Sheets("Client Data").Cells(4, 5).Value & Sheets("Client Data").Cells(25, 3).Value, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End With
End Sub
If anyone can help me with the end...
Martin -
Hello,
to be placed in ThisWorkbook taken from the site:
https://excel-malin.com/
Option Explicit Private Sub Workbook_Open() TesteSiDossierExiste End Sub Sub TesteSiDossierExiste() 'by Excel-Malin.com ( http://excel-malin.com ) Dim MonDossier As String MonDossier = ThisWorkbook.Path & "\DEVIS " If DossierExiste(MonDossier) = True Then ' MsgBox "The folder exists..." Else MkDir (ThisWorkbook.Path & "\DEVIS ") End If End Sub Public Function DossierExiste(MonDossier As String) 'by Excel-Malin.com ( http://excel-malin.com ) If Len(Dir(MonDossier, vbDirectory)) > 0 Then DossierExiste = True Else DossierExiste = False End If End Function
--
@+ The Woodpecker
