Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Good Day,
I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Perhaps something like the following Excel VBA sub for starters (NB all the
code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Peter, Thank you. I will try this out and see if I can make it work. I had
two different versions of WORD that I have to work with, 2002 and 2003 and I couldn't grasp the issue of reference to WORD in the VBA editor. If I have two different versions is there something that could cover both of them?? Bob "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
1. A number of problems in 2002 were fixed in 2003 - if you encounter them,
I wouldn't waste much time trying to compromise to make 2002 work - by all means raise specific problems here but there's no guarantee that there will be a /simple/ solution, if any. I couldn't grasp the issue of reference to WORD in the VBA editor. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. I would suggest that you work in Word/Excel 2002 and see what happens as/when you port to 2003, but you would be better off searching one of the VBA-oriented groups (e.g. using groups.google.com ) for information on cross-version development. In this case, you should not encounter any differences in the /interfaces (i.e. method parameters should be the same on both versions, and most are omitted in the code I suggested, but the behaviour of the method may differ). Peter Jamieson "BobR" wrote in message .. . Peter, Thank you. I will try this out and see if I can make it work. I had two different versions of WORD that I have to work with, 2002 and 2003 and I couldn't grasp the issue of reference to WORD in the VBA editor. If I have two different versions is there something that could cover both of them?? Bob "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Peter, Thank you so much.
We're going to have the 3 computers we have with 2002 still on it upgrade to 2003 and that should fix that issue. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... BOB "Peter Jamieson" wrote in message ... 1. A number of problems in 2002 were fixed in 2003 - if you encounter them, I wouldn't waste much time trying to compromise to make 2002 work - by all means raise specific problems here but there's no guarantee that there will be a /simple/ solution, if any. I couldn't grasp the issue of reference to WORD in the VBA editor. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. I would suggest that you work in Word/Excel 2002 and see what happens as/when you port to 2003, but you would be better off searching one of the VBA-oriented groups (e.g. using groups.google.com ) for information on cross-version development. In this case, you should not encounter any differences in the /interfaces (i.e. method parameters should be the same on both versions, and most are omitted in the code I suggested, but the behaviour of the method may differ). Peter Jamieson "BobR" wrote in message .. . Peter, Thank you. I will try this out and see if I can make it work. I had two different versions of WORD that I have to work with, 2002 and 2003 and I couldn't grasp the issue of reference to WORD in the VBA editor. If I have two different versions is there something that could cover both of them?? Bob "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
We're going to have the 3 computers we have with 2002 still on it upgrade
to 2003 and that should fix that issue. I'd consider waiting and seeing. If everything works OK with 2002, why change? On the other hand, if it's simpler to get everything in line with 2003, why not? Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? If you make the reference, then distribute an Excel workbook that uses it, it should work. But you really need to /test/ as many of the things that you eventually intend to /do/ as you possibly can. 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... Name:=ActiveWorkbook.FullName is intended to get the full path name of the workbook the user is working with, using the Excel Object Model. ActiveWorkbook.FullName has a specific meaning in that case, If you want to use a different file name, provide the full path name, e.g. Name:="Name=C:\folder one\folder two\yourworkbookname.xls", _ In a similar way, SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" is intended to get the active worksheet name from the Excel Object Model. ActveWorkbook.ActiveSheet (and .Name) have a specific meaning in that case. Appending "$" may or may not be necessary. If you are trying to use "SELECT * from [" & MyDataBase.Database & "$]" it suggests you might be using some other object model (it looks a bit like Access/DAO to me). In that case, let us know. Peter Jamieson "BobR" wrote in message .. . Peter, Thank you so much. We're going to have the 3 computers we have with 2002 still on it upgrade to 2003 and that should fix that issue. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... BOB "Peter Jamieson" wrote in message ... 1. A number of problems in 2002 were fixed in 2003 - if you encounter them, I wouldn't waste much time trying to compromise to make 2002 work - by all means raise specific problems here but there's no guarantee that there will be a /simple/ solution, if any. I couldn't grasp the issue of reference to WORD in the VBA editor. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. I would suggest that you work in Word/Excel 2002 and see what happens as/when you port to 2003, but you would be better off searching one of the VBA-oriented groups (e.g. using groups.google.com ) for information on cross-version development. In this case, you should not encounter any differences in the /interfaces (i.e. method parameters should be the same on both versions, and most are omitted in the code I suggested, but the behaviour of the method may differ). Peter Jamieson "BobR" wrote in message .. . Peter, Thank you. I will try this out and see if I can make it work. I had two different versions of WORD that I have to work with, 2002 and 2003 and I couldn't grasp the issue of reference to WORD in the VBA editor. If I have two different versions is there something that could cover both of them?? Bob "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Thank you
"Peter Jamieson" wrote in message ... We're going to have the 3 computers we have with 2002 still on it upgrade to 2003 and that should fix that issue. I'd consider waiting and seeing. If everything works OK with 2002, why change? On the other hand, if it's simpler to get everything in line with 2003, why not? We have just salvaged about 20 desktops and since we own 2003 on them we're going to remove 3 of them and put them on our 3 missing laptops. Doesn't make sense to let something we own go out the door if we can remove it and use it. If it were many more I would say the same as you, but it's only the three.... Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? If you make the reference, then distribute an Excel workbook that uses it, it should work. But you really need to /test/ as many of the things that you eventually intend to /do/ as you possibly can. Thanks for the heads up, I've got two other testers that I'm having use it and when I disburse it to them and the others I'll try your suggestion.. 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... Name:=ActiveWorkbook.FullName is intended to get the full path name of the workbook the user is working with, using the Excel Object Model. ActiveWorkbook.FullName has a specific meaning in that case, If you want to use a different file name, provide the full path name, e.g. Name:="Name=C:\folder one\folder two\yourworkbookname.xls", _ *****Did I form this one right???****** OpenDataSource _ Name:=C:\LettersForms\Full Database.xls, _ sqlstatement1:="SELECT * FROM [" Full Database.DataBase & "$]" End With This is my path that will not change and my "Full Database.xls" will never change and the Select From is always the "Database$" tab. your thoughts once more? In a similar way, SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" is intended to get the active worksheet name from the Excel Object Model. ActveWorkbook.ActiveSheet (and .Name) have a specific meaning in that case. Appending "$" may or may not be necessary. If you are trying to use "SELECT * from [" & MyDataBase.Database & "$]" it suggests you might be using some other object model (it looks a bit like Access/DAO to me). In that case, let us know. Everythin here is in an Excel database for the WORD documents merging source. THanks BOB Peter Jamieson "BobR" wrote in message .. . Peter, Thank you so much. We're going to have the 3 computers we have with 2002 still on it upgrade to 2003 and that should fix that issue. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... BOB "Peter Jamieson" wrote in message ... 1. A number of problems in 2002 were fixed in 2003 - if you encounter them, I wouldn't waste much time trying to compromise to make 2002 work - by all means raise specific problems here but there's no guarantee that there will be a /simple/ solution, if any. I couldn't grasp the issue of reference to WORD in the VBA editor. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. I would suggest that you work in Word/Excel 2002 and see what happens as/when you port to 2003, but you would be better off searching one of the VBA-oriented groups (e.g. using groups.google.com ) for information on cross-version development. In this case, you should not encounter any differences in the /interfaces (i.e. method parameters should be the same on both versions, and most are omitted in the code I suggested, but the behaviour of the method may differ). Peter Jamieson "BobR" wrote in message .. . Peter, Thank you. I will try this out and see if I can make it work. I had two different versions of WORD that I have to work with, 2002 and 2003 and I couldn't grasp the issue of reference to WORD in the VBA editor. If I have two different versions is there something that could cover both of them?? Bob "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
I got the following at the beginning of this code
Dim objWord As Word.Application Dim objDoc As Word.Document User-defined type not defined........ any ideas..?// The first line shows the error and the "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Given what you say, I suggest
OpenDataSource _ Name:="C:\LettersForms\Full Database.xls", _ sqlstatement1:="SELECT * FROM [DataBase$]" End With Peter Jamieson "BobR" wrote in message .. . Thank you "Peter Jamieson" wrote in message ... We're going to have the 3 computers we have with 2002 still on it upgrade to 2003 and that should fix that issue. I'd consider waiting and seeing. If everything works OK with 2002, why change? On the other hand, if it's simpler to get everything in line with 2003, why not? We have just salvaged about 20 desktops and since we own 2003 on them we're going to remove 3 of them and put them on our 3 missing laptops. Doesn't make sense to let something we own go out the door if we can remove it and use it. If it were many more I would say the same as you, but it's only the three.... Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? If you make the reference, then distribute an Excel workbook that uses it, it should work. But you really need to /test/ as many of the things that you eventually intend to /do/ as you possibly can. Thanks for the heads up, I've got two other testers that I'm having use it and when I disburse it to them and the others I'll try your suggestion.. 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... Name:=ActiveWorkbook.FullName is intended to get the full path name of the workbook the user is working with, using the Excel Object Model. ActiveWorkbook.FullName has a specific meaning in that case, If you want to use a different file name, provide the full path name, e.g. Name:="Name=C:\folder one\folder two\yourworkbookname.xls", _ *****Did I form this one right???****** OpenDataSource _ Name:=C:\LettersForms\Full Database.xls, _ sqlstatement1:="SELECT * FROM [" Full Database.DataBase & "$]" End With This is my path that will not change and my "Full Database.xls" will never change and the Select From is always the "Database$" tab. your thoughts once more? In a similar way, SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" is intended to get the active worksheet name from the Excel Object Model. ActveWorkbook.ActiveSheet (and .Name) have a specific meaning in that case. Appending "$" may or may not be necessary. If you are trying to use "SELECT * from [" & MyDataBase.Database & "$]" it suggests you might be using some other object model (it looks a bit like Access/DAO to me). In that case, let us know. Everythin here is in an Excel database for the WORD documents merging source. THanks BOB Peter Jamieson "BobR" wrote in message .. . Peter, Thank you so much. We're going to have the 3 computers we have with 2002 still on it upgrade to 2003 and that should fix that issue. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. Is there a way you know of to make the code enable this menu option, so I don't have to have issues with the operator of the computer?? 3. With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With I'm confused witht he third row.. for me woudl it read Name=C:\folder one\folder two,_ sqlstatement1.="SELECT * from [" & MyDataBase.Database & "$"]" or did I miss the boat... BOB "Peter Jamieson" wrote in message ... 1. A number of problems in 2002 were fixed in 2003 - if you encounter them, I wouldn't waste much time trying to compromise to make 2002 work - by all means raise specific problems here but there's no guarantee that there will be a /simple/ solution, if any. I couldn't grasp the issue of reference to WORD in the VBA editor. 2. When you create the macro I suggested, you have to use the Tools|References menu option in the VBA Editor to make a reference to Word. I would suggest that you work in Word/Excel 2002 and see what happens as/when you port to 2003, but you would be better off searching one of the VBA-oriented groups (e.g. using groups.google.com ) for information on cross-version development. In this case, you should not encounter any differences in the /interfaces (i.e. method parameters should be the same on both versions, and most are omitted in the code I suggested, but the behaviour of the method may differ). Peter Jamieson "BobR" wrote in message .. . Peter, Thank you. I will try this out and see if I can make it work. I had two different versions of WORD that I have to work with, 2002 and 2003 and I couldn't grasp the issue of reference to WORD in the VBA editor. If I have two different versions is there something that could cover both of them?? Bob "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
OPen the module & macro in Excel VBA.
Click Tools|References Lokk down the list till you find something like Microsoft Word 11.0 Object Library (it might be "10.0" if you're using Word 2002) Check the box to the left of the name Click OK Peter Jamieson "BobR" wrote in message .. . I got the following at the beginning of this code Dim objWord As Word.Application Dim objDoc As Word.Document User-defined type not defined........ any ideas..?// The first line shows the error and the "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Data Source being lost each time the word merge document starts
Peter, Thank you so much for the Specific information you gave to me. My
hat's off to all of you that help us out on these forums and Thanks just doesn't seem like enough but that's about all we've go, so Thanks BOB "Peter Jamieson" wrote in message ... OPen the module & macro in Excel VBA. Click Tools|References Lokk down the list till you find something like Microsoft Word 11.0 Object Library (it might be "10.0" if you're using Word 2002) Check the box to the left of the name Click OK Peter Jamieson "BobR" wrote in message .. . I got the following at the beginning of this code Dim objWord As Word.Application Dim objDoc As Word.Document User-defined type not defined........ any ideas..?// The first line shows the error and the "Peter Jamieson" wrote in message ... Perhaps something like the following Excel VBA sub for starters (NB all the code is executed in Excel, so it doesn't use a "selfactivating macro" as you were asking for. You would need to make a reference to the Word object in the Excel VBA editor. Sub Mergecurrentsheet() Dim objWord As Word.Application Dim objDoc As Word.Document Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If ' substitute the correct file name here Set objDoc = objWord.Documents.Open("C:\MyFiles\MergeLetters\?? ") If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If objDoc.Activate objWord.Visible = True With objDoc.MailMerge .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement1:="SELECT * FROM [" & ActiveWorkbook.ActiveSheet.Name & "$]" End With Set objDoc = Nothing Set objWord = Nothing End Sub Notes: a. this doesn't actually do the merge, but it could be done - you would need to specify merge type, destination, and so on b. do your own error handling code c. see http://tips.pjmsn.me.uk/t0003.htm for general info. about problems merging with Excel data.There are general problems getting data from Excel into Word. In this case, you might encounter locking problems if, for example, you are in the middle of editing an Excel cell when you invoke the macro. Saving the workbook before running the macro is probably a good move. Peter Jamieson "BobR" wrote in message ... Good Day, I have numerous letters that use an Excel spreadsheet as the data source and the documents are in WORD 2003 and the data is in EXCEL 2003. I have code in the excel spreadsheet to simply go to C:\MyFiles\MergeLetters\?? (?? is a specific named file) and open the WORD document. Once the document is opened, it just sits there and is ready for instructions as it is a non merge format at this time. (We've tried to have the opening code from excel to make it open to the data source and had really poor results with it.) My question is can someone give me the proper code (IF this can be done) that when letter one located at C:\MyFiles\MergeLetters\letter one.doc is called upon a selfactivating macro will fire and do the following? When opened it will fire and make the document a mail merge with "DataSource" as the data source. The datasource is a shortcut in the My Docs\M\y Data Sources\"DataSource". Once this is opened the select data tab comes up and we select the tab of data on excel. (If this could be done with the word code that would be great). Something like datasource is My Docs\My DataSources\DataSource and tab "datasourcetab". Then I would have the document openedas a mail merge and either I would have to make a selection for the datasource or it would be opened to it already?? Hope the explanation is understandable Thanks for the Assistance. BOB |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost data source | Mailmerge | |||
Do not change data source name in the main merge document. | Mailmerge | |||
How do I create a data source fo mail merge using word document | Microsoft Word Help | |||
merge document and data source are not merging | Mailmerge | |||
Merge multiple data in one document from same data source | Mailmerge |