1
votes

This is what I am trying to do:

  • I have an Excel sheet where users can choose some keywords.
  • For each keyword, there is an individual Word document with the same name that contains some texts regarding the keyword.
  • After making their choices, I would like the users to click on a Command Button which will then create a new Word document.
  • This new Word document will contain the chosen keywords and texts from the correspondent Word document. Style and formating of the resulting Word document is not important. A blank line separating different text from different document will be good enough.
  • I would like to do this with Late Binding as I have trouble adding reference in VBA (Error Accessing the System Registry)

How should I start and where to look for examples? I have intermediate Excel VBA experience but completely unfamiliar with cross-application and Word properties.

1
with intermediate Excel VBA experience you can write 50% of the code on your own. Do it and come back here with the problems you will encounter. As a good start for Excel-Word interop write the following in search engine here in SO: [excel-vba] [word-vba] and press enter. Half of the results will be a good points to start.Kazimierz Jawor

1 Answers

1
votes

As KazJaw said above, as an intermediate VBA user you should be able to create the userform and related code that allows your users to select the Word document as you describe. Once you get to working with the Word document things get a bit different from coding for Excel.

Let me share the little I know about this:

First, make sure you've activated the Word Object Library: on the Tools menu, click References. In the list of available references, find and select the appropriate Microsoft Word Object Library

As I understand it, late binding just means declaring the object type when you assign the value. I have no idea if this will solve your 'Error Accessing the System Registry' issue. I have used late binding when I call Word Documents by first defining a variable as a generic object:

Dim wdApp As Object
Dim wd As Object

Then defining the object(s) I created:

On Error Resume Next

    Set wdApp = GetObject(, "Word.Application") 'establishing the word application

    If Err.Number <> 0 Then
         Set wdApp = CreateObject("Word.Application")
    End If

On Error GoTo 0

    Set wd = wdApp.Documents.Open("C:\YourFilePath") 'establishing a file to use

Once you've done that, you can start manipulating Word with the commands available to you, all of which you should be able to find elsewhere on the web, or using the compiler's hints (start by entering Word.Application.ActiveDocument. for example and you will see a list of functions available for manipulating that document). Here are a few, with which I used a previously defined variable wd to refer to a specific document:

                wd.Activate 'activate the word doc
                wd.PrintOut 'printout the word doc
                wd.FormFields("BundleNumber1").Result = sBundleNumber 'fill in a pre-established form field with data stored in the variable 'sBundleNumber'
                wd.Close 'close the word doc

If you are selecting the entire content of the document, I think that should be fairly strait forward (something like Word.Application.ActiveDocument.SelectAllEditableRanges, but if you have to select a sub-section of the document you should know that ranges can be defined in Word in much the same way as they are defined in Excel, but the edges are not as neat as the cells in Excel. I believe they are defined by paragraphs and breaks, but you will have to research how this is done: I've never done it.

Hope this will be of help to you creating a code that can then be wrangled (if necessary) by the community.