Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
I have a mail merge document and need to attach a different
worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
What you probably need to do is have the path\filename of the files that you
want included in a field in the data source and then use a method similar to that in the "Graphics from data base" item under the "Special merges" section of fellow MVP Cindy Meister's website at http://homepage.swissonline.ch/cindy...r/MergFram.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 "BeJay" wrote in message oups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
There's a problem trying to merge LINK fields, because if you manually
update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. using F9), then merge to a new document, you should end up with the links you need. You may need to select them all and press F9 to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars to enable the database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressing F9. c. Use Alt-F9 to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message oups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
On 16 May, 08:29, "Peter Jamieson"
wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. using F9), then merge to a new document, you should end up with the links you need. You may need to select them all and press F9 to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars to enable the database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressing F9. c. Use Alt-F9 to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message oups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found at http://cornell.veplan.net/article.aspx?&a=3815 it seems to have come together like a plan! Thanks soooooo much for advice! |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
On 16 May, 17:48, BeJay wrote:
On 16 May, 08:29, "Peter Jamieson" wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. usingF9), then merge to a new document, you should end up with the links you need. You may need to select them all and pressF9to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars toenablethe database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressingF9. c. Use Alt-F9to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message roups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found athttp://cornell.veplan.net/article.aspx?&a=3815 it seems to have come together like a plan! Thanks soooooo much for advice!- Hide quoted text - - Show quoted text - HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged document on Word2002 - it does not work, doesn't even look like it is thinking of working. On Word2000 it works fine and it is clear that it is refreshing the links as a message tells you so. "Word is updating the fields in this document". This doesn't happen in 2002. Anyone got any clues? Peter?? Anyone? |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
We're already at the boundary of what MS lets you do without turning to
VBA-type code. If you use Alt-F9 to look at the field codes in your destination document, do you see the sheet names you want in the LINK fields, or the same sheet name every time? Probably also worth checking Word Tools|Options|Print!"Update fields" and "Update links". Peter Jamieson "BeJay" wrote in message ups.com... On 16 May, 17:48, BeJay wrote: On 16 May, 08:29, "Peter Jamieson" wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. usingF9), then merge to a new document, you should end up with the links you need. You may need to select them all and pressF9to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars toenablethe database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressingF9. c. Use Alt-F9to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message roups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found athttp://cornell.veplan.net/article.aspx?&a=3815 it seems to have come together like a plan! Thanks soooooo much for advice!- Hide quoted text - - Show quoted text - HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged document on Word2002 - it does not work, doesn't even look like it is thinking of working. On Word2000 it works fine and it is clear that it is refreshing the links as a message tells you so. "Word is updating the fields in this document". This doesn't happen in 2002. Anyone got any clues? Peter?? Anyone? |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
On 17 May, 21:04, "Peter Jamieson"
wrote: We're already at the boundary of what MS lets you do without turning to VBA-type code. If you use Alt-F9 to look at the field codes in your destination document, do you see the sheet names you want in the LINK fields, or the same sheet name every time? Probably also worth checking Word Tools|Options|Print!"Update fields" and "Update links". Peter Jamieson "BeJay" wrote in message ups.com... On 16 May, 17:48, BeJay wrote: On 16 May, 08:29, "Peter Jamieson" wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. usingF9), then merge to a new document, you should end up with the links you need. You may need to select them all and pressF9to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars toenablethe database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressingF9. c. Use Alt-F9to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message roups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found athttp://cornell.veplan.net/article.aspx?&a=3815it seems to have come together like a plan! Thanks soooooo much for advice!- Hide quoted text - - Show quoted text - HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged document on Word2002 - it does not work, doesn't even look like it is thinking of working. On Word2000 it works fine and it is clear that it is refreshing the links as a message tells you so. "Word is updating the fields in this document". This doesn't happen in 2002. Anyone got any clues? Peter?? Anyone?- Hide quoted text - - Show quoted text - Have switched on the print options for update link and update , but this makes no difference. Also in the destination document, I cannot see the fields when I alt F9. I know this works on 2000, but I cannot get it to work here on 2002. I don't know on 2002 what field codes are carrying through, and can't quite believe that it worked so perfectly on 2000 but isn't now! Peter. I know I am taking up some bit of your valuable time, and really really do appreciate your expertise but again would be very pleased indeed to see another response from you!! |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
Hi BeJay,
I will try to look at this on the correct platform(s) as soon as I can, but right now it's unlikely to be for a couple of days. If you don't see a reponse here by (say) end of Monday, can you please post a reminder? Peter Jamieson "BeJay" wrote in message ups.com... On 17 May, 21:04, "Peter Jamieson" wrote: We're already at the boundary of what MS lets you do without turning to VBA-type code. If you use Alt-F9 to look at the field codes in your destination document, do you see the sheet names you want in the LINK fields, or the same sheet name every time? Probably also worth checking Word Tools|Options|Print!"Update fields" and "Update links". Peter Jamieson "BeJay" wrote in message ups.com... On 16 May, 17:48, BeJay wrote: On 16 May, 08:29, "Peter Jamieson" wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. usingF9), then merge to a new document, you should end up with the links you need. You may need to select them all and pressF9to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars toenablethe database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressingF9. c. Use Alt-F9to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message roups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found athttp://cornell.veplan.net/article.aspx?&a=3815it seems to have come together like a plan! Thanks soooooo much for advice!- Hide quoted text - - Show quoted text - HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged document on Word2002 - it does not work, doesn't even look like it is thinking of working. On Word2000 it works fine and it is clear that it is refreshing the links as a message tells you so. "Word is updating the fields in this document". This doesn't happen in 2002. Anyone got any clues? Peter?? Anyone?- Hide quoted text - - Show quoted text - Have switched on the print options for update link and update , but this makes no difference. Also in the destination document, I cannot see the fields when I alt F9. I know this works on 2000, but I cannot get it to work here on 2002. I don't know on 2002 what field codes are carrying through, and can't quite believe that it worked so perfectly on 2000 but isn't now! Peter. I know I am taking up some bit of your valuable time, and really really do appreciate your expertise but again would be very pleased indeed to see another response from you!! |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
On 18 May, 12:22, "Peter Jamieson"
wrote: Hi BeJay, I will try to look at this on the correct platform(s) as soon as I can, but right now it's unlikely to be for a couple of days. If you don't see a reponse here by (say) end of Monday, can you please post a reminder? Peter Jamieson"BeJay" wrote in message ups.com... On 17 May, 21:04, "Peter Jamieson" wrote: We're already at the boundary of what MS lets you do without turning to VBA-type code. If you use Alt-F9 to look at the field codes in your destination document, do you see the sheet names you want in the LINK fields, or the same sheet name every time? Probably also worth checking Word Tools|Options|Print!"Update fields" and "Update links". Peter Jamieson "BeJay" wrote in message roups.com... On 16 May, 17:48, BeJay wrote: On 16 May, 08:29, "Peter Jamieson" wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. usingF9), then merge to a new document, you should end up with the links you need. You may need to select them all and pressF9to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars toenablethe database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressingF9. c. Use Alt-F9to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message roups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found athttp://cornell.veplan.net/article.aspx?&a=3815itseems to have come together like a plan! Thanks soooooo much for advice!- Hide quoted text - - Show quoted text - HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged document on Word2002 - it does not work, doesn't even look like it is thinking of working. On Word2000 it works fine and it is clear that it is refreshing the links as a message tells you so. "Word is updating the fields in this document". This doesn't happen in 2002. Anyone got any clues? Peter?? Anyone?- Hide quoted text - - Show quoted text - Have switched on the print options for update link and update , but this makes no difference. Also in the destination document, I cannot see the fields when I alt F9. I know this works on 2000, but I cannot get it to work here on 2002. I don't know on 2002 what field codes are carrying through, and can't quite believe that it worked so perfectly on 2000 but isn't now! Peter. I know I am taking up some bit of your valuable time, and really really do appreciate your expertise but again would be very pleased indeed to see another response from you!!- Hide quoted text - - Show quoted text - Hi Peter, Any luck?!?! I have a feeling that I have hit a brick wall with this one. Appreciate all your help though. Thnx. Bev |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Variable worksheets to attach to doc as result merged data
Hi BeJay,
Let's continue if necessary in the other conversation. Peter Jamieson "BeJay" wrote in message oups.com... On 18 May, 12:22, "Peter Jamieson" wrote: Hi BeJay, I will try to look at this on the correct platform(s) as soon as I can, but right now it's unlikely to be for a couple of days. If you don't see a reponse here by (say) end of Monday, can you please post a reminder? Peter Jamieson"BeJay" wrote in message ups.com... On 17 May, 21:04, "Peter Jamieson" wrote: We're already at the boundary of what MS lets you do without turning to VBA-type code. If you use Alt-F9 to look at the field codes in your destination document, do you see the sheet names you want in the LINK fields, or the same sheet name every time? Probably also worth checking Word Tools|Options|Print!"Update fields" and "Update links". Peter Jamieson "BeJay" wrote in message roups.com... On 16 May, 17:48, BeJay wrote: On 16 May, 08:29, "Peter Jamieson" wrote: There's a problem trying to merge LINK fields, because if you manually update the LINK field or save the Word document, Word tends to "flatten" the file name and the "subset name" in the LINK field, removing any fields you have in there and replacing them by their results. This certainly happens in recent version of Word. For example, if you have something like { LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD sheetname }!R1:R32767" } when you update the field or save the document, the chances are that when you re-open the document, { MERGEFIELD sheetname } will have been permanenty replaced by one of the sheet names. So merging it after that, you always get the same sheet. If you set up your LINK, do not update the LINK field (e.g. usingF9), then merge to a new document, you should end up with the links you need. You may need to select them all and pressF9to update them. If you want the sheet to appear as it does in Excel, that's probably the only reasonably simple approach that will work. If you just want the data in the sheet in a tabular format, you can use the { DATABASE } field approach instead. In that case, proceed as follows: a. use View|Toolbars toenablethe database toolbar b. use the Insert|Database button to insert the data from one of the worksheets , choosing the "Insert as field" option in the last step. Make sure the field will work a second time (sometimes they don't) by selecting it and pressingF9. c. Use Alt-F9to look at the field. it should have recorded the workbook pathname, a long connection string (you can probably cut almost all of it out), and an \s parameter containing a SQL statement like SELECT * FROM `Sheet1$` insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so you have SELECT * FROM `{ MERGEFIELD Locref }$` (in this case I'm assuming LocRef does not contain the $) Then try merging to a new document. (As usual, all the {} have to be the special field code braces you can insert using ctrl-F9). You have little control over the layout of this table except using the built-in styles. If you need more control, you either have to postprocess the output or use VBA and Word Events to do it as you go along. the \*Mergeformat switch looks like it may preserve existing layout, but when you get to a sheet with more rows, the additional rows are not formatted the way you want. (I guess if all your sheets have the same number of rows, it would work). Peter Jamieson "BeJay" wrote in message roups.com... I have a mail merge document and need to attach a different worksheet(or workbook if this is the only way) dependant on merged data. I am re-posting this in the hope someone can help prevent insanity setting in. My brain is now stuck in a loop and I'm really not getting anywhere..... In essence:- Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where MERGEFIELD LocRef= FRED, link to worksheet FRED. However, what is happening is:- Where MERGEFIELD LocRef = FRED, The merged letter is still linking to BOB worksheet and doesn't seem to want to pick up FRED. I have tried on seperate workbooks called BOB, FRED etc, but I still get the same problem. Again, any advice would be most gratefully appreciated. PS thanks to Doug for your suggestions yesterday, but can't get them to work for me (possibly more to do with my inadequacies than your suggested routes!?! been trying all day!! )... any further suggestions? - thanks in advance.- Hide quoted text - - Show quoted text - Peter .... I was just about to throw in the towel, when I found that using your technique of not updating the link until after the merge in combination with a bookmarking and MERGESEQ method found athttp://cornell.veplan.net/article.aspx?&a=3815itseems to have come together like a plan! Thanks soooooo much for advice!- Hide quoted text - - Show quoted text - HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged document on Word2002 - it does not work, doesn't even look like it is thinking of working. On Word2000 it works fine and it is clear that it is refreshing the links as a message tells you so. "Word is updating the fields in this document". This doesn't happen in 2002. Anyone got any clues? Peter?? Anyone?- Hide quoted text - - Show quoted text - Have switched on the print options for update link and update , but this makes no difference. Also in the destination document, I cannot see the fields when I alt F9. I know this works on 2000, but I cannot get it to work here on 2002. I don't know on 2002 what field codes are carrying through, and can't quite believe that it worked so perfectly on 2000 but isn't now! Peter. I know I am taking up some bit of your valuable time, and really really do appreciate your expertise but again would be very pleased indeed to see another response from you!!- Hide quoted text - - Show quoted text - Hi Peter, Any luck?!?! I have a feeling that I have hit a brick wall with this one. Appreciate all your help though. Thnx. Bev |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I AutoSize (or Fit) merged, variable data within a text box? H | Mailmerge | |||
merge data from 2 excel worksheets into one word document | Mailmerge | |||
How do I attach a file to a merged email messages automatically? | Mailmerge | |||
Strange result when printing a merged document | Mailmerge | |||
How do I mail merge data from different Excel worksheets into one. | Microsoft Word Help |