Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating PDF Files from Worksheets (Excel 2003)

    I did a search on PDF in the Excel forum but I didn't find anything that answered my question (or, if I did, I wasn't smart enough to know it).

    I have a series of files that all contain multiple worksheets. All the worksheets have defined print areas. I need to create a macro that will loop through each sheet, capture the sheet name and use that as the file name, and create a PDF file from the print area, then store that file in a specific folder.

    I can do everything except actually create the PDF file. I have tried several methods, as follows:

    This uses the SENDKEYS method, but it doesn't actually create the PDF file, for whatever reason. It selects the wrong folder and seems to go through the motions of creating the PDF file, but it doesn't actually do it. Don't know why.

    Sub TestPrint1()
    Dim sCounter As Integer
    Dim iSheet As Integer
    Dim sName As String
    Dim FILENAM

    sCounter = ActiveWorkbook.Worksheets.Count
    For iSheet = 1 To sCounter
    Worksheets(iSheet).Activate
    getprinter = ActivePrinter
    sName = ActiveSheet.Name
    ActivePrinter = "Adobe PDF on Ne04:"
    FILENAM = "c:PDF_Tests" & sName & ".PDF"
    Application.SendKeys FILENAM, True
    Application.SendKeys "{Enter}"
    Application.SendKeys "Y"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:"
    Application.Wait (Now + TimeValue("0.00.01"))
    ActivePrinter = getprinter
    Next iSheet
    End Sub


    Here is another approach that seemed promising, but it doesn't do the job either. It generates several error messages.


    Sub TestPrint1()
    Dim sCounter As Integer
    Dim iSheet As Integer
    Dim sName As String

    sCounter = ActiveWorkbook.Worksheets.Count
    getprinter = ActivePrinter
    For iSheet = 1 To sCounter
    Worksheets(iSheet).Activate
    sName = ActiveSheet.Name
    getprinter = ActivePrinter
    ActivePrinter = "Adobe PDF on Ne03:"
    ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:="c:PDF_Tests" & sName & ".PDF"
    ActivePrinter = getprinter

    Next iSheet

    End Sub

    Does anyone on this forum have any ideas for me? I would certainly appreciate them.

    Thanks,

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Creating PDF Files from Worksheets (Excel 2003)

    Do the links in <post#=392403>post 392403</post#> help at all?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating PDF Files from Worksheets (Excel 2003)

    Hi Steve,

    Well, I went through those posts and looked at the code and I still can't make it work. I suspect now that there is something in our work environment that I don't know that is the stopper. I have opened a support call with Adobe (we have a contract with them) hoping that they can point me in the right direction.

    Let me thank you for your efforts. You are one knowledgeable and helpful guy.

    George

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Creating PDF Files from Worksheets (Excel 2003)

    Thanks for the praise.

    I am just sorry, I couldn't help you further with this.

    Steve

Posting Permissions

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