send XL text to word
Hi All
I want to send cell data from Excel to Word as text, in a specific place in the word document, same place each month. I thought bookmarks in Word would do the trick but they get overwritten and need to be reset time and again. I found this post online which the author said worked nicely using variables. I can not get it to work as I do not know how to set Variables in Word. Does anyone know how to get this code working? Or an alternative method if this is all wrong? Thanks in advance. Chad Sub PushToWord() Dim objWord As New Word.Application Dim doc As Word.Document Dim bkmk As Word.Bookmark sWdFileName = Application.GetOpenFilename(, , , , False) Set doc = objWord.Documents.Add(sWdFileName) 'Is there a way to have word open say C:\Test.doc instead of above With doc ..Variables("Test1").Value = Range("Test1").Value ..Variables("Test2").Value = Range("Test2").Value ..Variables("Test3").Value = Range("Test3").Value ..Range.Fields.Update End With objWord.Visible = True End Sub |
send XL text to word
Hi Chad,
Suggest that you create a Template file for the variables. Search Help in word for Create Template. That way you do not mess up or loose the variables. A couple of tips:- When creating the template, if at any time you get a message asking if you want to save changes to Normal.dot then answer NO. (You have probably done something wrong). When opening a template (.dot) file for editing, do not double click from Windows Explorer. Open Word first and then open the template from within Word. (Double clicking a template opens a new Word document using the template.) When you have the template open, insert the fields as follows:- Position cursor where you want the field. Select Insert - Field - DocVariable (DO NOT double click DocVariable, just select it. In the New name field, give it a name. (The code below uses Test1, Test2 and Test3 to match named ranges in the Excel worksheet.) Repeat above for additional fields. Press Alt+F9 to toggle the display of the field codes on and off. You will see:- { DOCVARIABLE [Test1] } To set the format of the field:- (Originally from Doug Robbins - Word MVP) Add a \* Charformat switch inside the closing } of the Docvariable field and then apply the desired format to the D of Docvariable. (If there is a \* mergeformat switch there, you can delete it, or change it to \* charformat.) Save as a Word Template. (.dot). When you select Template format in the Save Dialog box it will default to a folder that holds templates. You can change the path. I usually put them in the same folder as the Excel project.) Now the following code example will create a new document based on the template created. An input box requests a File name to save the new document. (Watch the status bar for this; the Input box is not automatically on top.) Have included a couple of commented out lines of code just for info. In the VBA editor, you will need to select Tools - References - Microsoft Word nn.0 Object Library where nn.0 is the version number of your Office. (10.0 is 2002, 11.0 is 2003 and 12.0 is 2007) If using xl2007 then you will have to open word and record a macro to save the document to get the correct syntax. Sub CreateWordDoc() Dim objWord As Object Dim strPathFileName As String Dim strSaveFileName As String Set objWord = CreateObject("Word.Application") 'Save required path and filename of template file. 'This code uses current directory. 'Can edit path to a string representing your required path strPathFileName = CurDir & "\" & "My Template.dot" 'Template file With objWord 'Create a new Word document based on the template .Documents.Add template:=strPathFileName .Visible = True .ActiveDocument.Variables("Test1").Value = Range("Test1").Value .ActiveDocument.Variables("Test2").Value = Range("Test2").Value .ActiveDocument.Variables("Test3").Value = Range("Test3").Value .ActiveDocument.Fields.Update 'Turns off Show Fields in the Word Document. If .ActiveWindow.View.ShowFieldCodes = True Then .ActiveWindow.View.ShowFieldCodes = False End If strSaveFileName = Application.InputBox _ (Prompt:="Enter file name for saving" _ & vbCrLf & "Do not enter file extension", _ Title:="Get file name", _ Default:="My Test Document", _ Type:=2) 'This code uses current directory. 'Can edit path to a string representing your required path strSaveFileName = CurDir & "\" & strSaveFileName 'Following code to save document to specified name .ActiveDocument.SaveAs Filename:=strSaveFileName & ".doc" 'Following code to print document if required '.ActiveDocument.PrintOut 'Following code to save without saving .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Close word .Quit End With 'Cleanup Set objWord = Nothing End Sub -- Regards, OssieMac |
send XL text to word
Hi OssieMac
Thanks for your extensive reply. I will have a good look at it and reply regarding how I get on. I really appreciate your time and am so greatful for your time and advice. Take it easy. Chad |
send XL text to word
Hi Ossie
Can I just say a very heartfelt thankyou to you. I worked so long on this one and your elegant answer was perfect. This is an excellent method for shifting data into word. All the very best. Chad |
All times are GMT +1. The time now is 01:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com