Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Printing Excel to pdf

    If I set my default printer to Adobepdf, the following code prints the excel worksheet to pdf.

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    But it then asks 'Save as'..... I would like it to save itself as the text from the worksheet cell A1?, and save in the directory c:\My Documents\

    I would love to know if anyone has an answer to this.... Any help would be much appreciated

    Thanks

  2. #2
    Join Date
    Feb 2009
    Posts
    2
    Here is some code I use to print to a .PDF and specify the name:
    ActiveWindow.SelectedSheets.PrintOut Copies:=NumberCopies, Collate:=True
    ' delete old file and send save as file name to printer driver
    ' Create object for file handling
    Set FileObjects = CreateObject("Scripting.FileSystemObject")
    If FileObjects.FileExists(CurrentPath & "\" & _
    (CStr(Sheets("Controls").Cells(CheckRow, CheckCol))) & ".pdf") Then
    FileObjects.DeleteFile CurrentPath + "\" & _
    (CStr(Sheets("Controls").Cells(CheckRow, CheckCol))) & ".pdf"
    End If
    ' Wait for save as form to appear
    Application.Wait (Now() + TimeValue("0:0:1"))
    SendKeys CurrentPath + "\" & _
    (CStr(Sheets("Controls").Cells(Row, Col))) & ".pdf" & "{ENTER}"
    Application.Wait (Now() + TimeValue("0:0:1")) ' Wait for print/save to occur
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •