 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / April 2007Data 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 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 > Good Day, > I have numerous letters that use an Excel spreadsheet as the data source [quoted text clipped - 24 lines] > Hope the explanation is understandable Thanks for the Assistance. > BOB 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 > 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" [quoted text clipped - 73 lines] >> Hope the explanation is understandable Thanks for the Assistance. >> BOB 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 > 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 [quoted text clipped - 82 lines] >>> Hope the explanation is understandable Thanks for the Assistance. >>> BOB 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 > 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 - [quoted text clipped - 103 lines] >>>> Hope the explanation is understandable Thanks for the Assistance. >>>> BOB > 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 _ [quoted text clipped - 6 lines] > "$"]" > 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 > Peter, Thank you so much. > [quoted text clipped - 127 lines] >>>>> Hope the explanation is understandable Thanks for the Assistance. >>>>> BOB Thank you >> 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 _ [quoted text clipped - 15 lines] > > 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, > [quoted text clipped - 8 lines] > 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 > [quoted text clipped - 129 lines] >>>>>> Hope the explanation is understandable Thanks for the Assistance. >>>>>> BOB Given what you say, I suggest OpenDataSource _ Name:="C:\LettersForms\Full Database.xls", _ sqlstatement1:="SELECT * FROM [DataBase$]" End With Peter Jamieson > Thank you >>> We're going to have the 3 computers we have with 2002 still on it [quoted text clipped - 200 lines] >>>>>>> Hope the explanation is understandable Thanks for the Assistance. >>>>>>> BOB 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 > 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" [quoted text clipped - 73 lines] >> Hope the explanation is understandable Thanks for the Assistance. >> BOB 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 >I got the following at the beginning of this code > [quoted text clipped - 82 lines] >>> Hope the explanation is understandable Thanks for the Assistance. >>> BOB 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 > OPen the module & macro in Excel VBA. > [quoted text clipped - 98 lines] >>>> Hope the explanation is understandable Thanks for the Assistance. >>>> BOB
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.