I use the following Code to print muliple PDF's into a new folder. How can I carry over the "strDefpath & strDirname" string to the sub module (PrintToPDF) rather than duplicate the string in the sub module. The problem is because it takes several minutes to create all of the PDF's 2 or 3 folders get created instead of just one.
Appreciate any help with this,
Ben
Code:
Sub B_Create_Client_Sheet_PDF_Mon()
Dim strDirname, strDefpath As String
strDirname = Format(Now(), "yy-mm-dd_hhmm") 'Range("A1").Value ' New directory name
strDefpath = "C:\Users\BWS-Dell_540\Desktop\Client Sheet\" 'Default path name
MkDir strDefpath & strDirname
Sheets("Schedule").Select
CreateListAlpha_A 'update before printing
Application.ScreenUpdating = False
Range("C4:C5").Select
100
If ActiveCell.Value = "" Or ActiveCell.Value = "OFF" Then
ActiveCell.Offset(2, 0).Select
Else
Selection.Copy
Sheets("Client Sheet").Select
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Schedule").Select
Application.CutCopyMode = False
Cells(2, ActiveCell.Column).Copy
Sheets("Client Sheet").Select
Range("AO3").Select
ActiveSheet.Paste
'B_Add_Hard_Returns
PrintToPDF
'ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True ', Preview:=True
Sheets("Schedule").Select
ActiveCell.Offset(2, 0).Select
End If
If ActiveCell.Value = "" Or ActiveCell.Value = "OFF" Then
ActiveCell.Offset(2, 0).Select
Else
Selection.Copy
Sheets("Client Sheet").Select
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Schedule").Select
Application.CutCopyMode = False
Cells(2, ActiveCell.Column).Copy
Sheets("Client Sheet").Select
Range("AO3").Select
ActiveSheet.Paste
PrintToPDF
Sheets("Schedule").Select
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = "" Or ActiveCell.Value = "OFF" Then
If ActiveCell.Address = "$DD$8" Then
GoTo 200
End If
ActiveCell.Offset(-4, 7).Select
Else
Selection.Copy
Sheets("Client Sheet").Select
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Schedule").Select
Application.CutCopyMode = False
Cells(2, ActiveCell.Column).Copy
Sheets("Client Sheet").Select
Range("AO3").Select
ActiveSheet.Paste
PrintToPDF
Sheets("Schedule").Select
If ActiveCell.Address = "$DD$8" Then
GoTo 200
End If
ActiveCell.Offset(-4, 7).Select
End If
GoTo 100
200
Range("A3").Select
Application.ScreenUpdating = True
End Sub
Code:
Sub PrintToPDF()
'Author : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from [URL]http://sourceforge.net/projects/pdfcreator/[/URL])
' Designed for early bind, set reference to PDFCreator
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim bRestart As Boolean
''''''''''//////Moved to Print_PDF
Dim strFilename, strDirname, strPathname, strDefpath As String
strDirname = Format(Now(), "yy-mm-dd_hhmm") 'Range("A1").Value ' New directory name
strDefpath = "C:\Users\BWS-Dell_540\Desktop\Client Sheet\" 'Default path name
'MkDir strDefpath & strDirname
''''''''''//////
Application.ScreenUpdating = False
'/// Change the output file name here! ///
'sPDFName = "testPDF.pdf"
'sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
sPDFName = ActiveSheet.Range("X3").Value & " - " & ActiveSheet.Range("O1").Value & " - " & ActiveSheet.Range("AQ4").Value & ".pdf"
sPDFPath = strDefpath & strDirname
'sPDFPath = "C:\Users\BWS-Dell_540\Desktop\Client Sheet"
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
'Activate error handling and turn off screen updates
On Error GoTo EarlyExit
Application.ScreenUpdating = False
Set pdfjob = New PDFCreator.clsPDFCreator
'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False
'Assign settings for PDF job
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Delete the PDF if it already exists
If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
'Print the document to PDF
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until the PDFCreator queue is clear
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub
Appreciate any help with this,
Ben