Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
I have created an application in Excel that collects data for eventual
merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
I find it hard to imagine a mail merge that needs that many fields.
However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
My application has multiple tabs and generates a 7-9 page report in Word
using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
Are you preparing multiple reports from the data or is it just one report?
-- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
Just one report. Each spreadsheet generates one LONG reord of data based on
user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
In that case, you could have the "field names" in one column and the data
for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
Docvariable fields! That sounds promising. I'll read up on those. Could I
use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
Yes. In fact it must be done with VBA.
-- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
OK, so you think this will also fix my problem with Mac reports also? So let
me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
The following code, written for use with Word 2007 and an Excel 2007 data
source requires: 1. In the Visual Basic Editor - a reference must be set to the Microsoft Office 12.0 Access database engine Object Library If using earlier versions of Office, the reference should be set to the Microsoft DAO 3.6 Object Library and the following line of the code Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") must be replaced with Set db = OpenDatabase(DataSource, False, False, "Excel 8.0; IMEX=1;") I do not know the equivalent of these setup steps with a Mac. If you need assistance with that aspect of it, I would suggest that you post to one of the Microsoft.Public.Mac.Office newsgroups. 2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" When the code runs, it will create a document variable for each record in the datasource with the name of the variable being the item in the first column of the spreadsheet and the value of the variable being the item in the second column of the spreadsheet. To facilitate the insertion of the Docvariable fields in the document, I would create a userform with a listbox that was populated with the field names from the first column of the spreadsheet and a button that when clicked would insert a docvariable field into the documnet for the field that was selected in the listbox. It would in fact be possible to construct a user interface for this that had virtually the same functionality as the mail merge interface. Dim Msg, Style, Title, Response Dim i As Long Dim j As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim varname As String Dim varvalue As String Dim fd As FileDialog Dim DataSource As String Dim avar As Variable For Each avar In ActiveDocument.Variables avar.Value = " " Next avar 'Display the file selection dialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Select Data Source" .InitialFileName = "" If .Show = -1 Then DataSource = .SelectedItems(1) Else Exit Sub End If End With Set fd = Nothing Msg = "Please ensure that Excel is closed before Continuing. Do you want to continue?" Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Refresh Data" ' Define title. ' Display message. Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. ' Open the file containing the Data Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") Set rs = db.OpenRecordset("SELECT * FROM `DataItems`") With rs .MoveFirst For i = 0 To .RecordCount ActiveDocument.Variables(Format(.Fields(0).Value)) = ..Fields(1).Value .MoveNext Next i End With ' Cleanup rs.Close Set rs = Nothing db.Close Set db = Nothing Else Exit Sub End If -- 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 "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
That's outstanding, thanks for the effort. So, I would need a different
version of my report for 2007, 2003 and Mac? I have users who are in each environment. This macro would be run from the Word side? Is that correct? How does the macro know what worksheet to look for the data in? Thanks again, Doug, I really appreciate it. I will also check on the Excel boards. I really would rather this be totally driven from the Excel side of the interface. "Doug Robbins - Word MVP" wrote: The following code, written for use with Word 2007 and an Excel 2007 data source requires: 1. In the Visual Basic Editor - a reference must be set to the Microsoft Office 12.0 Access database engine Object Library If using earlier versions of Office, the reference should be set to the Microsoft DAO 3.6 Object Library and the following line of the code Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") must be replaced with Set db = OpenDatabase(DataSource, False, False, "Excel 8.0; IMEX=1;") I do not know the equivalent of these setup steps with a Mac. If you need assistance with that aspect of it, I would suggest that you post to one of the Microsoft.Public.Mac.Office newsgroups. 2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" When the code runs, it will create a document variable for each record in the datasource with the name of the variable being the item in the first column of the spreadsheet and the value of the variable being the item in the second column of the spreadsheet. To facilitate the insertion of the Docvariable fields in the document, I would create a userform with a listbox that was populated with the field names from the first column of the spreadsheet and a button that when clicked would insert a docvariable field into the documnet for the field that was selected in the listbox. It would in fact be possible to construct a user interface for this that had virtually the same functionality as the mail merge interface. Dim Msg, Style, Title, Response Dim i As Long Dim j As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim varname As String Dim varvalue As String Dim fd As FileDialog Dim DataSource As String Dim avar As Variable For Each avar In ActiveDocument.Variables avar.Value = " " Next avar 'Display the file selection dialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Select Data Source" .InitialFileName = "" If .Show = -1 Then DataSource = .SelectedItems(1) Else Exit Sub End If End With Set fd = Nothing Msg = "Please ensure that Excel is closed before Continuing. Do you want to continue?" Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Refresh Data" ' Define title. ' Display message. Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. ' Open the file containing the Data Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") Set rs = db.OpenRecordset("SELECT * FROM `DataItems`") With rs .MoveFirst For i = 0 To .RecordCount ActiveDocument.Variables(Format(.Fields(0).Value)) = ..Fields(1).Value .MoveNext Next i End With ' Cleanup rs.Close Set rs = Nothing db.Close Set db = Nothing Else Exit Sub End If -- 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 "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
You haven't studied the code, huh?
It presents the user with a dialog that allows them to browse to and select the Excel file. It could all be done from Excel - See the article "Control Word from Excel" at: http://www.word.mvps.org/FAQs/InterD...WordFromXL.htm -- 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 "AndyC812" wrote in message ... That's outstanding, thanks for the effort. So, I would need a different version of my report for 2007, 2003 and Mac? I have users who are in each environment. This macro would be run from the Word side? Is that correct? How does the macro know what worksheet to look for the data in? Thanks again, Doug, I really appreciate it. I will also check on the Excel boards. I really would rather this be totally driven from the Excel side of the interface. "Doug Robbins - Word MVP" wrote: The following code, written for use with Word 2007 and an Excel 2007 data source requires: 1. In the Visual Basic Editor - a reference must be set to the Microsoft Office 12.0 Access database engine Object Library If using earlier versions of Office, the reference should be set to the Microsoft DAO 3.6 Object Library and the following line of the code Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") must be replaced with Set db = OpenDatabase(DataSource, False, False, "Excel 8.0; IMEX=1;") I do not know the equivalent of these setup steps with a Mac. If you need assistance with that aspect of it, I would suggest that you post to one of the Microsoft.Public.Mac.Office newsgroups. 2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" When the code runs, it will create a document variable for each record in the datasource with the name of the variable being the item in the first column of the spreadsheet and the value of the variable being the item in the second column of the spreadsheet. To facilitate the insertion of the Docvariable fields in the document, I would create a userform with a listbox that was populated with the field names from the first column of the spreadsheet and a button that when clicked would insert a docvariable field into the documnet for the field that was selected in the listbox. It would in fact be possible to construct a user interface for this that had virtually the same functionality as the mail merge interface. Dim Msg, Style, Title, Response Dim i As Long Dim j As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim varname As String Dim varvalue As String Dim fd As FileDialog Dim DataSource As String Dim avar As Variable For Each avar In ActiveDocument.Variables avar.Value = " " Next avar 'Display the file selection dialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Select Data Source" .InitialFileName = "" If .Show = -1 Then DataSource = .SelectedItems(1) Else Exit Sub End If End With Set fd = Nothing Msg = "Please ensure that Excel is closed before Continuing. Do you want to continue?" Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Refresh Data" ' Define title. ' Display message. Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. ' Open the file containing the Data Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") Set rs = db.OpenRecordset("SELECT * FROM `DataItems`") With rs .MoveFirst For i = 0 To .RecordCount ActiveDocument.Variables(Format(.Fields(0).Value)) = ..Fields(1).Value .MoveNext Next i End With ' Cleanup rs.Close Set rs = Nothing db.Close Set db = Nothing Else Exit Sub End If -- 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 "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
Yes, I did, but I got lost quickly. :-) It's been a long while since I coded
anything. That's why I built my app in Excel, not VBA! :-) Thanks for all your efforts. I will read the article you suggested with interest! -- Thanks, Andy "Doug Robbins - Word MVP" wrote: You haven't studied the code, huh? It presents the user with a dialog that allows them to browse to and select the Excel file. It could all be done from Excel - See the article "Control Word from Excel" at: http://www.word.mvps.org/FAQs/InterD...WordFromXL.htm -- 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 "AndyC812" wrote in message ... That's outstanding, thanks for the effort. So, I would need a different version of my report for 2007, 2003 and Mac? I have users who are in each environment. This macro would be run from the Word side? Is that correct? How does the macro know what worksheet to look for the data in? Thanks again, Doug, I really appreciate it. I will also check on the Excel boards. I really would rather this be totally driven from the Excel side of the interface. "Doug Robbins - Word MVP" wrote: The following code, written for use with Word 2007 and an Excel 2007 data source requires: 1. In the Visual Basic Editor - a reference must be set to the Microsoft Office 12.0 Access database engine Object Library If using earlier versions of Office, the reference should be set to the Microsoft DAO 3.6 Object Library and the following line of the code Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") must be replaced with Set db = OpenDatabase(DataSource, False, False, "Excel 8.0; IMEX=1;") I do not know the equivalent of these setup steps with a Mac. If you need assistance with that aspect of it, I would suggest that you post to one of the Microsoft.Public.Mac.Office newsgroups. 2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" When the code runs, it will create a document variable for each record in the datasource with the name of the variable being the item in the first column of the spreadsheet and the value of the variable being the item in the second column of the spreadsheet. To facilitate the insertion of the Docvariable fields in the document, I would create a userform with a listbox that was populated with the field names from the first column of the spreadsheet and a button that when clicked would insert a docvariable field into the documnet for the field that was selected in the listbox. It would in fact be possible to construct a user interface for this that had virtually the same functionality as the mail merge interface. Dim Msg, Style, Title, Response Dim i As Long Dim j As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim varname As String Dim varvalue As String Dim fd As FileDialog Dim DataSource As String Dim avar As Variable For Each avar In ActiveDocument.Variables avar.Value = " " Next avar 'Display the file selection dialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Select Data Source" .InitialFileName = "" If .Show = -1 Then DataSource = .SelectedItems(1) Else Exit Sub End If End With Set fd = Nothing Msg = "Please ensure that Excel is closed before Continuing. Do you want to continue?" Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Refresh Data" ' Define title. ' Display message. Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. ' Open the file containing the Data Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") Set rs = db.OpenRecordset("SELECT * FROM `DataItems`") With rs .MoveFirst For i = 0 To .RecordCount ActiveDocument.Variables(Format(.Fields(0).Value)) = ..Fields(1).Value .MoveNext Next i End With ' Cleanup rs.Close Set rs = Nothing db.Close Set db = Nothing Else Exit Sub End If -- 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 "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. |
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
Also, I wasn't talking about which Excel file to open, but what worksheet
WITHIN the Excel file to use. I'm coming to the following as a possible deisgn for my app: 1. From my Excel file, store the outputs (header, data pairs) on one worksheet called Data Table1, where Col A are the field names and Col B is the value of the field (I have already made that modification). 2. Write a program to write the above data into a CSV file, the name of which is determined by the Excel app. (This is essentially done as well). I may embellish this with some of your code to allow the user to determine where they want to save the file. 3. Write a program to open Word with a particular mail merge report template, send it the path\name of the CSV and do a Merge to New Document. Save the document with the same name as the CSV, except as a .doc. I am only now beginning to put together how this might be done. The actual merge may have to be done from the Word side, I don't know. This is wher I need the most help. Not sure this will solve the Mac problem, but it should make it easier to generate reports in Windows. -- Thanks, Andy "Doug Robbins - Word MVP" wrote: You haven't studied the code, huh? It presents the user with a dialog that allows them to browse to and select the Excel file. It could all be done from Excel - See the article "Control Word from Excel" at: http://www.word.mvps.org/FAQs/InterD...WordFromXL.htm -- 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 "AndyC812" wrote in message ... That's outstanding, thanks for the effort. So, I would need a different version of my report for 2007, 2003 and Mac? I have users who are in each environment. This macro would be run from the Word side? Is that correct? How does the macro know what worksheet to look for the data in? Thanks again, Doug, I really appreciate it. I will also check on the Excel boards. I really would rather this be totally driven from the Excel side of the interface. "Doug Robbins - Word MVP" wrote: The following code, written for use with Word 2007 and an Excel 2007 data source requires: 1. In the Visual Basic Editor - a reference must be set to the Microsoft Office 12.0 Access database engine Object Library If using earlier versions of Office, the reference should be set to the Microsoft DAO 3.6 Object Library and the following line of the code Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") must be replaced with Set db = OpenDatabase(DataSource, False, False, "Excel 8.0; IMEX=1;") I do not know the equivalent of these setup steps with a Mac. If you need assistance with that aspect of it, I would suggest that you post to one of the Microsoft.Public.Mac.Office newsgroups. 2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" When the code runs, it will create a document variable for each record in the datasource with the name of the variable being the item in the first column of the spreadsheet and the value of the variable being the item in the second column of the spreadsheet. To facilitate the insertion of the Docvariable fields in the document, I would create a userform with a listbox that was populated with the field names from the first column of the spreadsheet and a button that when clicked would insert a docvariable field into the documnet for the field that was selected in the listbox. It would in fact be possible to construct a user interface for this that had virtually the same functionality as the mail merge interface. Dim Msg, Style, Title, Response Dim i As Long Dim j As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim varname As String Dim varvalue As String Dim fd As FileDialog Dim DataSource As String Dim avar As Variable For Each avar In ActiveDocument.Variables avar.Value = " " Next avar 'Display the file selection dialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Select Data Source" .InitialFileName = "" If .Show = -1 Then DataSource = .SelectedItems(1) Else Exit Sub End If End With Set fd = Nothing Msg = "Please ensure that Excel is closed before Continuing. Do you want to continue?" Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Refresh Data" ' Define title. ' Display message. Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. ' Open the file containing the Data Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") Set rs = db.OpenRecordset("SELECT * FROM `DataItems`") With rs .MoveFirst For i = 0 To .RecordCount ActiveDocument.Variables(Format(.Fields(0).Value)) = ..Fields(1).Value .MoveNext Next i End With ' Cleanup rs.Close Set rs = Nothing db.Close Set db = Nothing Else Exit Sub End If -- 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 "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. |
#15
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
From my post of 6/13/2008
2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" The code accesses the range of cells to which the name "DataItems" has been applied. Your approach of converting the two columns in Excel to a two paragraph .CSV file may allow you to continue to use mailmerge, but I will leave the Excel coding to you. The microsoft.public.excel.programming newsgroup would be the place to go for assistance with that if you need it. -- 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 "AndyC812" wrote in message ... Also, I wasn't talking about which Excel file to open, but what worksheet WITHIN the Excel file to use. I'm coming to the following as a possible deisgn for my app: 1. From my Excel file, store the outputs (header, data pairs) on one worksheet called Data Table1, where Col A are the field names and Col B is the value of the field (I have already made that modification). 2. Write a program to write the above data into a CSV file, the name of which is determined by the Excel app. (This is essentially done as well). I may embellish this with some of your code to allow the user to determine where they want to save the file. 3. Write a program to open Word with a particular mail merge report template, send it the path\name of the CSV and do a Merge to New Document. Save the document with the same name as the CSV, except as a .doc. I am only now beginning to put together how this might be done. The actual merge may have to be done from the Word side, I don't know. This is wher I need the most help. Not sure this will solve the Mac problem, but it should make it easier to generate reports in Windows. -- Thanks, Andy "Doug Robbins - Word MVP" wrote: You haven't studied the code, huh? It presents the user with a dialog that allows them to browse to and select the Excel file. It could all be done from Excel - See the article "Control Word from Excel" at: http://www.word.mvps.org/FAQs/InterD...WordFromXL.htm -- 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 "AndyC812" wrote in message ... That's outstanding, thanks for the effort. So, I would need a different version of my report for 2007, 2003 and Mac? I have users who are in each environment. This macro would be run from the Word side? Is that correct? How does the macro know what worksheet to look for the data in? Thanks again, Doug, I really appreciate it. I will also check on the Excel boards. I really would rather this be totally driven from the Excel side of the interface. "Doug Robbins - Word MVP" wrote: The following code, written for use with Word 2007 and an Excel 2007 data source requires: 1. In the Visual Basic Editor - a reference must be set to the Microsoft Office 12.0 Access database engine Object Library If using earlier versions of Office, the reference should be set to the Microsoft DAO 3.6 Object Library and the following line of the code Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") must be replaced with Set db = OpenDatabase(DataSource, False, False, "Excel 8.0; IMEX=1;") I do not know the equivalent of these setup steps with a Mac. If you need assistance with that aspect of it, I would suggest that you post to one of the Microsoft.Public.Mac.Office newsgroups. 2. In the Excel spreadsheet that is selected when the code is run, there is a two column list with "field names" in the first column and the corresponding values in the second column. It assumes that the first row is a header row - probably containing the column titles "field name" and "value" (but what is actually in the cells of that row does not really matter. The list, including the header row must be named "DataItems" When the code runs, it will create a document variable for each record in the datasource with the name of the variable being the item in the first column of the spreadsheet and the value of the variable being the item in the second column of the spreadsheet. To facilitate the insertion of the Docvariable fields in the document, I would create a userform with a listbox that was populated with the field names from the first column of the spreadsheet and a button that when clicked would insert a docvariable field into the documnet for the field that was selected in the listbox. It would in fact be possible to construct a user interface for this that had virtually the same functionality as the mail merge interface. Dim Msg, Style, Title, Response Dim i As Long Dim j As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim varname As String Dim varvalue As String Dim fd As FileDialog Dim DataSource As String Dim avar As Variable For Each avar In ActiveDocument.Variables avar.Value = " " Next avar 'Display the file selection dialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Select Data Source" .InitialFileName = "" If .Show = -1 Then DataSource = .SelectedItems(1) Else Exit Sub End If End With Set fd = Nothing Msg = "Please ensure that Excel is closed before Continuing. Do you want to continue?" Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Refresh Data" ' Define title. ' Display message. Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. ' Open the file containing the Data Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;") Set rs = db.OpenRecordset("SELECT * FROM `DataItems`") With rs .MoveFirst For i = 0 To .RecordCount ActiveDocument.Variables(Format(.Fields(0).Value)) = ..Fields(1).Value .MoveNext Next i End With ' Cleanup rs.Close Set rs = Nothing db.Close Set db = Nothing Else Exit Sub End If -- 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 "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. |
#16
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Running up against the limit in Excel 2003
FWIW in Mac office the support for getting data from Excel is much more
limited, but it does depend on which version of Mac Office you are talking about. I don't think any version has the equivalent of ADO or DAO (on Windows these both rely on Jet, which is the original engine used by Access, and neither Access nor (AFAIK) Jet are part of Mac Office. Mac Word X let you use ODBC to get data - whether there was a driver for Excel I couldn't tell you. This support was dropped in Office 2004 (Excel could still use ODBC to get data, but Word could not). I don't know where that stuff has gone in Office 2008 but of course the main change there has been the complete removal of VBA, which means (AFAIK) that you can only automate these applications "from outside" the programs - e.g. you would have to write a program - say in AppleScript - that automated both Word and Excel. As far as I can remember, you can automate Excel 2004 from Word 2004 and get data out of Excel, but as I said you wouldn't be able to use the precise method Doug has suggested. -- Peter Jamieson http://tips.pjmsn.me.uk (Needs an update!) "AndyC812" wrote in message ... OK, so you think this will also fix my problem with Mac reports also? So let me see, I need to transpose my data tab, headers in A and data in B. Then I need to replace all of the merge fields in my report documents with docvariable fields. Then I need to write a VBA procedure that will read the header, data pairs and send them to the correct Word template. Is that all? :-) Would it be asking too much for some sample code to get me started? I really appreciate you sticking with both of my questions! -Andy "Doug Robbins - Word MVP" wrote: Yes. In fact it must be done with VBA. -- 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 "AndyC812" wrote in message ... Docvariable fields! That sounds promising. I'll read up on those. Could I use VBA to also drive the report generation in Word? I would like to be able to push a button in the Excel file and have a "merged" report pop up in Word based on the field data in the spreadsheet. "Doug Robbins - Word MVP" wrote: In that case, you could have the "field names" in one column and the data for each "field" in the cells of the adjacted column and then you would not be limited by the number of columns available. Of course, you would then have to use something other than mailmerge to create the report and the way that I would do it would be to use Docvariable fields in the document in place of merge fields and use VBA to iterate through the rows of the spreadsheet, creating variables in the document with the name of the variables being the "field names" from the first column and the values of the variables being set to the data from the corresponding cells in the second column. -- 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 "AndyC812" wrote in message ... Just one report. Each spreadsheet generates one LONG reord of data based on user input to the other worksheets. "Doug Robbins - Word MVP" wrote: Are you preparing multiple reports from the data or is it just one report? -- 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 "AndyC812" wrote in message ... My application has multiple tabs and generates a 7-9 page report in Word using 250 merge fields. Not all the fields are used in all the reports, but I'm running out of room. Others have suggested CSV also, but can I automate it as well (generate the CSV from Excel and "Send it" to the proper merge template and do a "Merge to New Document" and perhaps even rename the resulting file. Thanks! "Doug Robbins - Word MVP" wrote: I find it hard to imagine a mail merge that needs that many fields. However, if you do need to exceed the 255 column limit of Excel, you can use a .csv file as the data source. -- 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 "AndyC812" wrote in message ... I have created an application in Excel that collects data for eventual merging into a Word mail merge document. It is so large that I am running out of columns in Excel for new merge fields (I am using 250 of possible 255). My question - is there a better way to get my data fields from Excel into Word (CSV file, XML?) ? Can this be automated? Any tips would be appreciated! Thanks! Andy |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 2003 running slowly | Microsoft Word Help | |||
Limit the number of decimal places shown when I merge Excel-Word | Mailmerge | |||
charater limit merging from excel to word | Mailmerge | |||
Is there a limit as to Address using Excel as source | Mailmerge | |||
Macros running very slow in Excel 2003 | Microsoft Word Help |