Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
Programming
 EXP Systems Forum : PDF reDirect : Programming
Topic: Create PDF using VBA (Word, Excel, etc.) Post Reply Post New Topic
Author Message
ziplock
Newbie
Newbie


Joined: 28 Jul 04
Posts: 1
Quote ziplock Replybullet Topic: Create PDF using VBA (Word, Excel, etc.)
    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.

IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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:





Edited by Michel_K17
IP IP Logged
ShowMasters
Newbie
Newbie
Avatar

Joined: 11 Jul 06
Location: United States
Posts: 2
Quote ShowMasters Replybullet 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."
IP IP Logged
ShowMasters
Newbie
Newbie
Avatar

Joined: 11 Jul 06
Location: United States
Posts: 2
Quote ShowMasters Replybullet Posted: 11 Jul 06 at 6:45PM
BTW... you have to set Distiller as your default printer.
IP IP Logged
dansam
Newbie
Newbie


Joined: 23 Dec 06
Posts: 1
Quote dansam Replybullet Posted: 23 Dec 06 at 11:47AM
how to create a txt file using a macro in excel?
can i make it copy protected?Wink
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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
IP IP Logged
jaywang66
Newbie
Newbie
Avatar

Joined: 16 Apr 09
Location: United States
Posts: 1
Quote jaywang66 Replybullet 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
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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 [here] instead.

   Cheers!

Michel Korwin-Szymanowski
EXP Systems LLC
IP IP Logged
DeanB
Newbie
Newbie
Avatar

Joined: 25 Sep 09
Location: United States
Posts: 1
Quote DeanB Replybullet 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.
IP IP Logged
fmxds12
Newbie
Newbie


Joined: 09 Mar 10
Posts: 1
Quote fmxds12 Replybullet 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
custom application developers | professional web services | remote dba consultants
IP IP Logged
Pepito
Newbie
Newbie


Joined: 07 Sep 10
Posts: 1
Quote Pepito Replybullet Posted: 07 Sep 10 at 5:59AM
hi
 
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?
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet Posted: 07 Sep 10 at 5:45PM
Hi Pepito,

   That will be tricky, but here is goes:

   1. Download just the ActiveX component from [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
IP IP Logged
Sara Myrphy
Newbie
Newbie


Joined: 11 Jul 11
Posts: 1
Quote Sara Myrphy Replybullet 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/
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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
IP IP Logged
theART.SK
Newbie
Newbie
Avatar

Joined: 13 Sep 11
Location: Thailand
Posts: 2
Quote theART.SK Replybullet 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
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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
IP IP Logged
theART.SK
Newbie
Newbie
Avatar

Joined: 13 Sep 11
Location: Thailand
Posts: 2
Quote theART.SK Replybullet 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
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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 [here].

   Cheers!

Michel Korwin-Szymanowski
EXP Systems LLC
IP IP Logged
MichealJhon
Newbie
Newbie
Avatar

Joined: 06 Oct 11
Location: United States
Posts: 7
Quote MichealJhon Replybullet Posted: 10 Oct 11 at 2:23AM
Thank you for this links, I have download and like the simple card. and     PDF reDirect Pro ...  

IP IP Logged
Gigi_
Newbie
Newbie
Avatar

Joined: 05 Nov 11
Location: Italy
Posts: 3
Quote Gigi_ Replybullet 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
 
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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
IP IP Logged
Gigi_
Newbie
Newbie
Avatar

Joined: 05 Nov 11
Location: Italy
Posts: 3
Quote Gigi_ Replybullet 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
 
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet 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
IP IP Logged
Gigi_
Newbie
Newbie
Avatar

Joined: 05 Nov 11
Location: Italy
Posts: 3
Quote Gigi_ Replybullet Posted: 09 Nov 11 at 12:22PM

Hi Michel,

Ok, I'm waiting for it.
 
Thanks!
 
Regards.
 
Gigi
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1673
Quote Michel_K17 Replybullet Posted: 10 Nov 11 at 1:17AM
On it's way...
Michel Korwin-Szymanowski
EXP Systems LLC
IP IP Logged
jaclin
Newbie
Newbie


Joined: 24 Nov 11
Posts: 3
Quote jaclin Replybullet 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.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum