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
)
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
