Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Saving a Word document as PDF from en Excel macro

Hello NG

I have an Excel macro, that creates a number single Word-documents (not mail merge), based on data from en Excel spreadsheet, using bookmarks in the Word document. I want to save the documents as PDF files, but so far with no success.

My code startes with:

On Error Resume Next
Set Wdapp = GetObject(, "Word.Application")
If Err.Number < 0 Then
Set Wdapp = CreateObject("Word.Application")
End If

<followed by a lot of of transfer from Excel to Word stuff and then I try to save as PDF with:

doknavn = ldir & "\" & a & ".pdf"
Wdapp.ChangeFileOpenDirectory ldir
Wdapp.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
doknavn, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False

but nothing happens. Apparently no pdf files are created. If i save as Word documents in stead, like in this test

Wdapp.ActiveDocument.SaveAs Filename:="C:\test\" & Navn & number & ".doc"

it Works perfectly. If I run the same code in a Word-document, it works as well. Can't you automate exports to pdf?

Anyone?

Jan

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Saving a Word document as PDF from en Excel macro

Le 27/02/2014 05:10, a écrit :
Hello NG

I have an Excel macro, that creates a number single Word-documents (not mail merge),

based on data from en Excel spreadsheet, using bookmarks in the Word document.
I want to save the documents as PDF files.


Hello
Printing to a Pdf file is different than to a Word file.
Have a look at
www.excelguru.ca
Regards
J@@

tested successfully using PDFCreator 0.9.1, GPLGhostscript.exe download package.
Excel versions tested include:
1. Excel 2003
2. Excel 2007

'**********************Print a Single Worksheet to a PDF File:
Option Explicit
Sub PrintToPDF_Late()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for late bind, no references req'd

Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String

'/// Change the output file name here! ///
sPDFName = "testPDF.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

With pdfjob
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'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 PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

'*******************Print Multiple Worksheets to Multiple PDF Files:
Option Explicit

Sub PrintToPDF_MultiSheet_Late()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for late bind, no references req'd

Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long

Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
For lSheet = 1 To ActiveWorkbook.Sheets.Count
'Check if worksheet is empty and skip if so
If Not IsEmpty(ActiveSheet.UsedRange) Then
With pdfjob
'/// Change the output file name here! ///
sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print the document to PDF
Worksheets(lSheet).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 PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
End If
Next lSheet
pdfjob.cClose
Set pdfjob = Nothing
End Sub

'**************Print Multiple Worksheets to a Single PDF File:
Option Explicit

Sub PrintToPDF_MultiSheetToOne_Late()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for late bind, no references req'd

Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim lTtlSheets As Long

'/// Change the output file name here! ///
sPDFName = "Consolidated.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

'Make sure the PDF printer can start
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "Error!"
Exit Sub
End If

'Set all defaults
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print the document to PDF
lTtlSheets = Application.Sheets.Count
For lSheet = 1 To Application.Sheets.Count
On Error Resume Next 'To deal with chart sheets
If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
Else
lTtlSheets = lTtlSheets - 1
End If
On Error GoTo 0
Next lSheet

'Wait until all print jobs have entered the print queue
Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
DoEvents
Loop

'Combine all PDFs into a single file and stop the printer
With pdfjob
.cCombineAll
.cPrinterStop = False
End With

'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

'*********************
Sub ImprimEn1PDF_SelectFeuils()
'Author : Ken Puls (www.excelguru.ca)
'mod. pour sélection de feuilles J@@ conseils DanielCo
'Macro Purpose: Print to PDF file using **PDFCreator**
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for late bind, no references req'd

Dim PdfJob As Object
Dim SpdFname As String
Dim SpdFpath As String
Dim lSheet As Long
Dim lTtlSheets As Long
Dim sh As Worksheet

'/// Change the output file name here! ///
SpdFname = "Consolidated.pdf"
SpdFpath = ActiveWorkbook.Path & Application.PathSeparator
Set PdfJob = CreateObject("PDFCreator.clsPDFCreator")

'Make sure the PDF printer can start
If PdfJob.cstart("/NoProcessingAtStartup") = False Then
MsgBox "Imposssible d'initialiser PDFCreator.", vbCritical + _
vbOKOnly, "Erreur!"
Exit Sub
End If

'Set all defaults
With PdfJob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = SpdFpath
.cOption("AutosaveFilename") = SpdFname
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Imprimer les feuilles sélectionnées
lTtlSheets = ActiveWorkbook.Windows(1).SelectedSheets.Count
For Each sh In ActiveWorkbook.Windows(1).SelectedSheets
On Error Resume Next 'To deal with chart sheets
If Not IsEmpty(sh.UsedRange) Then
sh.PrintOut copies:=1, ActivePrinter:="PDFCreator"
End If
On Error GoTo 0
Next sh

