MS Office Word 2007/2010 - how to view edit an existing built in mail merge SELECT?

Question

I've few word documents with Mail merge in them, I have to change the data source, but to do so I need to extract all the SELECT queries from these documents in order to detect the tables and fields being used. (I don't have access to the old datasource as it doesn't exist anymore).

I've been searching the whole day, and can't figure out how to extract or show the SELECT query applied to each document. When I run one document a dialog box pops open saying Word is going to run the following query SELECT X,Y.... but as the queries are quite big I can't see their whole structure, I can't resize the dialog box neither...

Is there a way to extract these selects?

Solution

If you open the word .docx file with an unzip utility program like 7-Zip this will reveal the folder/file structure within.

The root folders should be something like docProps, Word, _Rel. Look in the Word folder and open the "settings.xml" file. within this you should see the query (together with the database column mappings to fields within the document).

I tried this in Word 2007 mapping to a customers table and the query saved was:

query w:val="SELECT * FROM "Customer" "

Edit: extract from settings.xml (I had to strip out the < and > to get this to show up)

w:settings xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:sl="http://schemas.openxmlformats.org/schemaLibrary/2006/main" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:w10="urn:schemas-microsoft-com:office:word"
w:zoom w:percent="100"/
w:mailMerge
w:mainDocumentType w:val="formLetters"/
w:linkToQuery/
w:dataType w:val="native"/
w:connectString w:val="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Chinook;Data Source=NEWDELL\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NEWDELL;Use Encryption for Data=False;Tag with column collation when possible=False"/
w:query w:val="SELECT * FROM "Customer" "/
w:dataSource r:id="rId1"/
w:viewMergedData/
w:odso ....etc

Comments

[CoolStraw]

There's absolutely no single code in the VBA code editor. And if I write a Macro, I don't even know where I should look up these queries... They've been introduced using mail-merge but even in the mailing ruban I don't find anything related to them. Thx

[CoolStraw]

Oh wait I didn't work.. I converted my doc files to docx and there's no query w:val attribute, I've even checked ALL xml files I found in there... Strange

[CoolStraw]

I've just tried converting with compatiblity mode on, but the query is still not in the XML files.. I really don't get it.. where's it then?

[Andrew]

Oh sorry to hear that - the convertion from doc to docx may be an added wrinkle. I did a new mail merge in 2007 and saved it as a .doc file. Then reopened this and save as .docx with compatability mode and could still see the same in settings.xml -edit answer to include extract...

[Andrew]

I should add that I have not an old version of Word to hand.. so maybe my test workflow above will not duplicate the issue.

[CoolStraw]

This is weird... I did exactly the same workflow... The damn query isn't stored at all, I did a CTRL+F search and afterward I've read the whole settings.xml file... no SELECT, no data source... nothing at all.. I don't get it. And I don't understand how come they didn't think about adding a way to show the way mailmerge gets its data... sometimes big software has such stupid flaws...

[CoolStraw]

ah wait, you saved as docx through 2007? Because I did this through 2010... maybe this is the different, I'll check this by tomorrow and come back to you. Thank you Andrew :)