• Creating PDF Files from Worksheets (Excel 2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Creating PDF Files from Worksheets (Excel 2003)

    Author
    Topic
    #416819

    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,

    Viewing 0 reply threads
    Author
    Replies
    • #933523

      Do the links in post 392403 help at all?

      Steve

      • #933889

        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

        • #933894

          Thanks for the praise.

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

          Steve

    Viewing 0 reply threads
    Reply To: Creating PDF Files from Worksheets (Excel 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: