Excel VBA - generating and manipulating Word documents -Office 2010

[WarwickW]

I have a large program to extract data from 7 tables from a database and put all required data in a single workbook with 7 worksheets, each feeding a part of the ultimate word document. I am a volunteer trying to produce a Course Brochure for a not-for-profit educational organisation for older people. Because of the formatting requirements I wish to use Word documents to convert the data to the right format before generating a pdf file to go to the printers.
I am in the process of using Excel VBA code to generate the word documents. I have the following References activated: Visual Basic For Applications; Microsoft Excel 14.0 Object Library; OLE Automation; Microsoft Office 14.0 Object Library; Microsoft Forms 2.0 Object Library; Microsoft Word 14.0 Object Library.
In quite a number of cases when I try to run Word VBA statements within the Excel VBA code I get error messages which seem to indicate I am not doing something needed. In addition to the above references, I select or generate a word application:

Set objWord = GetObject(class:="Word Application")
    Err.Clear
    If objWord Is Nothing Then Set objWord = CreateObject(class:="Word.Application")
    If Err.Number = 429 Then
        MsgBox "Microsoft Word could not be found, terminating."
    End If
    objWord.Visible = True                                  '   make the MS Word visible
    objWord.Activate

(objWord is defined as Public Word.Application) then create each document with code such as

    Set oFrontCoverDocument = objWord.Documents.Add

where oFrontCoverDocument is defined as a Public Word.Document.
I successfully select a worksheet in Excel and paste it as a one-column table into the new document. I have a blank first cell and select that cell and try to insert a logo from a file:

    oFrontCoverDocument.Tables(1).Cell(1, 1).Select
    Selection.InlineShapes.AddPicture Filename:=sOutputFilePath & sColourFileName, LinkToFile:=False, SaveWithDocument:=True

The cell is selected correctly but the AddPicture command results in "Run-time error '438': Object doesn't support this property or method".
If I make the equivalent macro in the word document (with appropriate definitions) it works correctly, inserting the logo.
There are lots of Word commands that work, but many, such as the above, that do not.

I feel there must be something that I have missed but I do not know what.
Can anyone help?

 

[WarwickW]

I am disappointed at the (lack of response). Is this a very difficult problem?
Surely there is someone out there who has done something similar.

 

[HotRhodium]

So I believe the reason no one was answering you was not to be rude but because this is kinda a complicated issue. You are trying to use Excel to do Word operations. This is possible but not easy and we usually lose someone before we can walk them through this minefield. The problem with your code is that this line

Selection.InlineShapes.AddPicture Filename:=sOutputFilePath & sColourFileName, LinkToFile:=False, SaveWithDocument:=True

is working on Excel's selection not on Word's selection. you can point it in the correct direction like this (I do not know what the rest of your code looks like I just wanted to get it running on my machine so I made an example. Notice Dim objWord As Word.Application and Dim oFrontCoverDocument As Document.)

Public Sub WordDemo()
Dim objWord As Word.Application
Dim oFrontCoverDocument As Document
Dim MyRange As Range
 Set objWord = CreateObject(class:="Word.Application")
 objWord.Visible = True                                  '   make the MS Word visible
 objWord.Activate
 Set oFrontCoverDocument = objWord.Documents.Add
 oFrontCoverDocument.Tables.Add Range:=objWord.Selection.Range, NumRows:=5, NumColumns:=5, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
 oFrontCoverDocument.Tables(1).Cell(2, 2).Select
 objWord.Selection.InlineShapes.AddPicture Filename:="C:\Users\Public\Pictures\Sample Pictures\Desert.jpg", LinkToFile:=False, SaveWithDocument:=True
End Sub

Now for the bad news you must enable the

Microsoft Word 14.0 Object Library

in Excel's VBA editor in order to work with Word through Excel's VBA at this level. Please let me know how this goes it took me a bit to put together and I would also be disappointed if I never heard if you were able to get it to work and by extension a little reluctant to look so far into a compatibility issue for the next knowledge seeker. This macro will make a Word Document put a table in it and put a picture (from windows sample pictures) in the table on my machine with the said Library turned on. Hope that helps and good luck

 

[WarwickW]

Thanks for your reply. No I did not think people were rude - just too busy to tackle a complex problem.
I have the word 14.0 reference enabled.
I had objWord defined somewhat similarly. I first used GetObject and if it got an error 429 I used CreateObject.That seemed to work OK.
I have all if the word documents defined as Public word documents similar to your definition.
However, I dont think I qualified the select as a Word selection so I think that might have been my problem.

In the meantime, as I had an urgent task I approached the problem somewhat differently: I wrote all of the calculated working data out to 7 temporary documents then opened a macro-enabled document to which I passed parameters indicating options entered at the beginning of the excel process via a table in the macro-enabled word document.I then closed the excel program and clicked on a macrobutton in the word document to complete the job. In the process I found I could open the macro-enabled word document from Excel but was unable to generate the document using a template.
the open of the macro-enabled word document
Set oPrepareBrochureDocument = objWord.Documents.Open(sOutputFilePath & "PrepareBrochure" & ".docm")

worked like that but failed if I used an explicitly-addressed template to create it.

Anyway, I have succeeded in completing the task which produces a very complex course brochure from data extracted from a database, even if it took close to 2000 lines of code.

I will now try doing what I tried to do before when I get time, using the Word object to explicitly select the word document.

Thanks very much for your help. That is something I have learnt in addition to the many things I have learnt in the last few weeks about Excel and Word VBA. I had written off doing everything in Excel and won't redo that job but will now try again when a new project arised with a need to do very intricate word manipulation.

Thanks again, HotRhodium. You really must be white gold!

 

[HotRhodium]

Good deal. The Public part is just a bad habit I have gotten into because my current project has a large number of interdependent modules and should not be necessary for your code.

Your method actually sounds like a very smart solution and while we are talking about Public functions you should be able to call the Word macro from an Excel macro (providing the Word sub is public) removing the need for the button and further automating this process for you. Then save it without the macros enabled and no one would be the wiser.

Thank you for your feed back and please keep in mind that having a bunch of libraries enabled can cause conflicts. If your macro in the Word document works well enough you may also want to return you excel libraries back to normal. If it doesn't cause you problems then good just keep it in mind if you find something broken that worked yesterday etc. Good luck with your project.