Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting access to Word mail merge data source from VBA

Status
Not open for further replies.

jaol

Programmer
Oct 16, 2003
13
DK
Hi all,

In our company mail merging in Word is done with using Word as data source.

We also an Excel application that needs to extract information from this Word data source, but I cannot figure out how to do it from VBA.

If it is not posssible to get access to the Word data source file from Excel VBA, how can I convert the source file to Access?

Every little tiny piece of help is highly appreciated.

Thanks in advance
Janne
 
Hi,

Why does it need to be VBA? Why not a query via Data > Get External Data > Text Files?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Assuming that your mailmerge document is the active document in Word, then the following VBA code in Excel should get you started ...

Code:
[blue]    Dim myWord As Word.Application
    
    Set myWord = GetObject(, "word.application")
    
    Debug.Print myWord.ActiveDocument.MailMerge.DataSource.Name
    Debug.Print myWord.ActiveDocument.MailMerge.DataSource.QueryString[/blue]
 
The Word data source file acts like a database, but is a word document - not a txt-file.

In Excel I would like to select some of the information from one 'row' in the word data source file and process them into XMLfiles used for shipping.
If the data source file was a database - plain SQL and a ADODB-connection could have done it for me, but I cannot figure out how to access the word data source in that manner
 
If the data source is a Word file, you simply open the Word file as you would do for an ordinary Word document and extract your data. Being a data source for a Word mailmerge makes no difference.

Cheers
Paul Edstein
[MS MVP - Word]
 
For any type of mail merge, the data needs to be formatted properly. If the data you want is not the only thing in the Word document, you'll first need to get the data you want for the mail merge copied to another document. Also, if the data is not formatted consistently (e.g., let's say you have addresses in several columns, each address is between 3 to 5 lines long and you might not want all of the addresses), you'll need to do a lot more work.

Basically, without seeing what your data looks like, it's very hard to help you with a macro.
 
I'd hoped my code might point you towards something like:

Code:
[blue]    Dim myWord As Word.Application
    Dim CurrentRecord As Long
    Dim DataRow As MailMergeDataField
    
    Set myWord = GetObject(, "word.application")
    
    With myWord.ActiveDocument.MailMerge.DataSource
        .ActiveRecord = wdFirstDataSourceRecord
        Do
            CurrentRecord = .ActiveRecord
            For Each DataRow In .DataFields
                Debug.Print "Field: " & DataRow.Name, "Value: " & DataRow.Value
            Next
            .ActiveRecord = wdNextRecord
        Loop Until .ActiveRecord = CurrentRecord
    End With[/blue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top