'Wait until all print jobs have entered the print queue
Do Until PdfJob.ccountofprintjobs = lTtlSheets
DoEvents
Loop

'Combine all PDFs into a single file and stop the printer
With PdfJob
.ccombineall
.cPrinterStop = False
End With

'Wait until PDF creator is finished then release the objects
Do Until PdfJob.ccountofprintjobs = 0
DoEvents
Loop
PdfJob.cClose
Set PdfJob = Nothing
End Sub
'***************

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Saving a Word document as PDF from en Excel macro

Thank you. I have no problems printing spreadsheets. My problem is printing a Word document from a macro running in Excel.

Jan

Hello

Printing to a Pdf file is different than to a Word file.

Have a look at www.excelguru.ca

Regards

J@@



tested successfully using PDFCreator 0.9.1, GPLGhostscript.exe download package.

Excel versions tested include:

1. Excel 2003

2. Excel 2007



'**********************Print a Single Worksheet to a PDF File:

Option Explicit

Sub PrintToPDF_Late()

'Author : Ken Puls (www.excelguru.ca)

'Macro Purpose: Print to PDF file using PDFCreator

' (Download from http://sourceforge.net/projects/pdfcreator/)

' Designed for late bind, no references req'd



Dim pdfjob As Object

Dim sPDFName As String

Dim sPDFPath As String



'/// Change the output file name here! ///

sPDFName = "testPDF.pdf"

sPDFPath = ActiveWorkbook.Path & Application.PathSeparator



'Check if worksheet is empty and exit if so

If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub



Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")



With pdfjob

If .cStart("/NoProcessingAtStartup") = False Then

MsgBox "Can't initialize PDFCreator.", vbCritical + _

vbOKOnly, "PrtPDFCreator"

Exit Sub

End If

.cOption("UseAutosave") = 1

.cOption("UseAutosaveDirectory") = 1

.cOption("AutosaveDirectory") = sPDFPath

.cOption("AutosaveFilename") = sPDFName

.cOption("AutosaveFormat") = 0 ' 0 = PDF

.cClearCache

End With



'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 PDF creator is finished then release the objects

Do Until pdfjob.cCountOfPrintjobs = 0

DoEvents

Loop

pdfjob.cClose

Set pdfjob = Nothing

End Sub



'*******************Print Multiple Worksheets to Multiple PDF Files:

Option Explicit



Sub PrintToPDF_MultiSheet_Late()

'Author : Ken Puls (www.excelguru.ca)

'Macro Purpose: Print to PDF file using PDFCreator

' (Download from http://sourceforge.net/projects/pdfcreator/)

' Designed for late bind, no references req'd



Dim pdfjob As Object

Dim sPDFName As String

Dim sPDFPath As String

Dim lSheet As Long



Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

sPDFPath = ActiveWorkbook.Path & Application.PathSeparator



If pdfjob.cStart("/NoProcessingAtStartup") = False Then

MsgBox "Can't initialize PDFCreator.", vbCritical + _

vbOKOnly, "PrtPDFCreator"

Exit Sub

End If

For lSheet = 1 To ActiveWorkbook.Sheets.Count

'Check if worksheet is empty and skip if so

If Not IsEmpty(ActiveSheet.UsedRange) Then

With pdfjob

'/// Change the output file name here! ///

sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"

.cOption("UseAutosave") = 1

.cOption("UseAutosaveDirectory") = 1

.cOption("AutosaveDirectory") = sPDFPath

.cOption("AutosaveFilename") = sPDFName

.cOption("AutosaveFormat") = 0 ' 0 = PDF

.cClearCache

End With



'Print the document to PDF

Worksheets(lSheet).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 PDF creator is finished then release the objects

Do Until pdfjob.cCountOfPrintjobs = 0

DoEvents

Loop

End If

Next lSheet

pdfjob.cClose

Set pdfjob = Nothing

End Sub



'**************Print Multiple Worksheets to a Single PDF File:

Option Explicit



Sub PrintToPDF_MultiSheetToOne_Late()

'Author : Ken Puls (www.excelguru.ca)

'Macro Purpose: Print to PDF file using PDFCreator

' (Download from http://sourceforge.net/projects/pdfcreator/)

' Designed for late bind, no references req'd



Dim pdfjob As Object

