Save as PDF Macro Help

excel_stu

Board Regular
Joined
Oct 28, 2003
Messages
94
I have written a Macro that saves a print area in excel as a .PDF file. I have tried to include all the steps within the macro but stuck on a pop up message box that appears when saving, it asks me for the file name but all I want is to use the filename already there and just to click ok!!

Is there a bit of VB coding that will allow me to do this??

Thanks
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
The pop up is generated by Adobe not Excel so you can't control it with VBA as far as I know.

I've tried using Sendkeys with limited success, will dig out the code I wrote for that (on PC at work) and post it tomorrow if nobody else comes up with anything better.

Laters,
 
Upvote 0
Hi again,

Afraid I can't locate the code I wrote. It didn't really do what I wanted so I must have ditched it.

I recall using Sendkeys to 'hit' the enter key when the Save As dialogue appears but it was a little temperemental. You may need to add a pause in the code to make it wait until the dialogue has appeared.

Hope this helps.
 
Upvote 0
I had a similar request to dump an excel spreadsheet to a PDF file then email it, here is what I did. I hope it helps.

I had to create a class module named cAccroDist with the following code:

Code:
Public WithEvents oDist As PdfDistiller

Private Sub Class_Initialize()
    Set oDist = New PdfDistiller
End Sub

Then I added this to my program.
Code:
Private Sub cmdEmail_Click()

cmdUpdate_Click
Application.ScreenUpdating = False
Sheets("Packing Slip Rev A").Activate

    Dim OL As Object
    Dim EmailItem As Object
    Dim Wb As Workbook
    Dim sAddress As String
    Dim sSubject As String
    Dim sCustomerEmail As String
    Dim sPDFFileName As String
    Dim sPSFileName As String
    Dim sPDFRawFileName As String
    
    sAddress = Range("A6").Text
    sSubject = "Packing Slip For " & Range("B7").Value & " - " & Range("B16").Value & " - " & Range("G16").Value
    sCustomerEmail = Range("c10").Text
    sPDFRawFileName = "C:\" & Range("B7").Value & " - " & Range("B16").Value & " - " & Range("G16").Value & ""
    
 'Define the postscript and .pdf file names.
sPSFileName = sPDFRawFileName & ".ps"
sPDFFileName = sPDFRawFileName & ".pdf"

' Print the Excel range to the postscript file
ActiveSheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, prtofilename:=sPSFileName

'Create PDF File
Dim myPDFDist As New cAccroDist
Set myPDFDist = New cAccroDist
Call myPDFDist.oDist.FileToPDF(sPSFileName, sPDFFileName, bShowWindow)
    
'Email PDF File
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    Set Wb = ActiveWorkbook
    Wb.Save
    With EmailItem
        .Subject = sSubject
        .Body = "Here is the " & sSubject & ". " & "Thank you"
        .CC = ""
        .To = ""
        .Attachments.Add sPDFFileName
        .display
    End With

'Clean Up
Dim fso As New FileSystemObject
    fso.DeleteFile (sPSFileName)
    fso.DeleteFile (sPDFFileName)

    Application.ScreenUpdating = True
     
    Set Wb = Nothing
    Set OL = Nothing
    Set EmailItem = Nothing

Sheets("CoverSheet").Activate
Application.ScreenUpdating = True

End Sub

Good Luck
 
Upvote 0

Forum statistics

Threads
1,215,103
Messages
6,123,108
Members
449,096
Latest member
provoking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top