Programming : Create PDF using VBA (Word, Excel, etc.)

Print Page | Close Window

Create PDF using VBA (Word, Excel, etc.)

Printed From: www.exp-systems.com
Category: PDF reDirect
Forum Name: Programming
Forum Discription: VBA and Batch Tools to control PDF reDirect Pro
URL: http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=39
Printed Date: 22 Nov 24 at 3:24AM

Topic: Create PDF using VBA (Word, Excel, etc.)

Posted By: ziplock
Subject: Create PDF using VBA (Word, Excel, etc.)
Date Posted: 28 Jul 04 at 11:57AM

This is a really sweet product you have here, my question is this; can the creation process be automated with MS Office Visual Basic?

I'm wanting to create a Excel macro that will create a PDF file using the SelectedSheet.Name and save it in a pre-selected folder without continously having to select the folder and type in the name of the file.

I found a similar PDF creation utility (FreeDist) which provided an answer that ought to have solved this issue, but VB does not recognize the naming solution (highlighted in red below).

Sub Excel_FreeDist_PDF()
     'Stephan van der Palen 07-11-2003
     'Macro to print directly to PDF from MS-Excel in combination with FreeDist
     Dim NewFileName As String
     Dim retval
     Dim FreeDistFolder As String
     Dim FreeDistWatchedFolder As String
     Dim FreeDistWatchedFolderDrive As String
     'The folder where FreeDist.exe is placed
     FreeDistFolder = "C:\My Documents\"
     'The watched folder of FreeDist
     FreeDistWatchedFolder = "C:\My Documents\FreeDist-Watch\"
     'Make FreeDists Watched Folder the current folder
     ChDir FreeDistWatchedFolder
     'Determine the name of the printed document
     NewFileName = ActiveWorkbook.Name & "_" & ActiveSheet.Name & ".prn"
     'Print "to file" to the current active printer
     'Make sure it's got good postscriptdrivers attached!!!!!!
     ActiveWindow.SelectedSheets.PrintOut Copies:=1, PrintToFile:=True, _
     Collate:=True, prttofilename:=NewFileName
     'Startup FreeDist with the newly created postscriptfile
     retval = Shell(FreeDistFolder & "freedist.exe " & FreeDistWatchedFolder & NewFileName)
End Sub

Any assistance would be greatly appreciated. Thank you.

Replies:

Posted By: Michel_K17
Date Posted: 31 Jul 04 at 1:55PM

I have written a small Visual Basic class and some Sample Code to show how to create a PDF file from Microsoft Office VBA.

You can download the following sample code as follows:

