I need to generate a series of individual prints from a spreadsheet, going to
a PDF printer, and giving each one a different filename.
I have set the macro to do this, but it opens a PDFSaveAs window and waits
for a filename. Unfortunately the program is so fast and the PDF printer so
slow that they get out of seuqence and I dont know where I am in the sequence
of reports!
How do I pass the individual filenames to the print command in the macro?
Obviously I need a propoerty name to set, but I cannot guess it. I have tried
PrToFileName as the pop up box suggested that, but it doesn't work. Doesn't
even capitalise the property name when I type it.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
prtofilename:="XYZ"


Where do I find all the property/variable names, please?

RG

Hi RG,

Did you ever solve this problem? I have a similar one. Using a macro I have
worksheets print to a specified location, but the created file is either
empty (0 KB) or I get an error message stating it's corrupted when I try to
open it with Adobe.
Some of the macro wording I used:

Application.ActivePrinter = "Adobe PDF on Ne00:"
For CoNo = 7 To 400
AppStr = "LoadCompany_" & CoNo
CellRefA = "A" & CoNo
CellRefB = "B" & CoNo
CoName = Range(CellRefB).Value
XStr = UCase(Range(CellRefA).Value)

If XStr = "X" Then
Application.Run AppStr
Sheets("SUMMARY").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
PrToFileName:="C:\Documents and Settings\marusichd\Desktop\Email Worksheets\"
& CoName & ".pdf"
Sheets("FLEET").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
PrToFileName:="C:\Documents and Settings\marusichd\Desktop\Email Worksheets\"
& CoName & "2.pdf"
Sheets("SELECT TO PRINT").Select

End If
Next CoNo

If you can toss anything my way it would be much appreciated.

Thanx,
Darcy :o)


"RG" wrote:

I need to generate a series of individual prints from a spreadsheet, going to
a PDF printer, and giving each one a different filename.
I have set the macro to do this, but it opens a PDFSaveAs window and waits
for a filename. Unfortunately the program is so fast and the PDF printer so
slow that they get out of seuqence and I dont know where I am in the sequence
of reports!
How do I pass the individual filenames to the print command in the macro?
Obviously I need a propoerty name to set, but I cannot guess it. I have tried
PrToFileName as the pop up box suggested that, but it doesn't work. Doesn't
even capitalise the property name when I type it.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
prtofilename:="XYZ"


Where do I find all the property/variable names, please?

RG

Darcy
Glad to know I'm not the only one!
Similar problems, either a zero file size or corrupted file.
The difference is that I'm using PDF995 as the printer. Freeware and no
support.
As I see it, the problem is that the file name is set outside of the Excel
program - in a dialog box opened by the printer driver.
The way I got around it was by creating a message box in the Excel macro
telling me which file I was up to, then I input the required filename into
the PDF box when it prompts. Not an ideal solution, but it does solve the
problem until I can find another.
I am guessing, but I think you need to pass the required filename to a
different program. The question is, knowing the program name and the variable
name it's setting.
I'm not sure PrintToFile is relevent because printing to a PDF printer has
to go to file.
If this helps you develop further, let me know!!!
RG
:-(


"Darcy" wrote:

Hi RG,

Did you ever solve this problem? I have a similar one. Using a macro I have
worksheets print to a specified location, but the created file is either
empty (0 KB) or I get an error message stating it's corrupted when I try to
open it with Adobe.
Some of the macro wording I used:

Application.ActivePrinter = "Adobe PDF on Ne00:"
For CoNo = 7 To 400
AppStr = "LoadCompany_" & CoNo
CellRefA = "A" & CoNo
CellRefB = "B" & CoNo
CoName = Range(CellRefB).Value
XStr = UCase(Range(CellRefA).Value)

If XStr = "X" Then
Application.Run AppStr
Sheets("SUMMARY").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
PrToFileName:="C:\Documents and Settings\marusichd\Desktop\Email Worksheets\"
& CoName & ".pdf"
Sheets("FLEET").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
PrToFileName:="C:\Documents and Settings\marusichd\Desktop\Email Worksheets\"
& CoName & "2.pdf"
Sheets("SELECT TO PRINT").Select

End If
Next CoNo

If you can toss anything my way it would be much appreciated.

Thanx,
Darcy :o)


