Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
I don't really understand what you mean by:
I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. It just doesn't make sense. You are not saying what it is that you want done if the condition is met. Aside from that, the only way to do what you want will almost certainly be to use a macro that is triggered by the Save or Print commands by having macros with the same name as those commands. You could use the InputBox() command to have the user enter a value, but it might be more appropriate to throw up a userform that contains a combobox or listbox that gets populated with the values of ID2 so that the user only selects form the values that exist. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#3
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi John etc.,
I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. I had the impression that it was using Office X, but unfortunately I am much more ignorant on Mac issues and can only experiment with Office 2004, i.e. (a) do not know whether it is possible to connect directly using ODBC with Office X and (b) what the appropriate syntax for OpenDataSource (or a { DATABASE } field, if that is what the questioner wants) would be on Mac. On the Windows version this is doable partly because it's possible to specify "" as the Name in the OpenDataSource call when the ODBC DSN is a machine DSN (i.e. in the registry), or use a file DSN. If someone on the Mac side knows the answer to the general ODBC connectivity questions maybe we could get a little bit further. Peter Jamieson John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#4
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
My Bad... The OP did specify Office X...
In which case the following link may help: http://www.microsoft.com/mac/otherpr...pid=usingoffic ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml On 29/4/06 7:38 PM, in article , "Peter Jamieson" wrote: Hi John etc., I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. I had the impression that it was using Office X, but unfortunately I am much more ignorant on Mac issues and can only experiment with Office 2004, i.e. (a) do not know whether it is possible to connect directly using ODBC with Office X and (b) what the appropriate syntax for OpenDataSource (or a { DATABASE } field, if that is what the questioner wants) would be on Mac. On the Windows version this is doable partly because it's possible to specify "" as the Name in the OpenDataSource call when the ODBC DSN is a machine DSN (i.e. in the registry), or use a file DSN. If someone on the Mac side knows the answer to the general ODBC connectivity questions maybe we could get a little bit further. Peter Jamieson John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#5
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Still no obvious way to do it here - it's possible to get the data via Excel
and the Excel VBA Help gives an example of how to do it programmatically, but I haven't been able to get it to work so far (again, on Office 2004). Next opportunity to look is in a couple of days' time, but I'd have thought someone on the Mac side had had a thorough go at this and knows what's feasible. Peter jamieson "John McGhie [MVP - Word and Word Macintosh]" wrote in message ... My Bad... The OP did specify Office X... In which case the following link may help: http://www.microsoft.com/mac/otherpr...pid=usingoffic ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml On 29/4/06 7:38 PM, in article , "Peter Jamieson" wrote: Hi John etc., I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. I had the impression that it was using Office X, but unfortunately I am much more ignorant on Mac issues and can only experiment with Office 2004, i.e. (a) do not know whether it is possible to connect directly using ODBC with Office X and (b) what the appropriate syntax for OpenDataSource (or a { DATABASE } field, if that is what the questioner wants) would be on Mac. On the Windows version this is doable partly because it's possible to specify "" as the Name in the OpenDataSource call when the ODBC DSN is a machine DSN (i.e. in the registry), or use a file DSN. If someone on the Mac side knows the answer to the general ODBC connectivity questions maybe we could get a little bit further. Peter Jamieson John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#6
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
I suggest pinging JE McGimpsey. If anyone knows, he will.
-- Paul Berkowitz MVP MacOffice Entourage FAQ Page: http://www.entourage.mvps.org/faq/index.html AppleScripts for Entourage: http://macscripter.net/scriptbuilders/ Please "Reply To Newsgroup" to reply to this message. Emails will be ignored. PLEASE always state which version of Microsoft Office you are using - **2004**, X or 2001. It's often impossible to answer your questions otherwise. From: Peter Jamieson Newsgroups: microsoft.public.mac.office.word,microsoft.public. word.mailmerge.fields Date: Sat, 29 Apr 2006 17:54:19 +0100 Subject: fields queries and utter disaster Still no obvious way to do it here - it's possible to get the data via Excel and the Excel VBA Help gives an example of how to do it programmatically, but I haven't been able to get it to work so far (again, on Office 2004). Next opportunity to look is in a couple of days' time, but I'd have thought someone on the Mac side had had a thorough go at this and knows what's feasible. Peter jamieson "John McGhie [MVP - Word and Word Macintosh]" wrote in message ... My Bad... The OP did specify Office X... In which case the following link may help: http://www.microsoft.com/mac/otherpr...pid=usingoffic ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml On 29/4/06 7:38 PM, in article , "Peter Jamieson" wrote: Hi John etc., I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. I had the impression that it was using Office X, but unfortunately I am much more ignorant on Mac issues and can only experiment with Office 2004, i.e. (a) do not know whether it is possible to connect directly using ODBC with Office X and (b) what the appropriate syntax for OpenDataSource (or a { DATABASE } field, if that is what the questioner wants) would be on Mac. On the Windows version this is doable partly because it's possible to specify "" as the Name in the OpenDataSource call when the ODBC DSN is a machine DSN (i.e. in the registry), or use a file DSN. If someone on the Mac side knows the answer to the general ODBC connectivity questions maybe we could get a little bit further. Peter Jamieson John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#7
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Aside from the issues that have been raised by Peter, does anyone understand
what the OP means by: I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Paul Berkowitz" wrote in message ... I suggest pinging JE McGimpsey. If anyone knows, he will. -- Paul Berkowitz MVP MacOffice Entourage FAQ Page: http://www.entourage.mvps.org/faq/index.html AppleScripts for Entourage: http://macscripter.net/scriptbuilders/ Please "Reply To Newsgroup" to reply to this message. Emails will be ignored. PLEASE always state which version of Microsoft Office you are using - **2004**, X or 2001. It's often impossible to answer your questions otherwise. From: Peter Jamieson Newsgroups: microsoft.public.mac.office.word,microsoft.public. word.mailmerge.fields Date: Sat, 29 Apr 2006 17:54:19 +0100 Subject: fields queries and utter disaster Still no obvious way to do it here - it's possible to get the data via Excel and the Excel VBA Help gives an example of how to do it programmatically, but I haven't been able to get it to work so far (again, on Office 2004). Next opportunity to look is in a couple of days' time, but I'd have thought someone on the Mac side had had a thorough go at this and knows what's feasible. Peter jamieson "John McGhie [MVP - Word and Word Macintosh]" wrote in message ... My Bad... The OP did specify Office X... In which case the following link may help: http://www.microsoft.com/mac/otherpr...pid=usingoffic ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml On 29/4/06 7:38 PM, in article , "Peter Jamieson" wrote: Hi John etc., I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. I had the impression that it was using Office X, but unfortunately I am much more ignorant on Mac issues and can only experiment with Office 2004, i.e. (a) do not know whether it is possible to connect directly using ODBC with Office X and (b) what the appropriate syntax for OpenDataSource (or a { DATABASE } field, if that is what the questioner wants) would be on Mac. On the Windows version this is doable partly because it's possible to specify "" as the Name in the OpenDataSource call when the ODBC DSN is a machine DSN (i.e. in the registry), or use a file DSN. If someone on the Mac side knows the answer to the general ODBC connectivity questions maybe we could get a little bit further. Peter Jamieson John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 -- Please reply to the newsgroup to maintain the thread. Please do not me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#8
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi Doug, et al -
It sounds to me like the 2 tables are related using ID1 as the common field (Primary Key of Table1, Foreign Key of Table2). I'm not familiar with MySQL, but in Access & others, it would seem to me that a simple query in the database would provide the record set the user needs, matching records on the basis of the common field. An inner join would return just the matching records & the query should be constructed to return only the 2 required fields of data (ID1 & ID2) for the matching records. It seems far simpler to me to do this in the database & use the *query* as the data source rather than trying to get Word to query the two tables. What isn't clear (to me) is whether the OP wants to do a *merge*, with the user choosing which of the found records to include, or whether this doc is to be generated for one recipient at a time with the user being prompted for an 'ID2' criteria. Does this make sense? HTH |:) Bob Jones [MVP] Office:Mac On 4/30/06 2:23 AM, in article , "Doug Robbins - Word MVP" wrote: Aside from the issues that have been raised by Peter, does anyone understand what the OP means by: I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. |
#9
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Yes Bob, that would be my understanding and I always maintain that data
manipulation is best done in the data source. Looking now in the original newsgroup to which this was posted (microsoft.public.mac.office.word), I see that the OP posted a couple of follow ups to his original post, the most telling of which states: Quote I will settle for an even easier version ... The user is prompted for an input in the form of a number (id2) and then the corresponding id1 will be printed as well as id2 like this id1 - id2 Unquote If it was Access, I would say the form into which the user eterns the id2 should be in the database and the merge should be initiated from there, possibly making use of the information on Albert Kallal's site at: http://www.members.shaw.ca/AlbertKal...rge/index.html -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "CyberTaz" wrote in message .. . Hi Doug, et al - It sounds to me like the 2 tables are related using ID1 as the common field (Primary Key of Table1, Foreign Key of Table2). I'm not familiar with MySQL, but in Access & others, it would seem to me that a simple query in the database would provide the record set the user needs, matching records on the basis of the common field. An inner join would return just the matching records & the query should be constructed to return only the 2 required fields of data (ID1 & ID2) for the matching records. It seems far simpler to me to do this in the database & use the *query* as the data source rather than trying to get Word to query the two tables. What isn't clear (to me) is whether the OP wants to do a *merge*, with the user choosing which of the found records to include, or whether this doc is to be generated for one recipient at a time with the user being prompted for an 'ID2' criteria. Does this make sense? HTH |:) Bob Jones [MVP] Office:Mac On 4/30/06 2:23 AM, in article , "Doug Robbins - Word MVP" wrote: Aside from the issues that have been raised by Peter, does anyone understand what the OP means by: I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. |
#10
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
What's the best way to do that?
Peter Jamieson "Paul Berkowitz" wrote in message ... I suggest pinging JE McGimpsey. If anyone knows, he will. -- Paul Berkowitz MVP MacOffice Entourage FAQ Page: http://www.entourage.mvps.org/faq/index.html AppleScripts for Entourage: http://macscripter.net/scriptbuilders/ Please "Reply To Newsgroup" to reply to this message. Emails will be ignored. PLEASE always state which version of Microsoft Office you are using - **2004**, X or 2001. It's often impossible to answer your questions otherwise. From: Peter Jamieson Newsgroups: microsoft.public.mac.office.word,microsoft.public. word.mailmerge.fields Date: Sat, 29 Apr 2006 17:54:19 +0100 Subject: fields queries and utter disaster Still no obvious way to do it here - it's possible to get the data via Excel and the Excel VBA Help gives an example of how to do it programmatically, but I haven't been able to get it to work so far (again, on Office 2004). Next opportunity to look is in a couple of days' time, but I'd have thought someone on the Mac side had had a thorough go at this and knows what's feasible. Peter jamieson "John McGhie [MVP - Word and Word Macintosh]" wrote in message ... My Bad... The OP did specify Office X... In which case the following link may help: http://www.microsoft.com/mac/otherpr...pid=usingoffic ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml On 29/4/06 7:38 PM, in article , "Peter Jamieson" wrote: Hi John etc., I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. I had the impression that it was using Office X, but unfortunately I am much more ignorant on Mac issues and can only experiment with Office 2004, i.e. (a) do not know whether it is possible to connect directly using ODBC with Office X and (b) what the appropriate syntax for OpenDataSource (or a { DATABASE } field, if that is what the questioner wants) would be on Mac. On the Windows version this is doable partly because it's possible to specify "" as the Name in the OpenDataSource call when the ODBC DSN is a machine DSN (i.e. in the registry), or use a file DSN. If someone on the Mac side knows the answer to the general ODBC connectivity questions maybe we could get a little bit further. Peter Jamieson John McGhie [MVP - Word and Word Macintosh]" wrote in message ... I'm going to spit this off to the group that specialises in this kind of thing, however, my first question is "How did you get a connection to mysql from Microsoft Office in the first place?" The people in the mailmergefields group need to know that you are doing this in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004. This is a specialist area, and I am by no means a specialist. However, I think your answer will be something along the lines of "You need to use the Database toolbar to insert the database into your data source as a Word table." Once you have done that, you can then query the fields in that table. Hope this helps On 28/4/06 8:37 PM, in article , " wrote: Hi I'm trying to solve the following. I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. or to put it in mysql language SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = ? I can access the table, my problem is that I cannot figure out the proper syntax for MSWords fields As far as I can tell I should be able to do this using IF combined with ASK but I've spent the better part of my day not getting any closer att all. I would really appreciate any help. I might add i would like this to be don on saving or printing but only once for each document .. so if its opened again it will not ask .. in other words if the fields are filled in dont ask. But I'll settle for just getting the first part! -- Please reply to the newsgroup to maintain the thread. Please do not me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 -- Please reply to the newsgroup to maintain the thread. Please do not me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#11
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Does this make sense?
Partly - I agree with the analysis about what the user is trying to retrieve, but there are at least a couple of key issues: a. how does the user get any data at all from MySQL into Word on the Mac platform? Constructing a query or view in the DBMS does not solve that problem, although it might make it easier to do it without resorting to VBA or editing a DATABASE field. At the moment the only way I can see is "get the data from MySQL into a format Word can use as a data source, then use that". As far as I can see at the moment, that can be done using e.g. Excel & MS Query, but it isn't clear that it can easily be automated, e.g. there is no equivalent of ADO to use with Word VBA on Mac, I haven't been able to get the appropriate Mac Excel VBA "get data directly from an ODBC data source" sample to work so far, and the MS Query documentation in this version suggests that it isn't even possible to save a .qry/.dqy file b. in this case the user needs to enter a value which will restrict the set of records returned, which means that you have to create a query/view/procedure to which you can pass a parameter, which means you have to do slightly more than just use the query/view as a data source (again. some of this stuff can be done with some types of data source in the Windows version of Word, but you either have to use the old DDE connection type to Access (and that option's not available on Mac) or successfully issue a procedure call with a parameter which is typically not straightforward even on Windows Word. The bottom line is, either it's feasible to get this data directly from Word, or it's probably going to involve a separate manual step. FWIW, I agree with Doug about databses being the place to manipulate data, but it kinda depends on what you mean by a "database". To me, the whole point of a standard such as SQL is that anything can use all its features to retireve data.I don't see the necessity to do queries as a separate step. Just my 2c-worth Peter Jamieson "CyberTaz" wrote in message .. . Hi Doug, et al - It sounds to me like the 2 tables are related using ID1 as the common field (Primary Key of Table1, Foreign Key of Table2). I'm not familiar with MySQL, but in Access & others, it would seem to me that a simple query in the database would provide the record set the user needs, matching records on the basis of the common field. An inner join would return just the matching records & the query should be constructed to return only the 2 required fields of data (ID1 & ID2) for the matching records. It seems far simpler to me to do this in the database & use the *query* as the data source rather than trying to get Word to query the two tables. What isn't clear (to me) is whether the OP wants to do a *merge*, with the user choosing which of the found records to include, or whether this doc is to be generated for one recipient at a time with the user being prompted for an 'ID2' criteria. Does this make sense? HTH |:) Bob Jones [MVP] Office:Mac On 4/30/06 2:23 AM, in article , "Doug Robbins - Word MVP" wrote: Aside from the issues that have been raised by Peter, does anyone understand what the OP means by: I have a data source that contains two tables - table one has an id1 and some other fields and id1 is the primary key, table2 has as its primary key id2 but also contains id1 from table1 for linking. I want my word document to ask for a value for id2. And the select id1 and id2 if and only if table1.id1 equals table2.id1 and table2.id2 contains the value that i was asked for intially. |
#12
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi
Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#13
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
In that case, you are probably using a DATABASE field, in which case you can
reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message oups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#14
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Thanks,
That looks like it might work, or at least from a mysql viewpoint it seems reasonable. However I have just ordered office 2004 for mac so I shall wait untill it arrives in a couple of days and then give it a whirl. I'll post again as soon as I have had a chance to try it out. Once again many thanks for the offered suggestions. The simple fact that one actually gets a response has made me decide to abandon OpenOffice for now and go with "Bill's stuff". Thanks again |
#15
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
I would appreciate it if
a. you could try it on Word.X first. b. you could let us know what your existing database field actually says. Peter Jamieson "consiglieri" wrote in message oups.com... Thanks, That looks like it might work, or at least from a mysql viewpoint it seems reasonable. However I have just ordered office 2004 for mac so I shall wait untill it arrives in a couple of days and then give it a whirl. I'll post again as soon as I have had a chance to try it out. Once again many thanks for the offered suggestions. The simple fact that one actually gets a response has made me decide to abandon OpenOffice for now and go with "Bill's stuff". Thanks again |
#16
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Sure. not a problem. I'll give it a whirl on Word.X
|
#17
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Trying it on Word.X
To clear a few things up. Initially i connected to the mysql database using sql-query in excel after which I pulled int the information to an excel worksheet. I then tried to create fields along the lines I perviously explained. As for the last attempt. My filed contains the following { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } The only problem is I do not know what "connection info" to use. Normally to connect to mysql i would issue mysql -u usernamen -p password Database I tried that but it didnt work. Could you help me out with an example of "connection info". |
#18
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
OK, from your previous messages I had the impression that you might have
managed to get the results of a simple MySQL query directly into Word, in which case it would almost certainly have been possible to modify the query to get what you needed. But that appears not to be the case. Unfortunately, the gist of the replies so far is that no-one here - including me - actually knows whether it is possible to get data directly into Word from an ODBC data source in either Word X or Word 2004, or what other approaches might work if a direct connection is not possible. The one thing I am sure of is that it is possible to get data manually from an ODBC data source into Excel 2004 using MS Query (as you have done in Excel X). Whether that can be automated in a useful way is another question. Peter Jamieson "consiglieri" wrote in message oups.com... Trying it on Word.X To clear a few things up. Initially i connected to the mysql database using sql-query in excel after which I pulled int the information to an excel worksheet. I then tried to create fields along the lines I perviously explained. As for the last attempt. My filed contains the following { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } The only problem is I do not know what "connection info" to use. Normally to connect to mysql i would issue mysql -u usernamen -p password Database I tried that but it didnt work. Could you help me out with an example of "connection info". |
#19
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
OK,
Yes looking back on my posts I realize I my answers may have been somewhat befuddled. But I have not managed to get a direct mysql query into word, only via excel. Guess I will keep trying to find a reasonable solution. |
#20
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
As a workaround in Office 2004, the following general approach can be used
to get data via Excel. However, I am very unfamiliar with VBA on the Mac platform and the Excel objects this macro needs, so if you want to pursue this, you may need more expert help than I can give. In particular, I originally tried to modify the QueryTable connection and sql from this macro, but however I tried to do it, Word crashed, so I opted for the simpler approach below. Also, on Mac I don't know of a way to prevent Word from prompting for the Worksheet/range details whenever it tries to update the DATABASE field. 1. Create a new workbook (let's call it "Macintosh HD:Users:meocuments:query.xls") 2. Use the data menu to insert the results of a query in Sheet1 at cell A1. You can delete Sheets2 and 3 if you like. 3. Save and close the workbook. 4. In Word, use the database toolbar insertion feature to insert the .xls as a DATABASE field 5. In Word's VBA editor, use Tools|References to make a reference to the Excel object. Create a sub as follows: Sub GetODBCDataViaExcel() Dim objExcel As Excel.Application Dim objWorkBook As Excel.Workbook Dim strWorkbookName as String strWorkbookName = "Macintosh HD:Users:meocuments:query.xls" ' open the spreadsheet, refresh the table, and save the sheet Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open(strWorkbookName) objWorkbook.Worksheets(1).QueryTables(1).Refresh objWorkbook.Close savechanges:=True set objWorkbook = Nothing objExcel.Quit Set objExcel = Nothing ' Update all the fields in the body of the active Word document ActiveDocument.COntent.Fields.Update End Sub It may also be worth looking at the following in Office.X: Assuming you went through MS Query to set up the query, did your version of MS Query allow you to save the query definition before returning the data to Excel, or does it allow you to open a query definition file? On the Windows version, (a) this is feasible and MS Query lets you save a .dqy or .qry file which can be used by Word and (b), MS Query can be run as a standalone program, but on the Office 2004 version of MS Query, it only appears to be possible to start Query from Excel, and there is no facility to save/open separate query definition files. I have tried creating one by hand just in case the facility has been retained elsewhere but that doesn't work either. Peter Jamieson "consiglieri" wrote in message oups.com... OK, Yes looking back on my posts I realize I my answers may have been somewhat befuddled. But I have not managed to get a direct mysql query into word, only via excel. Guess I will keep trying to find a reasonable solution. |
#21
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi
My version of MS Query did not allow me to save my query. It seems I can only execute a query via excel. I can actually start MS Query as a standalone but it doesnt let me do anything. It can neither open a file or create a query. I find it a bit odd that neither Office.X nor Office 2004 has a simple method of accessing a mysql database. It would appear that OpenOffice is ahead of Microsoft in this respect. I find this to be a sad state of affairs. However, I shall not give up. I will see if I can get your suggestion to work. Once again many thanks for your effort. It is appreciated very much! |
#22
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
I find it a bit odd that neither Office.X nor Office 2004 has a simple
method of accessing a mysql database. I think it's fair to say that "Office" does, in the sense that Excel does. I've never had the impression that Microsoft has regarded data connectivity as a particularly important feature in Word, although they have occasionally tried to make it easier to use, and it may be that demand for such features on the Mac is lower than it is on the Windows version of Office (i.e. Mac users probably do that sort of stuff some other way - I'm a realtive newcomer to Mac so really don't know). Peter Jamieson "consiglieri" wrote in message oups.com... Hi My version of MS Query did not allow me to save my query. It seems I can only execute a query via excel. I can actually start MS Query as a standalone but it doesnt let me do anything. It can neither open a file or create a query. I find it a bit odd that neither Office.X nor Office 2004 has a simple method of accessing a mysql database. It would appear that OpenOffice is ahead of Microsoft in this respect. I find this to be a sad state of affairs. However, I shall not give up. I will see if I can get your suggestion to work. Once again many thanks for your effort. It is appreciated very much! |
#23
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi Peter et al,
The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message oups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#24
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi Jim,
A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. I would be interested to know what these were if you happened to keep a note. The only time I was able to get any such thing was when Word determined that the source was a text file and started asking for delimiters, and that was only when I tried to replicate the Windows approach of using a .dsn file. However, since the questioner was originally asking about Word.X perhaps you have found stuff that will work on that version. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. Indeed, it's the kind of thing where direct feedback from the developers is really needed. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. Yes, I posted some code for this that seems to work, but if you happen to have code that lets you modify the connection string/query string of an existing Excel QueryTable using automation from Word, please let me know. Every time I tried this, Word crashed. However, I can't say I've exhausted all the possibilities in this area. The other thing I noticed was that the documentation in Office 2004 suggests that it should be possible to use query files (cf. .qry/.dqy files on Windows), even though MS Query cannot currently do so, using FINDER; as the file type in the necessary connection string. So I tried one I'd created on Windows but no luck. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter et al, The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message oups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#25
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi all
I've been looking into using perl and the win32:le module. From my tests so far this seems to be a way to achieve mysql connectivity. Let the perl script connect to the mysql server, gather the info you want in the word documents, then open the word document with win32:le with the necessary info inputed. I haven't had 100% success, but so far I can initiate a word or excel document, input info. Ofcourse using perl and applescript should be a doable. Since I know more perl then applescript I havent tried applescript yet. I will certainly let the mac business unit know what I think word should be able to do with resepct to mysql connectivity. Thomas PS/ Should i continue to post efforts to try an connect even if they include som perl or applescript specifics? |
#26
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
In article .com,
"consiglieri" wrote: Ofcourse using perl and applescript should be a doable. Since I know more perl then applescript I havent tried applescript yet. One could do this from within VBA: Selection.TypeText MacScript("do shell script ""perl ~/hello.cgi""") |
#27
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Aha..didnt know VBA could execute perl scripts.Thats good news I think.
Can you recommend a good tutorial for VBA on the net ...where they have some kind of example that helps you get going? |
#28
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
|
#29
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi Peter,
After experimenting a bit more I discovered that the DATABASE field code does actually work on Mac Office. I tried it in Office 2001 and Office 2004. The following field code when refreshed will return the requested data: { DATABASE \d "MyDrive:MySource.xls \s "SELECT MyColumn from Sheet1" } This means that two of our major questions have been answered. The DATABASE field code works and SQL in Word works! It seems the only remaining piece of the puzzle to solve is what the syntax is for the \c field switch. I tried several likely things, but was unable to find a syntax that works. Someone at Microsoft is going to see if they can get us a sample or a determination for sure about the \c field switch. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: Hi Jim, A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. I would be interested to know what these were if you happened to keep a note. The only time I was able to get any such thing was when Word determined that the source was a text file and started asking for delimiters, and that was only when I tried to replicate the Windows approach of using a .dsn file. However, since the questioner was originally asking about Word.X perhaps you have found stuff that will work on that version. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. Indeed, it's the kind of thing where direct feedback from the developers is really needed. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. Yes, I posted some code for this that seems to work, but if you happen to have code that lets you modify the connection string/query string of an existing Excel QueryTable using automation from Word, please let me know. Every time I tried this, Word crashed. However, I can't say I've exhausted all the possibilities in this area. The other thing I noticed was that the documentation in Office 2004 suggests that it should be possible to use query files (cf. .qry/.dqy files on Windows), even though MS Query cannot currently do so, using FINDER; as the file type in the necessary connection string. So I tried one I'd created on Windows but no luck. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter et al, The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message egroups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#30
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi Jim,
Someone at Microsoft is going to see if they can get us a sample or a determination for sure about the \c field switch. Good. Even in Word 2003 where ODBC connections are definitely feasible, the basic problem is that you have to supply a data file (\d) parameter in the { DATABASE } field, specifying a file in the Windows file system, otherwise you get the Select Data Source dialog. Since server-type databases such as MySQL generally want you to connect to a "server" rather than a "file", it's not obvious what you specify in the \d parameter to make it all work. On the Windows version, in the similar OpenDataSource method you can get around this by specifying the data file as "", but to make that work in recent versions of Word you have to add a special Subtype parameter that makes it work like Word 2000. In the DATABASE field, \d "" just doesn't work. Again on the Windows version, it's possible to work around this by using the name of an ODBC file DSN in the \d parameter, and specifying \c "FILEDSN=the_file_DSN_pathname" I do not know whether the equivalent would work on Mac because a. the ODBC administrator I'm using (iODBC) doesn't have a facility to create a file DSN. Perhaps there is other ODBC administrator software that can do it. b. in Windows you can create one using the ODBC administrator or by hand in Notepad. It's pretty clear what needs to go in the .dsn. On Mac, I tried this but could not tell whether it did not work because... - file DSNs are not supported (and perhaps even unheard of) - I put the wrong information in - of some additional Mac constraint, e.g. the file has to have a certain type. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter, After experimenting a bit more I discovered that the DATABASE field code does actually work on Mac Office. I tried it in Office 2001 and Office 2004. The following field code when refreshed will return the requested data: { DATABASE \d "MyDrive:MySource.xls \s "SELECT MyColumn from Sheet1" } This means that two of our major questions have been answered. The DATABASE field code works and SQL in Word works! It seems the only remaining piece of the puzzle to solve is what the syntax is for the \c field switch. I tried several likely things, but was unable to find a syntax that works. Someone at Microsoft is going to see if they can get us a sample or a determination for sure about the \c field switch. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: Hi Jim, A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. I would be interested to know what these were if you happened to keep a note. The only time I was able to get any such thing was when Word determined that the source was a text file and started asking for delimiters, and that was only when I tried to replicate the Windows approach of using a .dsn file. However, since the questioner was originally asking about Word.X perhaps you have found stuff that will work on that version. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. Indeed, it's the kind of thing where direct feedback from the developers is really needed. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. Yes, I posted some code for this that seems to work, but if you happen to have code that lets you modify the connection string/query string of an existing Excel QueryTable using automation from Word, please let me know. Every time I tried this, Word crashed. However, I can't say I've exhausted all the possibilities in this area. The other thing I noticed was that the documentation in Office 2004 suggests that it should be possible to use query files (cf. .qry/.dqy files on Windows), even though MS Query cannot currently do so, using FINDER; as the file type in the necessary connection string. So I tried one I'd created on Windows but no luck. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter et al, The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message legroups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#31
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi
On Windows Word97 I can query and get results frpm mysql by using the DSN name for my connection \c DSN=connectionname and then issuing a query \s my query. The only problem is returning more than one field its inserted into a tabel and I have not been able to set a format for that table so it spreads out on the whole page which is not what I want. On office 2004 I can do the same by using \d and putting in the path for my xls file .. allthough it took me a while to figure out the format for the path. The query on mac must contain the path i.e \s "SELECT something FROM Macintosh HD:Users:folder:file.xls" This also results in a table ... but how to set a format? |
#32
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
You can't set the table format before you created it. You have to go back
afterwards and format the table. The following VBA code formats a table for you. This is very ³robust² code that is in service at some of my customer sites, so it is built to be unbreakable. You can simplify it a lot for what you want. For example, if you ensure that your Normal template contains Table Heading and Table Body styles, you can remove half the code :-) Sub Main() ' ' Insert or Format Table Macro ' Macro recorded 14/03/00 by John McGhie ' ' On Error GoTo Error Dim astyle As Style Dim aDoc As String Dim aTemplate As String Dim x As Integer Dim TabBodyText As Style Dim TabHeading As Style Dim TabBullet As Style Dim tabNumber As Style aTemplate = ActiveDocument.AttachedTemplate.FullName aDoc = ActiveDocument.FullName For Each astyle In ActiveDocument.Styles If UCase(astyle.NameLocal) = UCase("Table Body Text") Then Set TabBodyText = astyle If UCase(astyle.NameLocal) = UCase("Table Bullet") Then Set TabBullet = astyle If UCase(astyle.NameLocal) = UCase("Table Heading") Then Set TabHeading = astyle If UCase(astyle.NameLocal) = UCase("Table Number") Then Set tabNumber = astyle Next astyle If TabBodyText Is Nothing Then ActiveDocument.Styles.Add Name:="Table Body Text" Set TabBodyText = ActiveDocument.Styles("Table Body Text") With TabBodyText .AutomaticallyUpdate = False .BaseStyle = "Body Text" .NextParagraphStyle = "Table Body Text" End With With TabBodyText.Font .Name = Arial .Size = 10 .Bold = False .Italic = False End With With TabBodyText.ParagraphFormat .LeftIndent = 0 .RightIndent = 0 .SpaceBefore = 2 .SpaceBeforeAuto = False .SpaceAfter = 2 .SpaceAfterAuto = False .LineSpacingRule = wdLineSpaceSingle .Alignment = wdAlignParagraphLeft .WidowControl = False .KeepWithNext = False .KeepTogether = True .PageBreakBefore = False .FirstLineIndent = 0 .OutlineLevel = wdOutlineLevelBodyText End With End If If TabHeading Is Nothing Then ActiveDocument.Styles.Add Name:="Table Heading" Set TabHeading = ActiveDocument.Styles("Table Heading") With TabHeading .AutomaticallyUpdate = False .BaseStyle = "Table Body Text" .NextParagraphStyle = "Table Heading" End With With TabHeading.Font .Name = Arial .Size = 10 .Bold = True .Italic = False End With With TabHeading.ParagraphFormat .LeftIndent = 0 .RightIndent = 0 .SpaceBefore = 2 .SpaceBeforeAuto = False .SpaceAfter = 2 .SpaceAfterAuto = False .LineSpacingRule = wdLineSpaceSingle .Alignment = wdAlignParagraphLeft .WidowControl = False .KeepWithNext = True .KeepTogether = True .PageBreakBefore = False .FirstLineIndent = 0 .OutlineLevel = wdOutlineLevelBodyText End With End If If TabBullet Is Nothing Then ActiveDocument.Styles.Add Name:="Table Bullet" Set TabBullet = ActiveDocument.Styles("Table Bullet") With TabBullet .AutomaticallyUpdate = False .BaseStyle = "Table Body Text" .NextParagraphStyle = "Table Bullet" End With With TabBullet.Font .Name = Arial .Size = 10 .Bold = False .Italic = False End With With TabBullet.ParagraphFormat .LeftIndent = 22 .RightIndent = 0 .SpaceBefore = 0 .SpaceBeforeAuto = False .SpaceAfter = 5 .SpaceAfterAuto = False .LineSpacingRule = wdLineSpaceSingle .Alignment = wdAlignParagraphLeft .WidowControl = True .KeepWithNext = False .KeepTogether = False .PageBreakBefore = False .FirstLineIndent = -17 .OutlineLevel = wdOutlineLevelBodyText End With TabBullet.ParagraphFormat.TabStops.ClearAll TabBullet.ParagraphFormat.TabStops.Add _ Position:=22, Alignment:=wdAlignTabLeft, Leader:= _ wdTabLeaderSpaces With ListGalleries(wdBulletGallery).ListTemplates(7).Li stLevels(1) .NumberFormat = ChrW(61623) .TrailingCharacter = wdTrailingTab .NumberStyle = wdListNumberStyleBullet .NumberPosition = 6 .Alignment = wdListLevelAlignLeft .TextPosition = 22 .TabPosition = 22 .ResetOnHigher = 0 .StartAt = 1 .Font.Name = "Symbol" .LinkedStyle = "Table Bullet" End With End If If tabNumber Is Nothing Then ActiveDocument.Styles.Add Name:="Table Number" Set tabNumber = ActiveDocument.Styles("Table Number") With tabNumber .AutomaticallyUpdate = False .BaseStyle = "Table Bullet" .NextParagraphStyle = "Table Number" End With With tabNumber.Font .Name = Arial .Size = 10 .Bold = False .Italic = False End With With tabNumber.ParagraphFormat .LeftIndent = 22 .RightIndent = 0 .SpaceBefore = 0 .SpaceBeforeAuto = False .SpaceAfter = 2 .SpaceAfterAuto = False .LineSpacingRule = wdLineSpaceSingle .Alignment = wdAlignParagraphLeft .WidowControl = True .KeepWithNext = False .KeepTogether = False .PageBreakBefore = False .FirstLineIndent = -17 .OutlineLevel = wdOutlineLevelBodyText End With tabNumber.ParagraphFormat.TabStops.ClearAll tabNumber.ParagraphFormat.TabStops.Add _ Position:=22, Alignment:=wdAlignTabLeft, Leader:= _ wdTabLeaderSpaces With ListGalleries(wdNumberGallery).ListTemplates(7).Li stLevels(1) .NumberFormat = "%1." .TrailingCharacter = wdTrailingTab .NumberStyle = wdListNumberStyleArabic .NumberPosition = 6 .Alignment = wdListLevelAlignLeft .TextPosition = 22 .TabPosition = 22 .ResetOnHigher = 0 .StartAt = 1 .LinkedStyle = "Table Number" End With End If If Not Selection.Information(wdWithInTable) Then ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5, NumColumns:= _ 3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _ wdAutoFitFixed End If Dim aTable As Table Set aTable = Selection.Tables(1) aTable.Select aTable.AutoFormat Format:=wdTableFormatGrid5, ApplyBorders:= _ True, ApplyShading:=True, ApplyFont:=False, ApplyColor:=False, _ ApplyHeadingRows:=True, ApplyLastRow:=False, ApplyFirstColumn:=False, _ ApplyLastColumn:=False, AutoFit:=True aTable.Select With Selection .Rows.AllowBreakAcrossPages = False .Paragraphs.Reset .Font.Reset .Style = ActiveDocument.Styles("Table Body Text") With .Borders(wdBorderLeft) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth075pt .Color = wdColorAutomatic End With With .Borders(wdBorderRight) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth075pt .Color = wdColorAutomatic End With With .Borders(wdBorderTop) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth075pt .Color = wdColorAutomatic End With With .Borders(wdBorderBottom) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth075pt .Color = wdColorAutomatic End With With .Borders(wdBorderHorizontal) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth025pt .Color = wdColorAutomatic End With With .Borders(wdBorderVertical) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth025pt .Color = wdColorAutomatic End With .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone .Borders.Shadow = False End With With Options .DefaultBorderLineStyle = wdLineStyleSingle .DefaultBorderLineWidth = wdLineWidth025pt .DefaultBorderColor = wdColorAutomatic End With aTable.Rows(1).Select With Selection .Style = ActiveDocument.Styles("Table Heading") .Rows(1).HeadingFormat = True With .Shading .Texture = wdTexture10Percent .ForegroundPatternColor = wdColorAutomatic .BackgroundPatternColor = wdColorAutomatic End With End With aTable.AutoFitBehavior (wdAutoFitWindow) End Error: MsgBox "The Template has been damaged. See Tech Support.", vbCritical End Sub On 9/5/06 6:43 PM, in article , "consiglieri" wrote: Hi On Windows Word97 I can query and get results frpm mysql by using the DSN name for my connection \c DSN=connectionname and then issuing a query \s my query. The only problem is returning more than one field its inserted into a tabel and I have not been able to set a format for that table so it spreads out on the whole page which is not what I want. On office 2004 I can do the same by using \d and putting in the path for my xls file .. allthough it took me a while to figure out the format for the path. The query on mac must contain the path i.e \s "SELECT something FROM Macintosh HD:Users:folder:file.xls" This also results in a table ... but how to set a format? -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#33
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
WoW!
Thanks.. I will give it a test. |
#34
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
John
Could I possibly trouble you to send a file with that script to ? I tried copying it but I have compilation errors and I thought perhaps they where a result of some formatting issues when doing copy and paste? Or perhaps you allready know thats not the issue..if so please let me know. Once again, many thanks |
#35
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Did you paste directly out of the browser? That will do it... The browser
substitutes non-breaking spaces for normal spaces. These are significant characters to VBA and they will give you an error. To fix it, paste into TextEdit, convert it to plain text, then copy to Word from there. This is my copy, which will give compile errors because it calls external constants. Change BodyFont to the string name of the font you want to use for table body, and HeadingFont to the one for headings. Cheers On 9/5/06 8:46 PM, in article , "consiglieri" wrote: John Could I possibly trouble you to send a file with that script to ? I tried copying it but I have compilation errors and I thought perhaps they where a result of some formatting issues when doing copy and paste? Or perhaps you allready know thats not the issue..if so please let me know. Once again, many thanks -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |
#36
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Hi
I keep getting DefaultTableBehavior:=wdWord9TableBehavior The named argument does not exist Any chance you could explain that to me? I am a complete newbie to this VBA stuff |
#37
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
|
|||
|
|||
fields queries and utter disaster
Oh, sorry about that :-)
That's code from the PC version of the macro. Remove the whole property/value pair "DefaultTableBehavior:=wdWord9TableBehavior" Just delete it, and Word will revert to its default (auto-size columns) instead of the previous fixed-size columns. Hopefully it won't make a difference for your application. Cheers On 10/5/06 7:21 PM, in article , "consiglieri" wrote: Hi I keep getting DefaultTableBehavior:=wdWord9TableBehavior The named argument does not exist Any chance you could explain that to me? I am a complete newbie to this VBA stuff -- Please reply to the newsgroup to maintain the thread. Please do not email me unless I ask you to. John McGhie Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer Sydney, Australia +61 (0) 4 1209 1410 |