Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Getting Word to work with Excel and have it bind with excel without going into the code
Hello,
I have WORD 2003 and Excel 2003 with WINXP Pro and have the following problem. I'm opening a WORD (mail merge) document through code in Excel. I've gotten assistance with the code here and other places and the code works just as it's supposed to in bringing up WORD and selecting the particular datasheet to merge from, all automatically. It does give me issues regarding 0's instead of some proper entires but I'm going to convert them all to text and see if I can get it to work. ' My problem When I start my excel database and try to launch a WORD document with the code I get a warning that the ...... isn't connected. Then I need to go to the code, select relationships and then select the WORD connections that I need. I'm afraid that the people that are going to use this will cause damage to the code or won't be able to get this done. Here is the code I'm using (again thanks to a helper on here) Could someone please look at it and see if I have too many of the wdApp objDoc objWord in it and especially help me with an automated way to set a reference to the Word Object library, if anyone knows this trick in VB. We will all be using Excel 2003 so I shouldn't have any other versions of Excel or Word around. Any help would be appreciated. Thanks BOb Sub OpenWordDocument(WhichLetter As String) 'In order to use this code you must set a reference to the 'Word object library by doing this. In the VB Editor click 'Tools, References. Then search for Microsoft Word n.n Object Library 'where n.n will depend on your version of Word. Dim wdApp As Object Dim objWord As Word.Application Dim objDoc As Word.Document Dim wdDoc As Variant Application.ScreenUpdating = True Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If Set objDoc = objWord.Documents.Open("C:\LettersForms\LETTERS\" & WhichLetter & ".doc") 'actual name went here If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If If WhichLetter = "03CInterview" Or WhichLetter = "03FInterview" Or WhichLetter = "10FAgreement" Or _ WhichLetter = "11FCMP" Or WhichLetter = "12AExhibit" Or WhichLetter = "12BExhibit" Or _ WhichLetter = "13IA" Then Exit Sub objDoc.Activate objWord.Visible = True With objDoc.MailMerge '.OpenDataSource Name:=C:\LettersForms\Full Database.xls, _ 'sqlstatement1:="SELECT * FROM [" Full Database.DataBase & "$]" .OpenDataSource Name:="C:\LettersForms\Full Database.xls", _ sqlstatement1:="SELECT * FROM [DataBase$]" End With Set objDoc = Nothing Set objWord = Nothing |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Getting Word to work with Excel and have it bind with excel without going into the code
You may have to modify the registry as per
http://support.microsoft.com/?kbid=825765 The easiest way to do that for each workstation is to create the necessary entriy on one system, export it to a .reg file, edit with Notepad to cut out everything except the entries you need, then distribute it and ask users to double-click on the .reg. That assumes that admin. lockdowns are not in place to prevent exactly that (in which case you probably have your own mechanism for distributing changes). Here is the code I'm using (again thanks to a helper on here) Could someone please look at it and see if I have too many of the wdApp objDoc objWord in So far, you don't need wdApp or wdDoc. After you have made the connection, are you leaving the user to do the actual merge? Is the Excel workbook the current workbook in Excel? If so, you can use .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ SQLStatement:="SELECT * FROM [DataBase$]" (note that you should really use SQLStatement above, not SQLStatement1 as you have at present, although it doesn't actually make any difference since the statement word uses is SQLStatement & SQLStatement if you see what I mean) If you need to use DDE as discussed elsewhere, try .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ Connection:="Entire Spreadsheet", _ SubType:=wdMergeSubTypeWord2000 it and especially help me with an automated way to set a reference to the Word Object library, Sorry, I don't know how to do that. But you may be able to get away with Dim objWord As Object Dim objDoc As Object (AIUI the references are there to assist with "Intellisense/Autocomplete" and of course so that you can be specific about the types of the objects concerned). Peter Jamieson "LabrGuy Bob R" wrote in message ... Hello, I have WORD 2003 and Excel 2003 with WINXP Pro and have the following problem. I'm opening a WORD (mail merge) document through code in Excel. I've gotten assistance with the code here and other places and the code works just as it's supposed to in bringing up WORD and selecting the particular datasheet to merge from, all automatically. It does give me issues regarding 0's instead of some proper entires but I'm going to convert them all to text and see if I can get it to work. ' My problem When I start my excel database and try to launch a WORD document with the code I get a warning that the ...... isn't connected. Then I need to go to the code, select relationships and then select the WORD connections that I need. I'm afraid that the people that are going to use this will cause damage to the code or won't be able to get this done. Here is the code I'm using (again thanks to a helper on here) Could someone please look at it and see if I have too many of the wdApp objDoc objWord in it and especially help me with an automated way to set a reference to the Word Object library, if anyone knows this trick in VB. We will all be using Excel 2003 so I shouldn't have any other versions of Excel or Word around. Any help would be appreciated. Thanks BOb Sub OpenWordDocument(WhichLetter As String) 'In order to use this code you must set a reference to the 'Word object library by doing this. In the VB Editor click 'Tools, References. Then search for Microsoft Word n.n Object Library 'where n.n will depend on your version of Word. Dim wdApp As Object Dim objWord As Word.Application Dim objDoc As Word.Document Dim wdDoc As Variant Application.ScreenUpdating = True Set objWord = CreateObject("Word.Application") If objWord Is Nothing Then MsgBox "Could not create the Word object" Exit Sub End If Set objDoc = objWord.Documents.Open("C:\LettersForms\LETTERS\" & WhichLetter & ".doc") 'actual name went here If objDoc Is Nothing Then MsgBox "Could not open the specified document" objWord.Quit Set objWord = Nothing Exit Sub End If If WhichLetter = "03CInterview" Or WhichLetter = "03FInterview" Or WhichLetter = "10FAgreement" Or _ WhichLetter = "11FCMP" Or WhichLetter = "12AExhibit" Or WhichLetter = "12BExhibit" Or _ WhichLetter = "13IA" Then Exit Sub objDoc.Activate objWord.Visible = True With objDoc.MailMerge '.OpenDataSource Name:=C:\LettersForms\Full Database.xls, _ 'sqlstatement1:="SELECT * FROM [" Full Database.DataBase & "$]" .OpenDataSource Name:="C:\LettersForms\Full Database.xls", _ sqlstatement1:="SELECT * FROM [DataBase$]" End With Set objDoc = Nothing Set objWord = Nothing |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Getting Word to work with Excel and have it bind with excel without going into the code
Thanks for the help. It does have a lockdown so that would be out. I'm going
to modify the code I'm using to clean it up as you suggested. "Peter Jamieson" wrote in message ... So far, you don't need wdApp or wdDoc. Thanks After you have made the connection, are you leaving the user to do the actual merge? The user can select the record number and must select the view the data. Is the Excel workbook the current workbook in Excel? If so, you can use .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ SQLStatement:="SELECT * FROM [DataBase$]" (note that you should really use SQLStatement above, not SQLStatement1 as you have at present, although it doesn't actually make any difference since the statement word uses is SQLStatement & SQLStatement if you see what I mean) If you need to use DDE as discussed elsewhere, try .OpenDataSource _ Name:=ActiveWorkbook.FullName, _ Connection:="Entire Spreadsheet", _ SubType:=wdMergeSubTypeWord2000 it and especially help me with an automated way to set a reference to the Word Object library, Sorry, I don't know how to do that. But you may be able to get away with Dim objWord As Object Dim objDoc As Object I was thinking that might actually be the case but wasn't sure. Thanks so much for the help and the response. It's greatly appreciated. BOB Reynolds |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My zip code format is lost when merging from Excel to Word... | Microsoft Word Help | |||
Keys don't work in Word/Excel | Microsoft Word Help | |||
When I mail mrge from Excel into Word, it drops 1st 0 in zip code | Mailmerge | |||
How do I get Word to retain Excel formatting, eg zip code, ssn? | Mailmerge | |||
Formatting zip code in mail merge from Excel to Word | Microsoft Word Help |