"RG" wrote:

I need to generate a series of individual prints from a spreadsheet, going to
a PDF printer, and giving each one a different filename.
I have set the macro to do this, but it opens a PDFSaveAs window and waits
for a filename. Unfortunately the program is so fast and the PDF printer so
slow that they get out of seuqence and I dont know where I am in the sequence
of reports!
How do I pass the individual filenames to the print command in the macro?
Obviously I need a propoerty name to set, but I cannot guess it. I have tried
PrToFileName as the pop up box suggested that, but it doesn't work. Doesn't
even capitalise the property name when I type it.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
prtofilename:="XYZ"


Where do I find all the property/variable names, please?

RG

Hi RG,

Problem solved for me. The only glitch I still have is that the Save As box
pops up once after each new company is loaded, but all I have to do is hit
the enter key and the process will continue. That could probably be fixed,
but since this is only a minor irritation, I'm not going to spend any more
time on this right now. I copied a macro from another user that prints all
worksheets in a workbook to pdf SUCESSFULLY!! and made it my own. The
following is the VB I used to do my job. The Print_Selected_Worksheets sub
looks at upto 400 lines in my Excel table (each containing a different
Company) and loads data from the table for only those selected companies into
other worksheets in the workbook (one at a time). After each company is
loaded, the SheetsToPDF sub is called to print the needed worksheets for that
company. The XLSheetsToPDF sub is the original macro I borrowed from another
user and changed to make it my own in the SheetsToPDF sub. Hope this helps.
Darcy :o)

Public CoNo as integer
Public CoName as string
Sub Print_Selected_Worksheets()
'
' Print_Selected_Worksheets Macro
' Macro recorded 7/20/2005 by marusichd
'

'

Dim CoNo As Integer
Dim AppStr As String
Dim CellRefA As String
Dim CellRefB As String
Dim CoName As String
Dim XStr As String

For CoNo = 7 To 400
AppStr = "LoadCompany_" & CoNo
CellRefA = "A" & CoNo
CellRefB = "B" & CoNo
CoName = Range(CellRefB).Value
XStr = UCase(Range(CellRefA).Value)

If XStr = "X" Then
Application.Run AppStr

Call SheetsToPDF

Sheets("SELECT TO PRINT").Select

End If

Next CoNo

Application.Run "CLEAR_FORM"
Sheets("SELECT TO PRINT").Select


End Sub

Sub SheetsToPDF()
'Make sure to add in reference to Acrobat Distiller
'Visual Basic Editor Tools References and select Acrobat Distiller
from the list
'This works for Acrobat 6, should be backwardly compatible to Acrobat 5

Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim s As Worksheet
Set fs = CreateObject("Scripting.FileSystemObject") 'Good old FSO
'retval = MsgBox("Do you want to proceed with printing all sheets to
individual PDF files?", vbOKCancel, "Print to PDF")
'If retval = vbCancel Then Exit Sub
'Browse for PDF Folder if it changes, otherwise enter path in FixedPath
variable

FixedPath = "" 'Leave blank if you want to browse
If FixedPath < "" Then
pdfFilePath = FixedPath
Else
InitialFileName = "PDFSave.pdf"
FileFilter = "Acrobat Files (*.pdf;*.ps),*.pdf;*.ps,All Files (*.*),
*.*"
FilterIndex = 1
Title = "Save PDF Files to Folder..."
pdfFilePath = Application.GetSaveAsFilename(InitialFileName,
FileFilter, FilterIndex, Title)
If pdfFilePath < False Then
pdfFilePath = fs.GetParentFolderName(pdfFilePath)
'MsgBox pdfFilePath
Else
Exit Sub
End If
End If
'Identify printers
OriginalPrinter = Application.ActivePrinter
'MsgBox OriginalPrinter
PDFPrinter = "Acrobat Distiller on Ne04:" 'Change this to suit the
Acrobat Distiller printer

