I would appreciate it if there is any code could transfer the data from EXCEL to WORD's column. Says, I have a person's name "Jackson" in cell "A1" of my EXCEL file. In the WORD's file, there is a few column setup. Now, I want the code to link/transfer the name "Jackson" antomatically to a WORD's file. So that each of the alphabet "Jackson" will be placed in each column of the WORD's file. Great thanks.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Transfer excel data to word's column
- Thread starter DDLY
- Start date
Colo
MrExcel MVP,
- Joined
- Mar 20, 2002
- Messages
- 1,659
- Office Version
- 2016
- 2003 or older
- Platform
- Windows
DDLY said:
Hi DDLY,
Hope this code helps you.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
PasteToWord Target.Value
End Sub
Private Sub PasteToWord(ByVal strSeq As String)
'Place this code in a worksheet module
Dim appWrd As Object
Dim objDoc As Object
'Excecute Word
Set appWrd = CreateObject("Word.Application")
' appWrd.Visible = True
'Open sample.doc
On Error Resume Next
Set objDoc = appWrd.Documents.Open("C:\sample.doc")
On Error GoTo 0
'In case the word file is NOT found.
If objDoc Is Nothing Then
MsgBox "Word file is not found."
appWrd.Quit
Set appWrd = Nothing
Exit Sub
End If
'Word Macro
appWrd.Selection.TypeParagraph
objDoc.Save
.Quit
Set appWrd = Nothing
End Sub
Upvote
0
Colo, I am sorry for misleading you. What I mean & expect is illustrated in the worksheet below:
| ExplainToColo.xls | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 1 | |||||||||||||||||||
| 2 | IN MICROSOFT EXCEL | IN MICROSOFT WORD | |||||||||||||||||
| 3 | $ | ||||||||||||||||||
| 4 | Sales | 1,000 | (Data) | Profit | $ | 9 | 8 | 0 | |||||||||||
| 5 | Cost | 20 | (Data) | ||||||||||||||||
| 6 | Profit | 980 | (Result) | ||||||||||||||||
| 7 | |||||||||||||||||||
| 8 | |||||||||||||||||||
| 9 | What I expect is that the result ($980) shown in cell (C6) of Microsoft EXCEL will | ||||||||||||||||||
| 10 | automatically be transferred to the preset columns (one column for one figure) in | ||||||||||||||||||
| 11 | Microsoft WORDS through vba code. | ||||||||||||||||||
| 12 | |||||||||||||||||||
Sheet1 | |||||||||||||||||||
Upvote
0
Colo
MrExcel MVP,
- Joined
- Mar 20, 2002
- Messages
- 1,659
- Office Version
- 2016
- 2003 or older
- Platform
- Windows
Okay, DDLY!
So you need to prepare something in your word document.
Please note, sorry I do not have a Microsoft Word application English version, so real command or menu name must be different from what I wrote.
And I'm NOT a WORD EXPERT like Dreamboat
, so if there is more efficient way, please let me know.
1. In Ms Word application, insert BOOKMARKS to the location where you would like to send values from Excel.
Word Main menu > Insert > Bookmark. (Press [Alt]+, Press [K])
Now you have 7 boxes in the Word, so insert bookmark as Num1 to Num7.
These names are used in Excel VBA, you can change these names as you want.
2. Place this code in the Ms Excel Worksheet module.
So you need to prepare something in your word document.
Please note, sorry I do not have a Microsoft Word application English version, so real command or menu name must be different from what I wrote.
And I'm NOT a WORD EXPERT like Dreamboat
, so if there is more efficient way, please let me know.1. In Ms Word application, insert BOOKMARKS to the location where you would like to send values from Excel.
Word Main menu > Insert > Bookmark. (Press [Alt]+, Press [K])
Now you have 7 boxes in the Word, so insert bookmark as Num1 to Num7.
These names are used in Excel VBA, you can change these names as you want.
2. Place this code in the Ms Excel Worksheet module.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const strDocPath As String = "c:\test.doc" 'Full path of a word document
Dim appWord As Object ' Word.Application
Dim objDoc As Object ' Word.Document
Dim lngCnt As Long 'Counter for looping
Dim strTemp As String
If Target.Address(0, 0) <> "C6" Then Exit Sub 'Change this address for the Profit value
Application.EnableEvents = False
Set appWord = CreateObject("Word.Application")
Set objDoc = appWord.Documents.Add(strDocPath)
' appWord.Visible = True
If 7 - Len(Target.Value) <> 0 Then
For lngCnt = 1 To 7 - Len(Target.Value)
strTemp = strTemp & Chr(&H20)
Next
End If
strTemp = strTemp & Target.Value
For lngCnt = 1 To 7
objDoc.Bookmarks("Num" & lngCnt).Range.Delete Unit:=1, Count:=1
objDoc.Bookmarks("Num" & lngCnt).Range.InsertAfter Mid(strTemp, lngCnt, 1)
Next
objDoc.SaveAs strDocPath 'Save a word document
objDoc.Close 'Close a word document
appWord.Quit 'Quit Word Application
Set appWord = Nothing 'Cleaning an object variable
Set objDoc = Nothing 'Cleaning an object variable
Application.EnableEvents = True
End Sub
Upvote
0
Colo,
That’s excellent. A virtual cookie for you.
I couldn’t get it to works as a sheet event but after changing the macro to a sub, removing the references to a target value and setting a reference to cell C6, the macro worked like a charm.
The “tricky” part is setting up the bookmarks in Word to input the data in the right spots.
Regards,
Mike
That’s excellent. A virtual cookie for you.
I couldn’t get it to works as a sheet event but after changing the macro to a sub, removing the references to a target value and setting a reference to cell C6, the macro worked like a charm.
The “tricky” part is setting up the bookmarks in Word to input the data in the right spots.
Regards,
Mike
Upvote
0
Colo
MrExcel MVP,
- Joined
- Mar 20, 2002
- Messages
- 1,659
- Office Version
- 2016
- 2003 or older
- Platform
- Windows
Hey Mike, thanks for your comment and testing my code!
Wow what a yummy cookie this is!
hmm, i'm not sure why it doesn't work...
Try type this code in the immideate window.
Wow what a yummy cookie this is!
Ekim said:
hmm, i'm not sure why it doesn't work...
Try type this code in the immideate window.
Code:
Application.EnableEvent = TrueYes, that's it.Ekim said:
Upvote
0
TRANSFER DATA FROM EXCEL TO WORD
Hi All,
As you seem to be expert now in the domain may be you could help me)
I'm trying to transfer date from word to an existing word file.
I manage to create a VBA that opens the file and transfer data from cell A1 to Word but always in a newly created document not in my file.
Can anyone re-edit my code so that the A1 actuly copy in my opened word document from mt C drive. And stops generating a new document.
Many many thanks.
Kev
Sub ExcelToWord()
Dim oWd As Object
Dim wdapp As Object
Set oWd = GetObject("C:\ExistingDocument.doc")
oWd.Application.Visible = True
Set wdapp = Word.Application
wdapp.Visible = True
wdapp.Documents.Add
wdapp.Selection.TypeText Text:=Range("A1").Text
wdapp.Selection.TypeParagraph
Set wdapp = Nothing
Set oWd = Nothing
End Sub
Hi All,
As you seem to be expert now in the domain may be you could help me
)I'm trying to transfer date from word to an existing word file.
I manage to create a VBA that opens the file and transfer data from cell A1 to Word but always in a newly created document not in my file.
Can anyone re-edit my code so that the A1 actuly copy in my opened word document from mt C drive. And stops generating a new document.
Many many thanks.
Kev
Sub ExcelToWord()
Dim oWd As Object
Dim wdapp As Object
Set oWd = GetObject("C:\ExistingDocument.doc")
oWd.Application.Visible = True
Set wdapp = Word.Application
wdapp.Visible = True
wdapp.Documents.Add
wdapp.Selection.TypeText Text:=Range("A1").Text
wdapp.Selection.TypeParagraph
Set wdapp = Nothing
Set oWd = Nothing
End Sub
Upvote
0
Similar threads
- Solved
- Solved
