|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to transfer multiple excel cells into specific place in word document by formulas
I need to transfer data stored in excel file (column A,B,C etc.) into a word document (template) in specific places. I am using a code written in VBA (mentioned on forum Word 5.23.2009 by Bird FAT):
Option Explicit Sub ExportFinalColumnToWord() On Error GoTo errorHandler Dim wdApp As Word.Application Dim myDoc As Word.Document Dim mywdRange As Word.Range Dim MyColumnA As Excel.Range Dim MyColumnB As Excel.Range Dim MyColumnC As Excel.Range Dim MyColumnE As Excel.Range On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err.Number <> 0 Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 ' You need to change the path details here - ' This will create a new document based on your original. Set myDoc = wdApp.Documents.Add(Template:="C:\Tempo\ExWd.doc") ' Here you need to change the following: ' MySheet - this is the tab name of the sheet in the spreadsheet ' A1,B1,C1,E1 - these are the columns where the data is found Set MyColumnA = Sheets("MySheet").Range("A1").End(xlDown) Set MyColumnB = Sheets("MySheet").Range("B1").End(xlDown) Set MyColumnC = Sheets("MySheet").Range("C1").End(xlDown) Set MyColumnE = Sheets("MySheet").Range("E1").End(xlDown) ' This is where you get the information pasted into your document. ' Below there are four references to bookmarks - inside the (""). ' You will need to change the names to those of YOUR bookmarks. ' If you want data entered into more than one place, you need to ' create a new line, as below, and change the bookmark name, ' choosing the correct reference at the end 'MyColumnX' ' For example to have the information from Column A in another place, ' you would add the line in green that is below this section. With myDoc.Bookmarks .Item("bmMyColumnA").Range.InsertAfter MyColumnA .Item("bmMyColumnB").Range.InsertAfter MyColumnB .Item("bmMyColumnC").Range.InsertAfter MyColumnC .Item("bmMyColumnE").Range.InsertAfter MyColumnE ' .Item("bmMyColumnA_2").Range.InsertAfter MyColumnA End With ' At this point the Word Application will appear in your taskbar wdApp.Visible = True Exit Sub errorHandler: wdApp.Quit Set wdApp = Nothing Set myDoc = Nothing Set mywdRange = Nothing End Sub Everytime I run macro, I get new document (it is fine) but always with the same value which is text in last cell from my columns A,B,C. It looks like problem to read range defined in excel. Thanks for help Last edited by anton; 12-15-2011 at 05:49 AM. |
#2
|
|||
|
|||
Finally I found simple solution by using Merge Mail feature of Word application which allowed me to copy defined data from Excell worksheet to specific fields in word doc.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copying Multiple tables from excel into a single word document | dineshtgs | Word Tables | 1 | 04-07-2011 01:27 AM |
Setting focus to specific word document from UserForm | SaneMan | Word VBA | 5 | 04-01-2011 03:11 PM |
How to transfer Word 2003 Tables to Excel? | steeleye | Word Tables | 1 | 07-10-2009 04:18 PM |
How to transfer multiple cells from excel to word by formulas | justziggy | Word | 5 | 05-23-2009 02:33 AM |
format cells to automatically place quotes around text | dirtleg | Excel | 1 | 09-16-2008 01:52 PM |