Excel to PDF Using Visual Basic 11

[azh]

I used the wrong filename is the send keys command. It should read:
Application.SendKeys ("c:\myPostScript.ps{TAB}~&quot

It now works fine.

 

[Dubien]

After compiling a vb code and copy the applicqtion on an other computer, no error message is sent and thr program stay in a kind of loop on "mypdf.filetopdf". The references have been had to my project and the computer has all the same application except not install in the same path. Do you know what's the problem???

 

[Dubien]

I resolve my problem.
If your using this code on WIN Nt 4.0, you need to be an administrator for the computer, otherwise the program will stall on the line

Set myPDF = New PdfDistiller
or
myPDF.FileToPDF strMenuFilePS, strMenuFilePDF, ""*

thanks!

 

[zzfive03]

Hello guys.. can someone help me with my simular problem.

I am using VB 6.0 I have some PDF files that I need to print, but i want them to print in a certain order. When I send them to the windows print spool they dont always print in the same order send them in. i want to create a postscript file, that contains all of my PDF's, so I can send that file to the printer.

I have no idea where to start, I have never worked with PS files before. Any suggestions would be appreciated.

Thank you

 

[risma]

Hi JabbaTheNut,

It's been almost 2 years since you helped me with my previous question, it was really helpful and I appreciate it very much.

Now I have another mission and would like your expert help. How do I print mulitple pages to the same pdf file?
Using your previous code, I was able to print one page, but now I have muliptle pages to print to the same pdf output file, the following code would only give me the last page in the pdf file!

Here is part of my vba code:

' Print page to PDF File

PSFileName = "U:\1Tony\pdf\" & CustNo & "Ps"
PDFFileName = "U:\1Tony\pdf\" & CustNo & ".pdf"
' Print Invoice Detail page
Worksheets("InvDetail").Select
ActiveSheet.PrintOut Copies:=1, ActivePrinter:=PTR, Collate:=True, _
PrintToFile:=True, PrToFileName:=PSFileName
' Convert the postscript file to .pdf
' Print Remittance Summary Page
Worksheets("RemitSum").Select
ActiveSheet.PrintOut Copies:=1, ActivePrinter:=PTR, Collate:=True, _
PrintToFile:=True, PrToFileName:=PSFileName
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Looking forward to your early reply....
Thanks and regards
Tony

 

[innov]

Hi Tony.

It pretty simple. Just select multiple sheets first.

Sheets(array("abc", "def", "ghi")).select

Then change your .printout line slightly.

ActiveWindow.SelectedSheets.PrintOut ...

BY THE WAY:

I think it's MUCH easier to just change the activeprinter to the PDFWriter. It's just a printer driver that creates a PDF output to the file you designate. No need to convert from tiff to pdf, etc. in further steps.

If you don't SEE PDFWriter in Acrobat 5.0, it's because Adboe changed the default to Distiller. You have to choose CUSTOM install and pick the PDFWriter. I find it MUCH easier to use than Distiller. Distiller just has too many settings and things to go wrong.

Ted

 

[ActuaryAtLarge]

Dear Jabba,
I'm using your really great piece of code. Like so many others, I spent countless hours testing, then surfing, then finally finding your solution. I am having a slight problem still though.

1) I've uncheched the box in my Distiller settings to not send fonts.
2) I'm still getting the related error message.
3) Attached is my/your code:

Private Sub tektip1()
' *******
' From "JabbaThe Hut"

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
ActiveWorkbook.PrintOut from:=11, To:=17, copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

 

[JaganParna]

Hi Jabba,
Thanks for this thread its working fine for me.Great help.I got an issue though as I try to run in one of the m/c its giving me an error
Active X cannot create object while instancing pdf distiller.
The same code is working on other m/c
Please help
Thank you for your help.
Regards
Jagan

 

[DColcl]

wow. Excellent thred here, folks.

 

[VbNick]

Hi people, I use the gs632 API (gs32.dll postscript writer) for free instead of distiller, and to join several ps files with vb. (open byte). But when printing, I set the visibilty of the excel object to false, its ok, however, the printing... dialog still shows up? how can I make it invisible? (I have tried excelobject.displayalerts =false but didnt work? )

Kayhan
PAGOS INC.

 

[Kingnutin]

This thread has almost everything I have been searching for, save one. I have a single excel file that acts as a GUI to pick and choose which excel document(s) to print. These choices are all seperate excel files that contain one worksheet only. Could anyone help me to figure out how I can print multiple excel files into one single PDF? Can this even be done without have to append each printed file? Any and all ideas are much appreciated!

 

[corporatefinance]

Hi Jabba,

I have modified your EXCEL to PDF Visual Basic Code so I can loop through a list of several excel spreadsheets using a simple for next loop. For some reason after I run the first pass (the first excel file) when I go to run the 2nd loop I get the following error.

