Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Word with a mutiple sheets Excel file
Is there anyone could help me doing this:
I have a word file and I want to merge it with a 4 sheets excel file; I'd like word to ask me to which sheet connect while opening the mail merge file. Sheets are all in the same format. Thank you Guido |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Word with a mutiple sheets Excel file
If it's Word 2002/2003, you should get a choice of sheets by default when
you set up the connection to the workbook. That's obviously not quite the same as "when you open the mail merge file", but all you have to do is go through the connection process again. You will lose any sorts/filters you set up. To automate reconnection you would need to create a VBA module in your document. For Word 2002/2003 it can be roughly as follows: Sub AutoOpen() Dim strDataSourcePathName ' Substitute the pathname you need here strDataSourcePathName = "c:\mydata\myworkbook.xls" ' Make sure the previous data source is disconnected ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ' substitute the merge type you need here ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ' Open the data source - in this case, you should only need the path name of the .xls ActiveDocument.MailMerge.OpenDataSource _ Name:=strDataSourcePathName End Sub If it's Word 2000/97, things are more difficult because the default connection method (DDE) does not let you choose a sheet, and the ODBC method forces you to check various options in its dialog before you can choose a sheet. Manually, it might be best to reconnect using the "Excel Converter". Worse, using code you can probably only connect to ODBC data and I am fairly sure that you have to specify the sheet name - you cnanot leave it up to the user to choose. Peter Jamieson "Guido Rossi" wrote in message ... Is there anyone could help me doing this: I have a word file and I want to merge it with a 4 sheets excel file; I'd like word to ask me to which sheet connect while opening the mail merge file. Sheets are all in the same format. Thank you Guido |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Word with a mutiple sheets Excel file
Peter,
I am having a similiar problem that changing the options and following the directions you mention in your previous reply this thread doesn't seem to help. My new computer came with Office Pro 2003. It seems that when trying to set up connection between the word doc and the xls file, it's dividng up my xls file into several html files. None of the names match the sheets in the workbook. I can't make it let me choose the sheet I need for anything. And it's ruining the original xls file in the process. I open it up independently and all the data is completely gone, apparently moved to html files in another folder?This is insane. I've had to retrieve it from a back up several times now and it's getting old real fast. Doesn't seem to matter how I try to connect them, through the wizard, the tool bar, whatever. I used mail merge with word and excel 2000 quite a bit and I never ran into anything even close to this. I just want to have the merge fields show up in a word doc from a particular worksheet within the file., no complicated filtering or anything like that. It's very likely I am clueless and missing some obvious change between the old and new, but that doesn't help me be any less frustrated. Any ideas? And you say we have to do some VBA coding to just keep the files connected with each other now, which Word 97 and 2000 seemed to do on their own with appararently no problem? That's not what I'd call very user friendly. Please tell me your kidding. Mail merging was the only reason I even got Office on this new computer, but if I have to dig up my old office 2000 and install it just to have basic mail merge, I'm sure you'll see the mushroom cloud from where ever your are as my head implodes, hehehe. -LM "Peter Jamieson" wrote: If it's Word 2002/2003, you should get a choice of sheets by default when you set up the connection to the workbook. That's obviously not quite the same as "when you open the mail merge file", but all you have to do is go through the connection process again. You will lose any sorts/filters you set up. To automate reconnection you would need to create a VBA module in your document. For Word 2002/2003 it can be roughly as follows: Sub AutoOpen() Dim strDataSourcePathName ' Substitute the pathname you need here strDataSourcePathName = "c:\mydata\myworkbook.xls" ' Make sure the previous data source is disconnected ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ' substitute the merge type you need here ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ' Open the data source - in this case, you should only need the path name of the .xls ActiveDocument.MailMerge.OpenDataSource _ Name:=strDataSourcePathName End Sub If it's Word 2000/97, things are more difficult because the default connection method (DDE) does not let you choose a sheet, and the ODBC method forces you to check various options in its dialog before you can choose a sheet. Manually, it might be best to reconnect using the "Excel Converter". Worse, using code you can probably only connect to ODBC data and I am fairly sure that you have to specify the sheet name - you cnanot leave it up to the user to choose. Peter Jamieson "Guido Rossi" wrote in message ... Is there anyone could help me doing this: I have a word file and I want to merge it with a 4 sheets excel file; I'd like word to ask me to which sheet connect while opening the mail merge file. Sheets are all in the same format. Thank you Guido |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Word with a mutiple sheets Excel file
You are obviously doing something different to what thousands of other
people are doing. Try using the original 3-Step Mail Merge Helper which you can get access to by selecting Customize from the Tools menu and on the Commands tab, select the All Commands category and then scroll down through the list of Commands until you come to the Mail Merge Helper item and then left click on it and drag it onto a toolbar or the expanded Tools menu. There is also a Mail Merge Toolbar that can be activated by selecting Toolbars from the View menu and then checking the Mail Merge item. Forget what Peter said about VBA, that was for circumstances that are different from yours. -- 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 "ElleEm" wrote in message ... Peter, I am having a similiar problem that changing the options and following the directions you mention in your previous reply this thread doesn't seem to help. My new computer came with Office Pro 2003. It seems that when trying to set up connection between the word doc and the xls file, it's dividng up my xls file into several html files. None of the names match the sheets in the workbook. I can't make it let me choose the sheet I need for anything. And it's ruining the original xls file in the process. I open it up independently and all the data is completely gone, apparently moved to html files in another folder?This is insane. I've had to retrieve it from a back up several times now and it's getting old real fast. Doesn't seem to matter how I try to connect them, through the wizard, the tool bar, whatever. I used mail merge with word and excel 2000 quite a bit and I never ran into anything even close to this. I just want to have the merge fields show up in a word doc from a particular worksheet within the file., no complicated filtering or anything like that. It's very likely I am clueless and missing some obvious change between the old and new, but that doesn't help me be any less frustrated. Any ideas? And you say we have to do some VBA coding to just keep the files connected with each other now, which Word 97 and 2000 seemed to do on their own with appararently no problem? That's not what I'd call very user friendly. Please tell me your kidding. Mail merging was the only reason I even got Office on this new computer, but if I have to dig up my old office 2000 and install it just to have basic mail merge, I'm sure you'll see the mushroom cloud from where ever your are as my head implodes, hehehe. -LM "Peter Jamieson" wrote: If it's Word 2002/2003, you should get a choice of sheets by default when you set up the connection to the workbook. That's obviously not quite the same as "when you open the mail merge file", but all you have to do is go through the connection process again. You will lose any sorts/filters you set up. To automate reconnection you would need to create a VBA module in your document. For Word 2002/2003 it can be roughly as follows: Sub AutoOpen() Dim strDataSourcePathName ' Substitute the pathname you need here strDataSourcePathName = "c:\mydata\myworkbook.xls" ' Make sure the previous data source is disconnected ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ' substitute the merge type you need here ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ' Open the data source - in this case, you should only need the path name of the .xls ActiveDocument.MailMerge.OpenDataSource _ Name:=strDataSourcePathName End Sub If it's Word 2000/97, things are more difficult because the default connection method (DDE) does not let you choose a sheet, and the ODBC method forces you to check various options in its dialog before you can choose a sheet. Manually, it might be best to reconnect using the "Excel Converter". Worse, using code you can probably only connect to ODBC data and I am fairly sure that you have to specify the sheet name - you cnanot leave it up to the user to choose. Peter Jamieson "Guido Rossi" wrote in message ... Is there anyone could help me doing this: I have a word file and I want to merge it with a 4 sheets excel file; I'd like word to ask me to which sheet connect while opening the mail merge file. Sheets are all in the same format. Thank you Guido |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 97 in Windows XP to maintain formatting | Microsoft Word Help | |||
Reveal codes in a word document | Microsoft Word Help | |||
Word should allow to 'divide' page by 3 or 4, not just 2 | Page Layout | |||
WP merge file to Word | Tables | |||
Word Field Codes in Excel data file | Mailmerge |