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