Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compiling Error (Excel 2003 SP3)

    I have a simple programming issue that was hoping for some help on. I should say that I have no VBA programming experience, other than to tweak simple macros that I create with Excel.

    What I would like to do is the following: I need to generate about 75 reports for individual distribution centers. I have already set up a macro that pulls the data and drops it in a template which then populates a bunch of charts. I then end up manually printing a report in PDF. I want to use the Printout command to automatically send a PDF version to a specified folder. To do so, I need to automatically generate the PrToFilename argument. I thought I could do this in a cell in my spreadsheet and then have the macro pick up the cell value and use that as the specified filename for a given report. I have attached the simple code and kind of how this is supposed to flow.

    As always, your help is greatly appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compiling Error (Excel 2003 SP3)

    In the first place, since Path is a string variable, you shouldn't use the keyword Set, this is only for object variables.
    In the second place, you should use Path, not "Path" in the PrintOut instruction. If you use "Path", this will be seen as the literal filename.
    In the third place, you should have Application.ScreenUpdating = True near the end of the macro.

    Sub PhaseI_Print()
    Dim Path As String
    Path = Worksheets("Sheet1").Range("L6").Value
    Application.ScreenUpdating = False
    Sheets(Array("Page 1", "Page 2", "Page 3")).Select
    ActiveWindow.SelectedSheets.PrintOut , , , , "Adobe PDF", "True", , Path
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling Error (Excel 2003 SP3)

    Hi Hans,

    As always, thanks for your help. I tried your changes and the macro runs but it prompts me for a filename for the PDF file. I suppose this means that the PrintOut is not able to see the PrToFilename argument that we tried to set at the beginning (Path). When I copy and paste the entire string from the excel sheet ("C:UsersakaloLocalKPI ReportsPDFsPhaseI US SYSTEM TOTAL, Phase I.ps") it places the printed file in the specified folder with the name. Any thoughts?

    Altin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compiling Error (Excel 2003 SP3)

    In the workbook that you attached, there isn't anything in cell L6 on Sheet1, so Path remains blank.

    I also see now that there shouldn't be quotes around the word True:

    ActiveWindow.SelectedSheets.PrintOut , , , , "Adobe PDF", True, , Path

  5. #5
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling Error (Excel 2003 SP3)

    My apologies, it should be L1.

  6. #6
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compiling Error (Excel 2003 SP3)

    Hans,

    This works great. Thanks much.

    Altin

Posting Permissions

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