Hello,
I've created a 2 page document in Excel that essentially uses the vlookup function to pull information from a spreadsheet based on the person's name that is selected. This speadsheet contains over 2000 people, so I've created a print macro to cycle through the list. I've done this with a standard printer many times, but this time I wanted to print to pdf. To do this, I came up with the following code:
Private Sub CommandButton1_Click()
Dim x As Integer
Dim Time As String
x = 2
Do Until x = 2000
Sheet1.Range("A4").Formula = Sheet2.Range("A" & x)
Application.ActivePrinter = "Amyuni PDF Converter on LPT1:"
Application.SendKeys "%C", True
Application.SendKeys "{ENTER}", True
Sheet1.PrintOut
Time = (Now + TimeValue("00:00:1"))
While Time > Now
DoEvents
Wend
x = x + 1
DoEvents
Loop
End Sub
This loop is set up to populate the next person's data, print to PDF, then select the concatenate feature and press enter. It works great, except it randomly stalls. By this, I mean it will get to the print screen, but it won't select concatenate and hit enter, it will just stay there. At that point, the user has to manually click concatenate and enter. When you do this, the macro once again takes over.
Any thoughts for how to eliminate this problem and have it run smoothly and without user intervention? I'd love to be able to leave the computer to do the work without having to baby sit it. Thanks!
~David
Excel Print to PDF Macro
I will not be able to know why at some moments the user needs to manually click on concatenate and print without debugging your code.
I can however give you some advices that might be helpful.
1) you can fill all the excel sheet and then print it to the PDF Printer (i am not sure if this will be feasible in your context.
2) you can set the PDF Printer to NOT prompt your for an output file name, it can prgrammatically select the file to which you wnat to print and concatenate the newly generated file to it.
To get samples on doing this please feel free to download any of our technical notes from: http://www.amyuni.com/en/support/technotes.html
I can however give you some advices that might be helpful.
1) you can fill all the excel sheet and then print it to the PDF Printer (i am not sure if this will be feasible in your context.
2) you can set the PDF Printer to NOT prompt your for an output file name, it can prgrammatically select the file to which you wnat to print and concatenate the newly generated file to it.
To get samples on doing this please feel free to download any of our technical notes from: http://www.amyuni.com/en/support/technotes.html