Here is some code that automatically converts files to pdf. Hopefully there is enough information that you can adapt it to your personal needs. This message contains a version for Excel files only, the next message gives a way of combining different types of file (.xls/.doc/.txt etc.)
Rich (BB code):
'=============================================================================
'- CODE TO MAKE A PDF FILE USING PDF995 APPLICATION
'- BY WRITING TEXT FILE 'pdf995.ini' -
'- ** Version 1 : EXCEL WORKSHEET(S) FROM A SINGLE WORKBOOK
'- I am using the paid for version of PDF995 with no advertising
'- Brian Baulsom April 2010
'=============================================================================
'- PDF995 is free from http://www.pdf995.com/download.html
'- It mimics a printer and is normally used by an application as such -
'- when it prompts for a .pdf file name instead of printing.
'-
'- There is a separate free utility 'pdfEdit.exe' that enables a whole
'- host of possible output options. It works by changing the 'pdf995.ini' file
'- which is installed by the application (you will need to find where).
'- I used it to discover the parameters required. If you need a variety of
'- output scenarios, this is for you. Part of the purpose of this code is to
'- give the basics to set up your own personal output option. So you can do the same.
'-
'- This code is to set up a few common scenarios so they automatically make
'- a .pdf file with no user intervention. The required scenario is selected
'- and set up by changing the code where necessary. It can be run from any
'- Microsoft Office application (eg. amend 'SelectedSheets' to 'ActiveDocument'
'- in Word) and could be used in a Visual Basic project.
'-
'- The code deletes the previous version of 'pdf995.ini'
'- The code checks the date/time of the output file to ensure the process
'- is finished, except when using the PDF995 Save As dialogue method.
'- Some parameters are automatically added by PDF995
'- PDF995 overwrites files without warning. Some checking is done here
'- but need to take care.
'-----------------------------------------------------------------------------
'- ** There are some parameter details at the end of the code ***
'=============================================================================
Dim PDF995ini As String
Dim MyActivePrinter As String
Dim OutPutFile As String
Dim OutPutFolder As String
Dim InitialDir As String ' used with Save As dialogue & 'Output File="" '
'Dim UserFile As String ' output pdf not used here
'Dim Launch As String ' Set by PDF995. Same as User File (Auto Launch=1)
'Dim DocumentName As String ' Set by PDF995. Source document file name
Dim MyOutputScenario As String ' different output options
Dim MyFileDateTime As Date
Dim wb As String
Dim CheckFile As String ' file name use in Wait loop
Dim CheckCount As Integer ' count tries to timeout
'=============================================================================
'- MAIN ROUTINE
'=============================================================================
Sub MAKE_PDF_FROM_EXCEL()
'*************************************************************************
'- USER SETTINGS
'*************************************************************************
'- SELECT SCENARIO (amend as required)
MyOutputScenario = "Normal" ' pdf = workbook file name & folder.
'MyOutputScenario = "Folder\File" ' different folder and/or file name
'MyOutputScenario = "PromptForFile" ' use Save As dialog & initial folder set
'*************************************************************************
OutPutFile = ""
OutPutFolder = ""
MyActivePrinter = Application.ActivePrinter
'=========================================================================
'- NEW BASIC .INI FILE
PDF995ini = "C:\Program Files (x86)\pdf995\res\pdf995.ini"
Kill PDF995ini
'-------------------------------------------------------------------------
Open PDF995ini For Append As 1
Print #1, "[Parameters]"
Print #1, "Install=1"
Print #1, "Quiet=0"
Print #1, "Use GPL Ghostcript=1"
Print #1, "AutoLaunch=1" ' =0 to not run Acrobat at the end
'---------------------------------------------------------------------
'- SET OUTPUT FILE & OUTPUT FOLDER
Select Case MyOutputScenario
Case "Normal" ' all selected worksheets
OutPutFile = "SAMEASDOCUMENT"
OutPutFolder = ActiveWorkbook.Path
Case "Folder\File" ' all selected worksheets
OutPutFile = "F:\PDF Test.pdf" ' needs full path
OutPutFolder = "F:" ' no final "\"
Case "PromptForFile"
OutPutFile = "" ' PDF995 runs Save As
InitialDir = "F:\test" ' base Save As folder
Case Else
MsgBox ("Scenario " & MyOutputScenario & " is not a valid name")
Exit Sub
End Select
'---------------------------------------------------------------------
'- ADD PARAMETERS TO THE INI FILE
If OutPutFile = "" Then ' PDF995 will run the Save As dialog
Print #1, "OutPut File="
Print #1, "Initial Dir=" & InitialDir
Else
Print #1, "OutPut File=" & OutPutFile
End If
'---------------------------------------------------------------------
If OutPutFolder <> "" Then Print #1, "Output Folder=" & OutPutFolder
'---------------------------------------------------------------------
Close #1
'=========================================================================
'- CHECK IF FILE ALREADY EXISTS
wb = ActiveWorkbook.Name
CheckFile = IIf(OutPutFile = "SAMEASDOCUMENT", ActiveWorkbook.Path & "\" _
& Left(wb, Len(wb) - 4) & ".pdf", OutPutFile)
f = Dir(CheckFile)
If f <> "" Then
rsp = MsgBox(CheckFile & vbCr & " already exists and will be replaced.", vbOKCancel)
If rsp = vbCancel Then
Application.ActivePrinter = MyActivePrinter
Exit Sub
End If
End If
'=========================================================================
'- PRINT SELECTED WORKSHEET (changes Excel ActivePrinter)
MyFileDateTime = Now ' TIME FILE SETUP STARTS
ActiveWindow.SelectedSheets.PrintOut Collate:=True, ActivePrinter:="PDF995"
'-------------------------------------------------------------------------
'- WAIT UNTIL THE NEW OUTPUT FILE EXISTS
CheckCount = 1
If CheckFile <> "" Then
Do
Application.Wait Now + TimeValue("00:00:02") ' wait 2 seconds
CheckCount = CheckCount + 1
Loop While FileDateTime(CheckFile) <= MyFileDateTime _
And CheckCount < 15 ' 15 x 2 seconds = 30 seconds and give up
'=====================================================================
'- RESET THE ACTIVE PRINTER
Application.ActivePrinter = MyActivePrinter
MsgBox ("File Saved " & vbCr & CheckFile & vbCr _
& IIf(CheckCount > 14, " Took maximum time to run" & vbCr & "Please check the file", ""))
End If
'-------------------------------------------------------------------------
End Sub
'=============================================================================
'===========================================================================================================
'- SOME PARAMETER DETAILS (there are loads more possible depending on functions required)
'- "not required" = automatic addition by PDF995 depending on other parameters.
'===========================================================================================================
' AutoLaunch=
' 0 - no display of .pdf when finished
' 1 - Automaticall display PDF in default app. after printing (eg.Acrobat)
'-----------------------------------------------------------------------------------------------------------
' Output File=
' - (left blank) uses Save As dialog
' 1 - incremented file name starting at this number (eg. 1.pdf, 2.pdf..)
' PDF995 automatically increments this number & changes other parameters if it exists
' (Not tested. Saved to 'Initial Dir =' as far as I can tell)
' SAMEASDOCUMENT - auto name & save in default folder ..\pdf995\output
' or Output Folder if set (sets 'User File= ... .pdf ')
' F:\test\test.pdf - eg. uses same file name set for all output
'-----------------------------------------------------------------------------------------------------------
' Initial Dir=
' F:\test - eg. for Save As dialog
'-----------------------------------------------------------------------------------------------------------
' Output Folder=
' F:\test - eg. uses this folder instead of default '..\pdf995\output'
' use with 'Output File=SAMEASDOCUMENT'
'-----------------------------------------------------------------------------------------------------------
' Fixed Dir=
' F:\test - eg. Save As opens at this folder use with AutoLaunch=1
'-----------------------------------------------------------------------------------------------------------
' Document Name = (Not required)
' book1.xls - eg. source document
'-----------------------------------------------------------------------------------------------------------
' Combine Documents=
' 1 - to combine consecutively "printed" documents into 1 .pdf (from any application !!)
' they are added individually so can stop\start any time
' 0 - does not combine documents (default value so not usually needed)
'-----------------------------------------------------------------------------------------------------------
' Combine Last = (Not required if not combining)
' 0 - does not combine with previous print job
' 1 - combines with previous print job
'-----------------------------------------------------------------------------------------------------------
' User File = (Not required.automatic by PDF995)
' F:\test\test.pdf - eg. Don't know what this is for.
'-----------------------------------------------------------------------------------------------------------
