Home |
Search |
Today's Posts |
#1
|
|||
|
|||
macro to run series of reports to PDF
I use the macro below with Hyperion Enterprise Retreive (an Excel add-in) to
run a series of reports and save them to PDF files. The macro works great with my Win2000Pro machine running Excel 2000, but I've been upgraded to an XP machine with Excel 2003. The macro gets stuck on the row: Application.ActivePrinter = "Acrobat Distiller on Ne00:" Any thoughts as to why? Thanks, Marc Range("$K$1").Select ActiveCell.FormulaR1C1 = "report1" Range("$c$21").Select Dim PSFileName As String Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) Range("$K$1").Select ActiveCell.FormulaR1C1 = "report2" Range("$c$21").Select Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) The macro continues on for a list of around 50 reports... |
#3
|
|||
|
|||
Thanks for this suggestion, Gary. I feel a little foolish, but I don't know
how to do what you are suggesting. Adding the script you suggested to my existing macro did not work. What do I do to run this? "Gary Brown" wrote in message ... Maybe the new configuration looks at the printer differently. Run this... Debug.Print Application.ActivePrinter to check out what the syntax should be. Don't know if this will solve your problem, however. Good Luck and Hope this Helps, -- Gary Brown "Marc Bobrow" wrote: I use the macro below with Hyperion Enterprise Retreive (an Excel add-in) to run a series of reports and save them to PDF files. The macro works great with my Win2000Pro machine running Excel 2000, but I've been upgraded to an XP machine with Excel 2003. The macro gets stuck on the row: Application.ActivePrinter = "Acrobat Distiller on Ne00:" Any thoughts as to why? Thanks, Marc Range("$K$1").Select ActiveCell.FormulaR1C1 = "report1" Range("$c$21").Select Dim PSFileName As String Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) Range("$K$1").Select ActiveCell.FormulaR1C1 = "report2" Range("$c$21").Select Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) The macro continues on for a list of around 50 reports... |
#4
|
|||
|
|||
OK, Let's try something slightly different. Create a small macro and run
it. A message box will appear telling you what the syntax is for your current printer. To do this... Select... 'TOOLS' then 'MACRO' then 'VISUAL BASIC EDITOR' Select... 'INSERT MODULE' Enter the following text... Sub Test() MsgBox Application.ActivePrinter End Sub Put your cursor on the line 'MsgBox Application.ActivePrinter'. Press the 'F5' key or select... 'RUN' then 'RUN SUB/USER FORM' A Message Box will appear with something like... hp officejet 6100 series on Ne00: Very similar to what you have... "Acrobat Distiller on Ne00:" Hopefully, it's slightly different and by making the 'correction', your macro will work. Good Luck. Sincerely, -- Gary Brown "Marc Bobrow" wrote: Thanks for this suggestion, Gary. I feel a little foolish, but I don't know how to do what you are suggesting. Adding the script you suggested to my existing macro did not work. What do I do to run this? "Gary Brown" wrote in message ... Maybe the new configuration looks at the printer differently. Run this... Debug.Print Application.ActivePrinter to check out what the syntax should be. Don't know if this will solve your problem, however. Good Luck and Hope this Helps, -- Gary Brown "Marc Bobrow" wrote: I use the macro below with Hyperion Enterprise Retreive (an Excel add-in) to run a series of reports and save them to PDF files. The macro works great with my Win2000Pro machine running Excel 2000, but I've been upgraded to an XP machine with Excel 2003. The macro gets stuck on the row: Application.ActivePrinter = "Acrobat Distiller on Ne00:" Any thoughts as to why? Thanks, Marc Range("$K$1").Select ActiveCell.FormulaR1C1 = "report1" Range("$c$21").Select Dim PSFileName As String Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) Range("$K$1").Select ActiveCell.FormulaR1C1 = "report2" Range("$c$21").Select Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) The macro continues on for a list of around 50 reports... |
#5
|
|||
|
|||
I just thought of something else. You say you just got upgraded to a new
computer. Are you sure it has Acrobat Distiller on it? -- Gary Brown "Marc Bobrow" wrote: Thanks for this suggestion, Gary. I feel a little foolish, but I don't know how to do what you are suggesting. Adding the script you suggested to my existing macro did not work. What do I do to run this? "Gary Brown" wrote in message ... Maybe the new configuration looks at the printer differently. Run this... Debug.Print Application.ActivePrinter to check out what the syntax should be. Don't know if this will solve your problem, however. Good Luck and Hope this Helps, -- Gary Brown "Marc Bobrow" wrote: I use the macro below with Hyperion Enterprise Retreive (an Excel add-in) to run a series of reports and save them to PDF files. The macro works great with my Win2000Pro machine running Excel 2000, but I've been upgraded to an XP machine with Excel 2003. The macro gets stuck on the row: Application.ActivePrinter = "Acrobat Distiller on Ne00:" Any thoughts as to why? Thanks, Marc Range("$K$1").Select ActiveCell.FormulaR1C1 = "report1" Range("$c$21").Select Dim PSFileName As String Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) Range("$K$1").Select ActiveCell.FormulaR1C1 = "report2" Range("$c$21").Select Application.ActivePrinter = "Acrobat Distiller on Ne00:" Application.Goto Reference:="r1c1" Let PSFileName = Application.ActiveCell SendKeys PSFileName & "{ENTER}", False ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83" ActiveSheet.PrintOut , PrintToFile:=True PSFileName = Chr(34) & PSFileName & Chr(34) The macro continues on for a list of around 50 reports... |
#6
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change Series order in a Combination Chart? | Charts and Charting in Excel | |||
series graph -- one series being added to another series | Charts and Charting in Excel | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
How to program an excel macro to repeat a series of keystrokes? | Excel Discussion (Misc queries) |