PDA

View Full Version : transfer data from excel to word



johan4b
10-04-2016, 03:52 AM
Hi guys

I have a question about if it is possible to transfer selectively data to an existing word document with VBA?

I searched the internet but didn't find any information.

I hope you can help me with this

thank you very much

Johan

mana
10-04-2016, 04:31 AM
Option Explicit

Sub test()
Dim wd As Object
Dim doc As Object
Const myDoc = "D:\***\****\test.docx"

Set wd = CreateObject("Word.application")
wd.Visible = True
Set doc = wd.Documents.Open(myDoc)

doc.Range(0, 0).Text = Range("a1").Value


End Sub

johan4b
10-04-2016, 04:42 AM
Hi Mana

Thank you very much for your help, i think i didn't explain well sorry for that.

I have a word document and some lines in this document, i want to fill them up with data from Excel with VBA

Thanks

Johan

mana
10-04-2016, 05:06 AM
>i want to fill them up with data from Excel

I can't undestand.
Would you provide a concrete example?

johan4b
10-04-2016, 05:09 AM
Yes, i have a word document with placeholders and i want this placeholders to fill them up with excel data.

I want a button in excel where i can select this word file and then VBA fill the placeholders in Word with data form Excel

I hope this is possible

johan

mana
10-04-2016, 05:52 AM
you can use word Bookmark.
A1:data
B1:bookmark name



Option Explicit

Sub test()
Dim wd As Object
Dim doc As Object
Dim r As Object
Const myDoc = "D:\***\****\test.docx"

Set wd = CreateObject("Word.application")
wd.Visible = True
Set doc = wd.Documents.Open(myDoc)

Set r = doc.Bookmarks(Range("b1").Value).Range
r.Text = Range("a1").Value
doc.Bookmarks.Add Range("b1").Value, r

End Sub

johan4b
10-04-2016, 06:23 AM
Hi Mana

I have this code to select a certain placeholder <<...>> and select a cell in excel and paste it in word where <<>> is
but i get this error :run time error 242 object required

Private Sub CommandButton1_Click()


Dim wApp As Word.Application
Dim wDoc As Word.Document
Set wApp = CreateObject("Word.Application")
wApp.Visible = True


Set wDoc = Application.GetOpenFilename("Word Files (*.doc*), *.doc*")


With wDoc
.Application.Selection.Find.Text = "<<Invoerveld>>"
.Application.Selection.Find.Execute
Application.Selection = Range("A7")
.Application.Selection.EndOf


.Application.Selection.Find.Text = "<<Invoerveld>>"
.Application.Selection.Find.Execute
Application.Selection = Range("A8")


End With


End Sub

mana
10-04-2016, 07:22 AM
Option Explicit

Sub test()
Dim wApp As Word.Application
Dim myFile
Dim wDoc As Word.Document

myFile = Application.GetOpenFilename("Word Files (*.doc*), *.doc*")

If VarType(myFile) = vbBoolean Then Exit Sub
If Not myFile Like "*.doc*" Then Exit Sub

Set wApp = New Word.Application
wApp.Visible = True
Set wDoc = wApp.Documents.Open(myFile)


With wDoc.Application.Selection
.Find.Text = "<<Invoerveld>>"
If .Find.Execute Then
.Text = Range("A7")
.EndOf wdWord, wdMove
End If

If .Find.Execute Then
.Text = Range("A8")
End If
End With

End Sub