Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Create PDF from excel with filename...
I'm using Excel to create invoices for my company. I want to then
save all the invoices to .pdf using Acrobat Distiller using the invoice number as the file name. Can anyone help? I've pasted the code I have below: Sub PrintPDF() ' 'Keyboard Shortcut: Ctrl+p ' 'The invoice number is in cell I8. However, it is a function, and therefore 'can't be pasted directly. So, I just copy it to J1, and work from there. Sheets("Invoice").Select Range("I8").Select Selection.Copy Range("J1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'This just puts the active cell back onto my document Range("D8").Select Dim strOldPrinter As String strOldPrinter = Application.ActivePrinter ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Actobat Distiller on LPT1:" Application.SendKeys "{return}" & Range("J1") & "{return}", True Application.Dialogs(xlDialogPrint).Show Application.ActivePrinter = strOldPrinter End Sub When I run this macro, It starts printing to my Deskjet, and not the Distiller. If you can help me, please post here. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Create PDF from excel with filename...
I would set the application.activeprinter to the
Distiller then print then change back (sort of what you are doing.) Here are work we actually use the activeprinter index to loop thru the printers until the one we want is found then it prints. I would try that method and see what the exact name of the printer is, that could be the problem. Keith www.kjtfs.com -----Original Message----- I'm using Excel to create invoices for my company. I want to then save all the invoices to .pdf using Acrobat Distiller using the invoice number as the file name. Can anyone help? I've pasted the code I have below: Sub PrintPDF() ' 'Keyboard Shortcut: Ctrl+p ' 'The invoice number is in cell I8. However, it is a function, and therefore 'can't be pasted directly. So, I just copy it to J1, and work from there. Sheets("Invoice").Select Range("I8").Select Selection.Copy Range("J1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'This just puts the active cell back onto my document Range("D8").Select Dim strOldPrinter As String strOldPrinter = Application.ActivePrinter ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Actobat Distiller on LPT1:" Application.SendKeys "{return}" & Range("J1") & "{return}", True Application.Dialogs(xlDialogPrint).Show Application.ActivePrinter = strOldPrinter End Sub When I run this macro, It starts printing to my Deskjet, and not the Distiller. If you can help me, please post here. Any help would be greatly appreciated. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Create PDF from excel with filename...
Can you or anyone provide some sample code that could loop through the
printers? I've thought of it not being named right, but how could I find out? Thanks for any information. "Keith" wrote in message ... I would set the application.activeprinter to the Distiller then print then change back (sort of what you are doing.) Here are work we actually use the activeprinter index to loop thru the printers until the one we want is found then it prints. I would try that method and see what the exact name of the printer is, that could be the problem. Keith www.kjtfs.com -----Original Message----- I'm using Excel to create invoices for my company. I want to then save all the invoices to .pdf using Acrobat Distiller using the invoice number as the file name. Can anyone help? I've pasted the code I have below: Sub PrintPDF() ' 'Keyboard Shortcut: Ctrl+p ' 'The invoice number is in cell I8. However, it is a function, and therefore 'can't be pasted directly. So, I just copy it to J1, and work from there. Sheets("Invoice").Select Range("I8").Select Selection.Copy Range("J1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'This just puts the active cell back onto my document Range("D8").Select Dim strOldPrinter As String strOldPrinter = Application.ActivePrinter ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Actobat Distiller on LPT1:" Application.SendKeys "{return}" & Range("J1") & "{return}", True Application.Dialogs(xlDialogPrint).Show Application.ActivePrinter = strOldPrinter End Sub When I run this macro, It starts printing to my Deskjet, and not the Distiller. If you can help me, please post here. Any help would be greatly appreciated. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Create PDF from excel with filename...
This is old (XL97 and maybe Acrobat 4) but should work with potentially
minor modifications; to get your current printer name just record a macro while you print to the adobe, assuming distiller is still an option?: Sub PrintPDF() Application.ActivePrinter = _ "Acrobat Distiller on C:\Program Files\Adobe\Acrobat 4.0\PDF Output\*.pdf" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Acrobat Distiller on C:\Program Files\Adobe\Acrobat 4.0\PDF Output\*.pdf", _ Collate:=True ' ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ ' "Acrobat PDFWriter on LPT1:", PrToFileName:="reser.pdf" ' Application.WindowState = xlMinimized newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 10 waitTime = TimeSerial(newHour, newMinute, newSecond) 'give enough time for distiller to finish processing the file before renaming it Application.Wait waitTime ' Move and rename file. OldName = "C:\Program Files\Adobe\Acrobat 4.0\PDF Output\Myfilename.pdf" NewName = "C:\Windows\desktop\PDFs\MyNEWfilename.pdf" Name OldName As NewName End Sub Steve Hillin wrote in article . .. Can you or anyone provide some sample code that could loop through the printers? I've thought of it not being named right, but how could I find out? Thanks for any information. "Keith" wrote in message ... I would set the application.activeprinter to the Distiller then print then change back (sort of what you are doing.) Here are work we actually use the activeprinter index to loop thru the printers until the one we want is found then it prints. I would try that method and see what the exact name of the printer is, that could be the problem. Keith www.kjtfs.com -----Original Message----- I'm using Excel to create invoices for my company. I want to then save all the invoices to .pdf using Acrobat Distiller using the invoice number as the file name. Can anyone help? I've pasted the code I have below: Sub PrintPDF() ' 'Keyboard Shortcut: Ctrl+p ' 'The invoice number is in cell I8. However, it is a function, and therefore 'can't be pasted directly. So, I just copy it to J1, and work from there. Sheets("Invoice").Select Range("I8").Select Selection.Copy Range("J1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'This just puts the active cell back onto my document Range("D8").Select Dim strOldPrinter As String strOldPrinter = Application.ActivePrinter ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Actobat Distiller on LPT1:" Application.SendKeys "{return}" & Range("J1") & "{return}", True Application.Dialogs(xlDialogPrint).Show Application.ActivePrinter = strOldPrinter End Sub When I run this macro, It starts printing to my Deskjet, and not the Distiller. If you can help me, please post here. Any help would be greatly appreciated. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto create "backup of filename.xls" in Excel 2007. | Excel Discussion (Misc queries) | |||
Create hyperlink without filename? | Excel Worksheet Functions | |||
Excel macro to prompt for filename | Excel Discussion (Misc queries) | |||
Create buttom that saves current file in a given filename (Excel) | Excel Worksheet Functions | |||
How do i create a macro that saves the filename that is equal to a cell in the sheet? | Excel Discussion (Misc queries) |