Run-time error ‘-2147417851 (80010105)’:
Method ‘FileToPDF of object ‘IPdfDistiler’failed

Do you have a copy of your code that loops through a series of excel spreadsheet names using a for next loop? My code is listed below. I am not sure what is wrong with the myPDF.FileToPDF PSFileName, PDFFileName, "" code. I would appreciate any help you could provide


Public Sub Distill_it_Modified()

Dim q As Integer
Dim TotNumFiles As Integer

Dim zPSFileName As String
Dim zLogFileName As String
Dim zPDFFileName As String

Dim ExcelFileName As String
Dim PSFileName As String
Dim LogFileName As String
Dim PDFFileName As String

Dim LocationExcelFile As String
Dim LocationPSFile As String
Dim LocationLOGFile As String
Dim LocationPDFFile As String

Dim Output_Sheet As String
Dim myPDF As PdfDistiller

TotNumFiles = Range("TotNumFiles").Value
Range("CurrentFile").Value = 0
Application.Calculate

For q = 2 To TotNumFiles
Application.ScreenUpdating = False
Range("CurrentFile").Value = q
Application.Calculate
Output_Sheet = Range("Output_Sheet").Value
ExcelFileName = Range("ExcelFileName").Value
zPSFileName = Range("PSFileName").Value
zLogFileName = Range("LogFileName").Value
zPDFFileName = Range("PDFFileName").Value

LocationExcelFile = Range("LocationExcelFile").Value
LocationPSFile = Range("LocationPSFile").Value
LocationLOGFile = Range("LocationLOGFile").Value
LocationPDFFile = Range("LocationPDFFile").Value

PSFileName = LocationPSFile & zPSFileName
LogFileName = LocationLOGFile & zLogFileName
PDFFileName = LocationPDFFile & zPDFFileName

'Open Excel File
ChDir LocationExcelFile
Workbooks.Open Filename:=LocationExcelFile & ExcelFileName
Sheets(Output_Sheet).Activate

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, Collate:=True, prtofilename:=PSFileName

' Convert the postscript file to .pdf

Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
ActiveWorkbook.Close
Kill PSFileName
Kill LogFileName

Application.Calculate
Next q
Application.ScreenUpdating = True

End Sub

 

[corporatefinance]

Hi Jabba,

I have modified your code to run as a for next loop so I can loop through several spreadsheets at once. However, I seem to get an error with the line:
myPDF.FileToPDF PSFileName, PDFFileName, ""

The error I get is as follows:
Microsoft Visual Basic
Run-time error ‘-2147417851 (80010105)’:
Method ‘FileToPDF of on ject ‘IPdfDistiler’failed

The macro seems to run fine for the first loop and creates the PDF, but then bombs out on loops 2. Can you post a version of your code with a simple for next loop to show how to correct the problem. My code is listed below. I appreciate your help.

My code is as follows:
Public Sub Distill_it()

Dim q As Integer
Dim TotNumFiles As Integer

Dim zPSFileName As String
Dim zLogFileName As String
Dim zPDFFileName As String

Dim ExcelFileName As String
Dim PSFileName As String
Dim LogFileName As String
Dim PDFFileName As String

Dim LocationExcelFile As String
Dim LocationPSFile As String
Dim LocationLOGFile As String
Dim LocationPDFFile As String

Dim Output_Sheet As String
Dim myPDF As PdfDistiller

TotNumFiles = Range("TotNumFiles").Value
Range("CurrentFile").Value = 0
Application.Calculate

For q = 2 To TotNumFiles
Application.ScreenUpdating = False
Range("CurrentFile").Value = q
Application.Calculate
Output_Sheet = Range("Output_Sheet").Value
ExcelFileName = Range("ExcelFileName").Value
zPSFileName = Range("PSFileName").Value
zLogFileName = Range("LogFileName").Value
zPDFFileName = Range("PDFFileName").Value

LocationExcelFile = Range("LocationExcelFile").Value
LocationPSFile = Range("LocationPSFile").Value
LocationLOGFile = Range("LocationLOGFile").Value
LocationPDFFile = Range("LocationPDFFile").Value

PSFileName = LocationPSFile & zPSFileName
LogFileName = LocationLOGFile & zLogFileName
PDFFileName = LocationPDFFile & zPDFFileName

'Open Excel File
ChDir LocationExcelFile
Workbooks.Open Filename:=LocationExcelFile & ExcelFileName
Sheets(Output_Sheet).Activate

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, Collate:=True, prtofilename:=PSFileName

' Convert the postscript file to .pdf

Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
ActiveWorkbook.Close
Kill PSFileName
Kill LogFileName

Application.Calculate
Next q
Application.ScreenUpdating = True

End Sub