For v2.0.x: [ http://exp-systems.com/PDFreDirect/Download/PDF_Class4VB.zip - here ]
For v2.1.x: [ http://exp-systems.com/PDFreDirect/Download/PDF_Class4VB_Rev_D.zip - here ]

Posted By: ShowMasters
Date Posted: 11 Jul 06 at 6:27PM

Try this... it can be fussy, but it works. I'm presently looking for a more robust code:

    Dim YourFileName
     YourFileName = Whatever you are wanting to name your file

C:\Document Bin\in\ is the "Watched Folder" that needs to be set in

    Dim PSFileName As String, PDFFileName As String, DistillerCall As String
    Dim ReturnValue As Variant
    PSFileName = "C:\Document Bin\in\" & YourFileName & ".PS"
    PDFFileName = "C:\Document Bin\out\" & YourFileName & ".PDF"
    If Dir(PSFileName) <> "" Then Kill (PSFileName)
    If Dir(PDFFileName) <> "" Then Kill (PDFFileName)
    SendKeys PSFileName & "{ENTER}", False
    ActiveSheet.PrintOut , PrintToFile:=True
    PSFileName = Chr(34) & PSFileName & Chr(34)
    PDFFileName = Chr(34) & PDFFileName & Chr(34)
    DistillerCall = "C:\Program Files\Adobe\Acrobat 5.0\Distillr\Acrodist.exe"
    ReturnValue = Shell(DistillerCall, vbNormalFocus)
    If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName & "failed."

Posted By: ShowMasters
Date Posted: 11 Jul 06 at 6:45PM

BTW... you have to set Distiller as your default printer.

Posted By: dansam
Date Posted: 23 Dec 06 at 11:47AM

how to create a txt file using a macro in excel?

can i make it copy protected?

Posted By: Michel_K17
Date Posted: 25 Dec 06 at 6:32PM

Hi,

I am not sure I understand what you mean. PDF reDirect is for creating PDF files, not txt files. Sorry.

No, txt files cannot be protected.

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: jaywang66
Date Posted: 16 Apr 09 at 12:54PM

A little while ago I was able to develop excel script to print excel page as PDF file through intensive google search.

Sub Print_PDF()

    'need to check Reference to Acrobat Distiller in Tools --> References
    'Define the postscript and .pdf file names.

    Dim PSFileName As String
    Dim PDFFileName As String
    PSFileName = "c:\VBAPrintPDF\Sample.ps"
    PDFFileName = "c:\VBAPrintPDF\Sample.pdf"

    'Print the Excel range to the postscript file
    ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", _
    printtofile:=True, collate:=True, prtofilename:=PSFileName

    'Convert the postscript file to .pdf
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

But last week I found I couldn't print out color in PDF - only black and white. Then I used several days to search on google again but didn't find directly answers. With the help of all the different info I changed activePrinter from Acrobat Distiller to Adobe PDF. Then the problem was solved - I got colorful PDF file. Here is the new code - only a tiny change:

Sub Print_PDF()

    'need to check Reference to Acrobat Distiller in Tools --> References
    'Define the postscript and .pdf file names.

    Dim PSFileName As String
    Dim PDFFileName As String
    PSFileName = "c:\VBAPrintPDF\Sample.ps"
    PDFFileName = "c:\VBAPrintPDF\Sample.pdf"

    'Print the Excel range to the postscript file
    ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
    printtofile:=True, collate:=True, prtofilename:=PSFileName

    'Convert the postscript file to .pdf
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

Posted By: Michel_K17
Date Posted: 16 Apr 09 at 7:35PM

Hi,

   Thanks for the tip.

   In case anyone is wondering, the code above will work if you have Adobe Acrobat installed on your computer. If you are using PDF reDirect Pro, you should use the sample code provided [ http://www.exp-systems.com/forum_exp/forum_posts.asp?TID=254&FID=7&PR=3 - here ] instead.

   Cheers!

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: DeanB
Date Posted: 25 Sep 09 at 11:48AM

Greetings all,

I was able to use this command to export an Excel file as a pdf:

ActiveWorkbook.ExportAsFixedFormat xlTypePDF, buildSaveDest

where buildSaveDest is a variable containnig both filename and path.

I actually use the Sheets().Copy command to export a sheet as excel, modify is as needed, then after saving to pdf, I close it without saving:

ActiveWorkbook.Close SaveChanges:=False

HOWEVER, as I am working in SharePoint, I am having great difficulty finding code to checkin/checkout the pdf file in sharepoint. So I have to do this manually for each file after the export.

Any help with the sharepoint issue would be great.

Please note that while I did have full Adobe installed, there is a free patch to allow handling of pdf files on the microsoft site. The F1 help notes thats:

Note
An error will occur if the PDF add-in is not currently installed.

But the add-in is readily available and free.

Posted By: fmxds12
Date Posted: 09 Mar 10 at 8:19AM

try this plugin from MS, by using this, you should be able to Export any Office document to a PDF file.

http://www.microsoft.com/downloads/details.aspx?FamilyId=F1FC413C-6D89-4F15-991B-63B07BA5F2E5&displaylang=en

thanks - Lina
http://www.fomaxtech.com - custom application developers | http://www.articlesnatch.com/Article/Offering-Professional-Web-Services-At-Affordable-Costs/505557 - professional web services | http://www.articlesbase.com/databases-articles/remote-dba-consultants-and-database-management-services-1541396.html - remote dba consultants

Posted By: Pepito
Date Posted: 07 Sep 10 at 5:59AM

i am new to the forum as wellas into vba.

i have tried to use your macro below but it doesnt work.

is possible to get a code where i can just copy paste it?

Posted By: Michel_K17
Date Posted: 07 Sep 10 at 5:45PM

Hi Pepito,

   That will be tricky, but here is goes:

   1. Download just the ActiveX component from [ http://www.exp-systems.com/PDFreDirect/Download/ActiveX_Component_for_PDFR_Pro_v25002.exe - here ].

   2. Install it.

   3. Open an MS Word or an MS Excel document

   4. Open the VBA editor.

   5. Copy/Paste the following code. It won't do much, but it will confirm that the ActiveX was installed correctly.

Sub TestPDF()
    Dim myPDF As Object
    Dim UserSelection As String
    Set myPDF = New PDF_reDirect_v25002.Batch_RC_AXD
    UserSelection = myPDF.Utility_ShowAs_Dialog("My Title", "C:\", "MyFilename.pdf", 0)
    Debug.Print "FileLocation chosen is: " & UserSelection
    Set myPDF = Nothing
End Sub

   6. Look at the full code for more examples of what you can do like creating a PDF file using VBA.

I hope this helps.

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: Sara Myrphy
Date Posted: 11 Jul 11 at 3:15AM

Dear All,
I'm having the Adobe Acrobat SDk 8.0 and I need to convert our Word
Documentent and Excel Sheets into PDF format thru our VB6.0 program. I'm
able to manage to open the PDF documents and printing. I don't know hoe
to convert the documents thru VB program.

-------------
http://www.iflightsystems.com/

Posted By: Michel_K17
Date Posted: 11 Jul 11 at 9:05PM

Hi Sara,

We provide support for our ActiveX components only, but not for the Adobe Acrobat SDK. For that, you need to contact Adobe.

Cheers!

Michel

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: theART.SK
Date Posted: 13 Sep 11 at 1:04PM

Hi all,

I downloaded this version PDF reDirect v2.5.2, and installed ActiveX
when I run sample code; Sub TestPDF() ; for testing
It showed error in this line

[ Set myPDF = New PDF_reDirect_v25002.Batch_RC_AXD ]

Compile error:
User-defined type not defined

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
thanks for your support

Posted By: Michel_K17
Date Posted: 13 Sep 11 at 9:54PM

Hi,

   For some reason, it looks like the ActiveX did not get registered on your system.

   In your VBA IDE, go to Tools >> References >> Browse >> C:\Program Files\PDF reDirect\ActiveX_PDF_Pro.dll . This should auto-register the ActiveX.

   Alternatively, you can register manually using the regsvr32 command in the command tool (cmd.exe that comes with Windows). Let me know if you need help on how to register manually (or search google for "regsvr32").

   Cheers!

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: theART.SK
Date Posted: 14 Sep 11 at 2:30AM

thanks Michel,
I follow your advice ,and now it show dialog box for saving :)
I insert sub Test
,but no pdf file save in that directory

do we have sample code for newbie getting start?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
thanks for your support

Posted By: Michel_K17
Date Posted: 15 Sep 11 at 12:00AM

Hi,

   Sure! the sample Excel, Word and Access samples are very complete, and include sample code on how to create PDF files programmatically.

   The Sample code is available [ http://www.exp-systems.com/PDFreDirect/Download/VB_Sample_for_PDFR_Pro_v25002.exe - here ].

   Cheers!

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: MichealJhon
Date Posted: 10 Oct 11 at 2:23AM

Thank you for this links, I have download and like the simple card. and PDF reDirect Pro ...

Posted By: Gigi_
Date Posted: 05 Nov 11 at 2:38PM

hi,

i followed your instructions

downloaded the activex module and the vba examples

your example for excel works great

i'm not able to copy your code

i dont find the macros in my workheet

can you help me?

i work with office 2003 on os vista home and you last trial pro release

Posted By: Michel_K17
Date Posted: 06 Nov 11 at 10:04AM

Hi,

   Open the VBA editor in Excel. The "Developer" tab does not appear by default, so you may need to go into the File >> Options >> Customize Ribbon, and to choose to have it displayed.

   Now, choose the "Developer" Tab >> Visual Basic. Alternatively, you can press [ALT]+[F11] as a shortcut.

   The "Sheet1" contains the code for the button. The full code is in the Module called "modMain"

   Cheers!

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: Gigi_
Date Posted: 08 Nov 11 at 9:40AM

Thanks k17,

however i solved the problems opening the control toolbox menù and finding the link to the macro in the sheet1.

great program!

there are some bugs in translation (italian for me) but great program

Posted By: Michel_K17
Date Posted: 09 Nov 11 at 12:57AM

Hi Gigi,

   Thanks! Excellent.

   Let me know if you want to fix the translation bugs. I can send you the translation file.

   Cheers!

Michel

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: Gigi_
Date Posted: 09 Nov 11 at 12:22PM

Hi Michel,

Ok, I'm waiting for it.

Thanks!

Regards.

Gigi

Posted By: Michel_K17
Date Posted: 10 Nov 11 at 1:17AM

On it's way...

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: jaclin
Date Posted: 26 Nov 11 at 3:37AM

I too had been wondering whether the creation process can indeed be automated by the usage of MS Office Visual Basic. Thanks to the Sample code version 2.0 that is written by Michel K17, I was able to easily get past the issue. By the way, you can download the code from here itself.

-------------

Print Page | Close Window