'Cycle through each sheet, retrieves sheet name and generate PS file in
selected folder
For Each s In ThisWorkbook.Worksheets
sName = s.Name 'Sheet name
If sName = "SUMMARY" Then 'GoTo MoveOn 'Here you can identify sheets
you don't want printed
CoName = Range("B6").Value
PSFileName = pdfFilePath & "\" & CoName & ".PS" 'Converts sheet
name to file path
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Print to PS file in nominated folder
s.PrintOut copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
printtofile:=True, collate:=True, PrToFileName:=PSFileName
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
End If
If sName = "FLEET" Then 'GoTo MoveOn 'Here you can identify sheets
you don't want printed
PSFileName = pdfFilePath & "\" & CoName & "2.PS" 'Converts sheet
name to file path
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Print to PS file in nominated folder
s.PrintOut copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
printtofile:=True, collate:=True, PrToFileName:=PSFileName
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
End If
MoveOn:
Next s

Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
Do Until fs.FileExists(PDFFileName)
Loop
fs.DeleteFile pdfFilePath & "\*.PS"
fs.DeleteFile pdfFilePath & "\*.LOG"

Application.ActivePrinter = OriginalPrinter 'Resets to original printer
selection in Excel
End Sub

Sub XLSheetsToPDF()
'**ORIGNAL MACRO COPIED FROM INTERNET**
'Make sure to add in reference to Acrobat Distiller
'Visual Basic Editor Tools References and select Acrobat Distiller
from the list
'This works for Acrobat 6, should be backwardly compatible to Acrobat 5

Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim s As Worksheet
Set fs = CreateObject("Scripting.FileSystemObject") 'Good old FSO
'retval = MsgBox("Do you want to proceed with printing all sheets to
individual PDF files?", vbOKCancel, "Print to PDF")
'If retval = vbCancel Then Exit Sub
'Browse for PDF Folder if it changes, otherwise enter path in FixedPath
variable
FixedPath = "" 'Leave blank if you want to browse
If FixedPath < "" Then
pdfFilePath = FixedPath
Else
InitialFileName = "PDFSave.pdf"
FileFilter = "Acrobat Files (*.pdf;*.ps),*.pdf;*.ps,All Files (*.*),
*.*"
FilterIndex = 1
Title = "Save PDF Files to Folder..."
pdfFilePath = Application.GetSaveAsFilename(InitialFileName,
FileFilter, FilterIndex, Title)
If pdfFilePath < False Then
pdfFilePath = fs.GetParentFolderName(pdfFilePath)
'MsgBox pdfFilePath
Else
Exit Sub
End If
End If
'Identify printers
OriginalPrinter = Application.ActivePrinter
'MsgBox OriginalPrinter
PDFPrinter = "Acrobat Distiller on Ne04:" 'Change this to suit the
Acrobat Distiller printer
'Cycle through each sheet, retrieves sheet name and generate PS file in
selected folder
For Each s In ThisWorkbook.Worksheets
sName = s.Name 'Sheet name
If sName = "Help" Then GoTo MoveOn 'Here you can identify sheets you
don't want printed
PSFileName = pdfFilePath & "\" & sName & ".PS" 'Converts sheet name
to file path
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Print to PS file in nominated folder
s.PrintOut copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
printtofile:=True, collate:=True, PrToFileName:=PSFileName
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
MoveOn:
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
Do Until fs.FileExists(PDFFileName)
Loop
fs.DeleteFile pdfFilePath & "\*.PS"
fs.DeleteFile pdfFilePath & "\*.LOG"

Application.ActivePrinter = OriginalPrinter 'Resets to original printer
selection in Excel
End Sub

Darcy
Many thanks. Haven't had a chance to try it, but it lloks like it'll sort my
problems.
If not, I'll be back!

;-))

"Darcy" wrote:

Hi RG,

Problem solved for me. The only glitch I still have is that the Save As box