Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Insert Field Database only fails when I try to use filters
Hi, I have a word doc and an excel doc. The excel doc has several worksheets
with data that I would like to include in my word doc. When I do the following: 1) Insert - Field - Database OR "Insert Database" from Database menu 2) Get Data - (use explorer to select excel spreadsheet 3) Select Data - (pick worksheet of interest) - choose OK (1st row contains headers) 4) Insert Data - "All" and select "Insert data as field" This works great. However, if in between steps 3) and 4), I choose a) Query Options - "field" "greater than" "0" (my query is really this simple) and then try to insert the data, I get error "Word was unable to open the data source" then error "Word could not replace the selection with the specified database" It seems like I should be able to do this, does anyone have any ideas why filters would cause this kind of problem? Any ideas on how to get this to work? Using Office Professional 2003 SP2 (11.8026.8036) Thanks, Sherwood |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Insert Field Database only fails when I try to use filters
It doesn't work because when you specify conditions and filters, Word does
not always generate SQL that the OLEDB provider that fetches the data understands. With any luck, you should be able to see the SQL it has generated by using Alt-F9 to look at the code in the DATABASE field. For example, one I have here looks like this: { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` '0'))" \h } In this case, because the OLEDB provider that gets the data /probably/ thinks that "K" is a numeric column, the SQL should be "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` 0))" But unfortunately even that is not quite enough, because the way Word uses the OLEDB provider seems to require that you specify a "table alias" (which is really an alternative name you can use for the table): "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` S1 WHERE ((`K` 0))" (or in this case, "SELECT * FROM `Sheet1$` S1 WHERE K 0" should be enough. You can also cut out a lot of the stuff in the "\c" parameter, e.g. ending up with { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=35;" \s "SELECT * FROM `Sheet1$` S1 WHERE K 0" \h } or even cut out the \c parameter altogether, e.g. { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \s "SELECT * FROM `Sheet1$` S1 WHERE K 0" \h } That's in a fully up-to-date copy of Word 2003. You may find other glitches if you're using earlier versions, and the OLEDB connection method isn't available at all before Word 2002. Peter Jamieson "Sherwood" wrote in message ... Hi, I have a word doc and an excel doc. The excel doc has several worksheets with data that I would like to include in my word doc. When I do the following: 1) Insert - Field - Database OR "Insert Database" from Database menu 2) Get Data - (use explorer to select excel spreadsheet 3) Select Data - (pick worksheet of interest) - choose OK (1st row contains headers) 4) Insert Data - "All" and select "Insert data as field" This works great. However, if in between steps 3) and 4), I choose a) Query Options - "field" "greater than" "0" (my query is really this simple) and then try to insert the data, I get error "Word was unable to open the data source" then error "Word could not replace the selection with the specified database" It seems like I should be able to do this, does anyone have any ideas why filters would cause this kind of problem? Any ideas on how to get this to work? Using Office Professional 2003 SP2 (11.8026.8036) Thanks, Sherwood |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Insert Field Database only fails when I try to use filters
Peter,
Thanks! I've finally had a chance to implement your suggestions and I was able to get it working. Your reply was a huge help. I have some other problems now with the import formatting of the data in my various columns, but I'll try to do my due diligence research first before asking. If that's not fruitful, I'll be back in the newsgroup soon with more data import questions. If you happen to have a link to a useful information source on data formatting while importing, I'd appreciate the link. But regardless, thanks for your help on the import problem. Sherwood "Peter Jamieson" wrote: It doesn't work because when you specify conditions and filters, Word does not always generate SQL that the OLEDB provider that fetches the data understands. With any luck, you should be able to see the SQL it has generated by using Alt-F9 to look at the code in the DATABASE field. For example, one I have here looks like this: { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` '0'))" \h } In this case, because the OLEDB provider that gets the data /probably/ thinks that "K" is a numeric column, the SQL should be "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` 0))" But unfortunately even that is not quite enough, because the way Word uses the OLEDB provider seems to require that you specify a "table alias" (which is really an alternative name you can use for the table): "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` S1 WHERE ((`K` 0))" (or in this case, "SELECT * FROM `Sheet1$` S1 WHERE K 0" should be enough. You can also cut out a lot of the stuff in the "\c" parameter, e.g. ending up with { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=35;" \s "SELECT * FROM `Sheet1$` S1 WHERE K 0" \h } or even cut out the \c parameter altogether, e.g. { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \s "SELECT * FROM `Sheet1$` S1 WHERE K 0" \h } That's in a fully up-to-date copy of Word 2003. You may find other glitches if you're using earlier versions, and the OLEDB connection method isn't available at all before Word 2002. Peter Jamieson "Sherwood" wrote in message ... Hi, I have a word doc and an excel doc. The excel doc has several worksheets with data that I would like to include in my word doc. When I do the following: 1) Insert - Field - Database OR "Insert Database" from Database menu 2) Get Data - (use explorer to select excel spreadsheet 3) Select Data - (pick worksheet of interest) - choose OK (1st row contains headers) 4) Insert Data - "All" and select "Insert data as field" This works great. However, if in between steps 3) and 4), I choose a) Query Options - "field" "greater than" "0" (my query is really this simple) and then try to insert the data, I get error "Word was unable to open the data source" then error "Word could not replace the selection with the specified database" It seems like I should be able to do this, does anyone have any ideas why filters would cause this kind of problem? Any ideas on how to get this to work? Using Office Professional 2003 SP2 (11.8026.8036) Thanks, Sherwood |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Insert Field Database only fails when I try to use filters
Glad to hear you've been able to make progress.
There's a real problem formatting the data in tables returned by DATABASE fields becuase you just don't get to use the formatting switches you get to use in ordinary MERGEFIELD fields. other than the formatting options available in the DATABASE field itself, as far as I know you can't do alignment using tabs or spaces, you can't do decimal alignment etc. You may also find that if you are inserting multiple tables, Word autoformats the column widths to be different when you want them to be the same. IMO the only way to deal with that is to probably to post-process the resulting document using VBA, formatting rows, columns and cells as you want. I don't have sample code - if anyone has posted such stuff recently in this group it's probably Doug Robbins, but that sort of code is the sort of thing you might find in any of the VBA-related newsgroups. Peter Jamieson "Sherwood" wrote in message ... Peter, Thanks! I've finally had a chance to implement your suggestions and I was able to get it working. Your reply was a huge help. I have some other problems now with the import formatting of the data in my various columns, but I'll try to do my due diligence research first before asking. If that's not fruitful, I'll be back in the newsgroup soon with more data import questions. If you happen to have a link to a useful information source on data formatting while importing, I'd appreciate the link. But regardless, thanks for your help on the import problem. Sherwood "Peter Jamieson" wrote: It doesn't work because when you specify conditions and filters, Word does not always generate SQL that the OLEDB provider that fetches the data understands. With any luck, you should be able to see the SQL it has generated by using Alt-F9 to look at the code in the DATABASE field. For example, one I have here looks like this: { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` '0'))" \h } In this case, because the OLEDB provider that gets the data /probably/ thinks that "K" is a numeric column, the SQL should be "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` 0))" But unfortunately even that is not quite enough, because the way Word uses the OLEDB provider seems to require that you specify a "table alias" (which is really an alternative name you can use for the table): "SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` S1 WHERE ((`K` 0))" (or in this case, "SELECT * FROM `Sheet1$` S1 WHERE K 0" should be enough. You can also cut out a lot of the stuff in the "\c" parameter, e.g. ending up with { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=35;" \s "SELECT * FROM `Sheet1$` S1 WHERE K 0" \h } or even cut out the \c parameter altogether, e.g. { DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data Sources\\etest.xls" \s "SELECT * FROM `Sheet1$` S1 WHERE K 0" \h } That's in a fully up-to-date copy of Word 2003. You may find other glitches if you're using earlier versions, and the OLEDB connection method isn't available at all before Word 2002. Peter Jamieson "Sherwood" wrote in message ... Hi, I have a word doc and an excel doc. The excel doc has several worksheets with data that I would like to include in my word doc. When I do the following: 1) Insert - Field - Database OR "Insert Database" from Database menu 2) Get Data - (use explorer to select excel spreadsheet 3) Select Data - (pick worksheet of interest) - choose OK (1st row contains headers) 4) Insert Data - "All" and select "Insert data as field" This works great. However, if in between steps 3) and 4), I choose a) Query Options - "field" "greater than" "0" (my query is really this simple) and then try to insert the data, I get error "Word was unable to open the data source" then error "Word could not replace the selection with the specified database" It seems like I should be able to do this, does anyone have any ideas why filters would cause this kind of problem? Any ideas on how to get this to work? Using Office Professional 2003 SP2 (11.8026.8036) Thanks, Sherwood |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert database problem | Tables | |||
Form Letter Mail Merge using field code: {Database} | Mailmerge | |||
Mail Merge Field window should remain open to insert multiple fiel | Mailmerge | |||
Insert a file into a merge field | Mailmerge | |||
How do I insert a field in a dialog box? | Microsoft Word Help |