Dim sPDFName As String

Dim sPDFPath As String

Dim lSheet As Long

Dim lTtlSheets As Long



'/// Change the output file name here! ///

sPDFName = "Consolidated.pdf"

sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")



'Make sure the PDF printer can start

If pdfjob.cStart("/NoProcessingAtStartup") = False Then

MsgBox "Can't initialize PDFCreator.", vbCritical + _

vbOKOnly, "Error!"

Exit Sub

End If



'Set all defaults

With pdfjob

.cOption("UseAutosave") = 1

.cOption("UseAutosaveDirectory") = 1

.cOption("AutosaveDirectory") = sPDFPath

.cOption("AutosaveFilename") = sPDFName

.cOption("AutosaveFormat") = 0 ' 0 = PDF

.cClearCache

End With



'Print the document to PDF

lTtlSheets = Application.Sheets.Count

For lSheet = 1 To Application.Sheets.Count

On Error Resume Next 'To deal with chart sheets

If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then

Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"

Else

lTtlSheets = lTtlSheets - 1

End If

On Error GoTo 0

Next lSheet



'Wait until all print jobs have entered the print queue

Do Until pdfjob.cCountOfPrintjobs = lTtlSheets

DoEvents

Loop



'Combine all PDFs into a single file and stop the printer

With pdfjob

.cCombineAll

.cPrinterStop = False

End With



'Wait until PDF creator is finished then release the objects

Do Until pdfjob.cCountOfPrintjobs = 0

DoEvents

Loop

pdfjob.cClose

Set pdfjob = Nothing

End Sub



'*********************

Sub ImprimEn1PDF_SelectFeuils()

'Author : Ken Puls (www.excelguru.ca)

'mod. pour sélection de feuilles J@@ conseils DanielCo

'Macro Purpose: Print to PDF file using **PDFCreator**

' (Download from http://sourceforge.net/projects/pdfcreator/)

' Designed for late bind, no references req'd



Dim PdfJob As Object

Dim SpdFname As String

Dim SpdFpath As String

Dim lSheet As Long

Dim lTtlSheets As Long

Dim sh As Worksheet



'/// Change the output file name here! ///

SpdFname = "Consolidated.pdf"

SpdFpath = ActiveWorkbook.Path & Application.PathSeparator

Set PdfJob = CreateObject("PDFCreator.clsPDFCreator")



'Make sure the PDF printer can start

If PdfJob.cstart("/NoProcessingAtStartup") = False Then

MsgBox "Imposssible d'initialiser PDFCreator.", vbCritical + _

vbOKOnly, "Erreur!"

Exit Sub

End If



'Set all defaults

With PdfJob

.cOption("UseAutosave") = 1

.cOption("UseAutosaveDirectory") = 1

.cOption("AutosaveDirectory") = SpdFpath

.cOption("AutosaveFilename") = SpdFname

.cOption("AutosaveFormat") = 0 ' 0 = PDF

.cClearCache

End With



'Imprimer les feuilles sélectionnées

lTtlSheets = ActiveWorkbook.Windows(1).SelectedSheets.Count

For Each sh In ActiveWorkbook.Windows(1).SelectedSheets

On Error Resume Next 'To deal with chart sheets

If Not IsEmpty(sh.UsedRange) Then

sh.PrintOut copies:=1, ActivePrinter:="PDFCreator"

End If

On Error GoTo 0

Next sh



'Wait until all print jobs have entered the print queue

Do Until PdfJob.ccountofprintjobs = lTtlSheets

DoEvents

Loop



'Combine all PDFs into a single file and stop the printer

With PdfJob

.ccombineall

.cPrinterStop = False

End With



'Wait until PDF creator is finished then release the objects

Do Until PdfJob.ccountofprintjobs = 0

DoEvents

Loop

PdfJob.cClose

Set PdfJob = Nothing

End Sub

'***************


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
Macro For Saving Worksheet to Word Document with A1 as filename ghillman89 Excel Programming 0 March 5th 12 03:58 AM
Macro to open a word document from excel MarkC Excel Programming 11 April 3rd 07 09:34 AM
Printing a Word document from an excel macro Mike_Shimandle Excel Programming 1 September 30th 04 04:13 PM
Printing a Word document from an excel macro mshimandle Excel Programming 0 September 30th 04 04:07 PM
using excel macro to activate word document Amy[_4_] Excel Programming 1 August 12th 03 12:40 AM


All times are GMT +1. The time now is 11:40 AM.

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

About Us

"It's about Microsoft Excel"