Creating a word file and then making a pdf from excel

babysatch · #1 04-30-2017, 01:05 PM

Hi,

does anybody have any idea what am I missing in the code below:

I get this error:

Run time '5':
Invalid Procedure call or invalid argument

Here is the code:

Code:

Private Sub CommandButton1_Click()
    Dim MSWord As Object                            
    Set MSWord = CreateObject("Word.Application")   
    MSWord.Visible = True                          

    Datei = "D:\WordTest.doc"                   
    pdfName = "D:\WordTest.pdf"
                                                    
    MSWord.Documents.Add                           
    With MSWord.Selection
        .Font.Name = "Helvetica"                        
        .Font.Size = 20                           
        .Font.Color = wdColorRed                  
        .Font.Bold = True                       
        .Font.Italic = True                     
        .TypeText Text:="Text"                  
        .TypeParagraph                             
    End With

    MSWord.ActiveDocument.SaveAs Datei            
    

       MSWord.ActiveDocument.ExportAsFixedFormat OutputFileName:=pdfName, _
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, Item:= _
        wdExportDocumentContent, IncludeDocProps:=False, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
    

    
    
    MSWord.ActiveDocument.Close                   

    MSWord.Quit                                     
    Set MSWord = Nothing                            






End Sub

Ist it possible to open a pdf-file created by ActiveDocument.SaveAs2-Method?

Thank you.

macropod · #2 04-30-2017, 04:36 PM

Did you check which line was causing the error? Doubtless you'd get one with:
With MSWord.Selection
since MSWord in your code is the application, not the document.

And yes, the SaveAs2 method can produce PDFs. There are plenty of examples of that in these forums.

Try:

Code:

Private Sub CommandButton1_Click()
    Dim wdApp As Object, wdDoc As Object
    Const docName As String = "D:\WordTest.doc"
    Const pdfName As String = "D:\WordTest.pdf"
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Add
    With wdDoc
        .Font.Name = "Helvetica"
        .Font.Size = 20
        .Font.Color = wdColorRed
        .Font.Bold = True
        .Font.Italic = True
        .TypeText Text:="Text"
        .TypeParagraph
        'SaveAs2 Parameters: FileName, FileFormat, LockComments, Password, AddToRecentFiles, & _
            WritePassword, ReadOnlyRecommended, EmbedTrueTypeFonts, SaveNativePictureFormat, & _
            SaveFormsData, SaveAsAOCELetter, Encoding, InsertLineBreaks, AllowSubstitutions, & _
            LineEnding, AddBiDiMarks, CompatibilityMode
        .SaveAs2 docName, 0, , , False    '0 = wdFormatDocument
        .SaveAs2 pdfName, 17, , , False ' 17 = wdFormatPDF
        .Close False
    End With
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

gmayor · #3 04-30-2017, 08:27 PM

It looks like you are using late binding to Word in which case you need to use the numeric equivalents of all the Word specific commands e.g.

Code:

Private Sub CommandButton1_Click()
Dim MSWord As Object
Dim Datei As String
Dim pdfName As String
    Set MSWord = CreateObject("Word.Application")
    MSWord.Visible = True

    Datei = "D:\WordTest.doc"
    pdfName = "D:\WordTest.pdf"

    MSWord.Documents.Add
    With MSWord.Selection
        .Font.Name = "Helvetica"
        .Font.Size = 20
        .Font.Color = 255
        .Font.Bold = True
        .Font.Italic = True
        .TypeText Text:="Text"
        .TypeParagraph
    End With

    MSWord.ActiveDocument.SaveAs Datei

    MSWord.ActiveDocument.ExportAsFixedFormat _
            OutputFileName:=pdfName, _
            ExportFormat:=17, _
            OpenAfterExport:=False, _
            OptimizeFor:=0, _
            Range:=0, _
            Item:=0, _
            IncludeDocProps:=False, _
            KeepIRM:=True, _
            CreateBookmarks:=0, _
            DocStructureTags:=True, _
            BitmapMissingFonts:=True, _
            UseISO19005_1:=False

    MSWord.ActiveDocument.Close
    MSWord.Quit
    Set MSWord = Nothing

End Sub

babysatch · #4 05-01-2017, 01:42 AM

Hi,

thank you for your reply.

Maybe I didn't express myself clearly.

What I meant is: "Is it possible that the pdf file created by Save2-Method automatically opens?"

Thank you.

babysatch · #5 05-01-2017, 01:46 AM

Hi again,

your reply was very helpful.

It's working out very nicely now.

...

macropod · #6 05-01-2017, 01:50 AM

Well, technically it's already open when you save it as a PDF in Word - just not in Adobe, etc. If you want to open the PDF in Adobe, you'll have to add code to do that (merely saving it via SaveAs2 won't cause it to do so). It should be fairly obvious from Greg's code that the ExportAsFixedFormat method can be used to automatically open the PDF.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel does not work if any other excel file selected in any folder	tonytonytony	Excel	3	03-10-2016 04:18 PM
Making Venn Diagram in Excel	kareemva	Excel Programming	5	02-16-2016 12:10 AM
How to update source excel file with changes made to insserted excel file in OneNote	winwell	OneNote	1	11-03-2015 12:19 PM
sending data from UserForm of existing excel file to a new excel file	saltlakebuffalo	Excel Programming	0	02-13-2014 10:55 PM
how to copy some information from one excel file to another excel file	tomlam	Excel Programming	4	10-01-2013 03:06 PM