locked
How to manipulate Word userform from Excel VBA? (Office 2010) RRS feed

  • Question

  • Hello,

    Seems I'm trying some black magic and searching for it is forbidden ;)

    The situation is:

    1 Office 2010.

    2. I am provided with a Word template "template.dot". This template, when opened, runs a userform "Form1", clicking on OK runs another userform "Form2", where I can input some data, click OK and the data is populated to multiple form fields in a new document.

    This template tends to change, so I don't want to tamper with the code in it - I would have to do so every time I get new version of template.

    3. The data I need to input in Form2 is already in an Excel file, let's call it "exceldata.xls".

    I need a way to copy the data from "exceldata.xls" (one row) into Form2, using Excel VBA.

    I already know how to open "template.dot" from Excel, but then I'm stuck. The templeta loads, creates new document "doc1.doc" and does not load userforms (i.e. the "Document_New" macro is not run).

    Seems that I have to load userform Form1 somehow, allow the user to check the checkboxes on it, wait for loading of Form2 then push the Excel data into checkboxes in Form2 and eventually let the user click OK to populate the data into form fields.

    I don't want to push the data directly into form fields in the document, as this is what is chaned between versions of "template.dot", while the userforms are relatively stable.

    Is this all possible?

    If so, how do I force loading the userform in Word template from Excel VBA and how can I refer to textboxes in this userform to fill them with data?

    Saturday, November 10, 2012 3:17 AM

All replies

  • You will need to call the form specifically from the template and you will probably need to pull the data into the form from Word rather than push it from Excel e.g. as follows. Wouldn't it be simpler to run the template directly from Word rather than from Excel?

    Sub OpenWord()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim bStarted As Boolean
    Dim strMyTemplate As String
    strMyTemplate = "D:\Word 2010 Templates\Template.dot"

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
        bStarted = True
    End If
    Set wdDoc = wdApp.Documents.Add(strMyTemplate)

    wdApp.Visible = True
    wdApp.Activate
    ' do whatever else you want in Word here -e.g. wdApp.Run YourWordMacro etc.
    wdApp.Run ("ShowMyForm")
    ' and when you're finished
    If bStarted = True Then
        wdApp.Quit
    End If
    Set wdDoc = Nothing
    Set wdApp = Nothing
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    Saturday, November 10, 2012 7:41 AM