Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-15-2011, 12:31 AM
anton anton is offline Windows XP Office 2003
Novice
 
Join Date: Dec 2011
Posts: 2
anton is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 12-20-2011, 03:56 AM
anton anton is offline Windows XP Office 2003
Novice
 
Join Date: Dec 2011
Posts: 2
anton is on a distinguished road
Default

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.
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying Multiple tables from excel into a single word document dineshtgs Word 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 8 05-23-2009 02:33 AM
format cells to automatically place quotes around text dirtleg Excel 2 12-06-2008 08:44 AM


All times are GMT -7. The time now is 04:36 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft