Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 19th 05, 09:43 PM posted to microsoft.public.excel.setup
G
 
Posts: n/a
Default VB script for converting each worksheet into individual pdf files.

I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.

  #2   Report Post  
Old November 20th 05, 02:52 AM posted to microsoft.public.excel.setup
Barb Reinhardt
 
Posts: n/a
Default VB script for converting each worksheet into individual pdf files.

I assume you have the ability to Print to PDF? If you don't, a macro won't
do it.

"G" wrote in message
...
I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.



  #3   Report Post  
Old November 21st 05, 03:38 PM posted to microsoft.public.excel.setup
G
 
Posts: n/a
Default VB script for converting each worksheet into individual pdf fi

I do have Acrobat 6.0 Professional installed. I am trying to run a macro
that would go to each worksheet in a workbook and create a pdf with the name
of the worksheet. I used the codes below but keep getting an error at:

Dim myPDF As PdfDistiller

Thanks again.

G


Sub testmePrint()

Dim wks As Worksheet
Dim PSFileName As String
Dim PDFFileName As String

For Each wks In ActiveWorkbook.Worksheets
With ActiveSheet
PSFileName = "C:\Documents and
Settings\C05354\Desktop\Testing\.ps" & .Name & ".ps"
PDFFileName = "C:\Documents and
Settings\C05354\Desktop\Testing\.pdf" & .Name & ".pdf"
.Parent.PrintOut copies:=1, preview:=False,
ActivePrinter:="Adobe PDF", PrintToFile:=True, collate:=True,
prtoFilename:=PSFileName

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
End With

Next wks

End Sub



"Barb Reinhardt" wrote:

I assume you have the ability to Print to PDF? If you don't, a macro won't
do it.

"G" wrote in message
...
I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.




  #4   Report Post  
Old November 22nd 05, 12:57 AM posted to microsoft.public.excel.setup,microsoft.public.excel.programming
Barb Reinhardt
 
Posts: n/a
Default VB script for converting each worksheet into individual pdf files.

Maybe someone in the programming group can assist.

"G" wrote in message
...
I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.



  #5   Report Post  
Old November 22nd 05, 04:27 AM posted to microsoft.public.excel.setup,microsoft.public.excel.programming
Tom Ogilvy
 
Posts: n/a
Default VB script for converting each worksheet into individual pdf files.

There is no built in support for it. If you have Adobe Acrobat (not Acrobat
Reader), then you can select a print driver which you can print to to
develop a pdf. There are 3rd party packages as well; I believe some are
Free. Try doing a google search on PDF and Excel.

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in message
...
Maybe someone in the programming group can assist.

"G" wrote in message
...
I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.







  #6   Report Post  
Old November 23rd 05, 03:34 AM posted to microsoft.public.excel.setup
Jezebel
 
Posts: n/a
Default VB script for converting each worksheet into individual pdf files.

This is surprisingly messy, and the answer is different for different
versions of Acrobat. I've seen these methods used, none of which is entirely
reliable --

Method 1 (works well, but is specific to Acrobat 6) --

1. Add to your project a reference to Acrobat PDFMaker.

2. Before printing, set these registry keys to zero:

HKCU\Software\Adobe\Acrobat\PDFMaker\6.0\Global\Se ttings\PromptForPDFFileName
HKCU\Software\Adobe\Acrobat\PDFMaker\6.0\Global\Se ttings\ViewPDFFile

(You might want to store the original settings so you can restore them
afterwards.)

3. Iterate the Sheets collection and print each by activating it and calling
AcrobatPDFMaker.ConvertToPDFA. This prints the active sheet, saving it under
the name of the workbook, in the same folder as the workbook. Rename this
file to the sheet name.

There's a gotcha with this method: Acrobat will save the workbook if
.Saved = FALSE, then re-open it. This kills any object references you
have to the book or any derivative object.



Method 2 (messier, but works with other versions of Acrobat, and quite a lot
faster than method 1) --

1. Manually set Acrobat as the default printer and uncheck the 'Prompt for
file name' and 'View output' options.

2. Iterate the sheets and print them using .PrintOut. This creates the PDF
in the default Acrobat folder, which you can find by looking at the 'port'
in Printers set-up. default is MyDocuments. Rename the output file. Note
that the PrintOut function runs asynchronously (ie your macro doesn't stop
and wait -- unlike with method 1), so you'll need a delay loop or use the
Sleep API function to wait until it's finished.







"G" wrote in message
...
I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect Workbook vs Worksheet?? Dan B Excel Worksheet Functions 3 November 7th 05 09:02 PM
Import multiple text files into a single worksheet Scott Excel Discussion (Misc queries) 0 January 13th 05 10:22 PM
How do link to a remote worksheet using the path value in a field? Michael T. Links and Linking in Excel 3 December 11th 04 08:45 AM
Linking items GREATER THAN O on another worksheet in the same Work Eddie Shapiro Excel Discussion (Misc queries) 4 December 1st 04 02:55 PM
Worksheet name and Backward compatibility Rich Excel Discussion (Misc queries) 3 November 30th 04 06:10 PM


All times are GMT +1. The time now is 01:55 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017