export cells from excel to MS word form document

J

Justin.B

New Member
Joined
Jul 1, 2008
Messages
30
Is there a way to export a cell from excel to a certain field on a form document in microsoft word using VBA???

If anyone could help it would be greatly appreicated.

Thank you all very much!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
I think I created a code to use a button on an excel form but for some reason its not working!!! Please help!!!

Code: 
Private Sub CommandButton1_Click()
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
 
Set wd = wdApp.Documents.Open("C:\JustinB\DOMESTICWTTEMPLATE.doc")
 
wdApp.Visible = True
wd.formfields(1).Result = ActiveSheet.Cells("C12").Value
wd.formfields(2).Result = ActiveSheet.Cells("e12").Value
wd.formfields(3).Result = ActiveSheet.Cells("b13").Value
wd.formfields(4).Result = ActiveSheet.Cells("e21").Value
wd.formfields(5).Result = ActiveSheet.Cells("C22").Value
wd.formfields(6).Result = ActiveSheet.Cells("C23").Value
wd.formfields(7).Result = ActiveSheet.Cells("C24").Value
wd.formfields(8).Result = ActiveSheet.Cells("C25").Value
wd.formfields(9).Result = ActiveSheet.Cells("C26").Value
wd.formfields(10).Result = ActiveSheet.Cells("C27").Value
wd.formfields(11).Result = ActiveSheet.Cells("C28").Value
wd.formfields(12).Result = ActiveSheet.Cells("C29").Value
wd.formfields(13).Result = ActiveSheet.Cells("A37").Value
wd.formfields(14).Result = ActiveSheet.Cells("A39").Value
wd.formfields(15).Result = ActiveSheet.Cells("A41").Value
wd.formfields(16).Result = ActiveSheet.Cells("A43").Value
wd.formfields(21).Result = ActiveSheet.Cells("A45").Value
wd.formfields(17).Result = ActiveSheet.Cells("A48").Value
wd.formfields(18).Result = ActiveSheet.Cells("A50").Value
wd.formfields(19).Result = ActiveSheet.Cells("A52").Value
wd.formfields(20).Result = ActiveSheet.Cells("A54").Value
 
End Sub
 
Upvote 0
That worked for me. Maybe your formfields did not exist or your document. You can use DIR to see if the doc does exist.

You can remove the ActiveSheet parts.

Here is a what I tested using a doc similar to what I created in the other thread but named the fields.
Code: 
Sub test()
  Dim wdApp As Object, wd As Object, ac As 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
   
  Set wd = wdApp.Documents.Open("C:\myfiles\MSWord\Car Information Page.doc")
   
  wdApp.Visible = True
  Set ac = ActiveCell
  With wd
    .FormFields("Brand").Result = Cells(ac.Row, "B")
    .FormFields("Model").Result = Cells(ac.Row, "C")
    .FormFields("Chasis").Result = Cells(ac.Row, "D")
    .FormFields("Engine").Result = Cells(ac.Row, "E")
    .FormFields("Color").Result = Cells(ac.Row, "F")
  End With
  
 Set wd = Nothing
 Set wpapp = Nothing
End Sub
 
Upvote 0
what if I just want to break it up so that a specific random cell corresponds to a field on the word doc form?
 
Upvote 0
Not sure what you mean but I suspect that it is easily done.

One can make the code a bit more tidy by replacing the ac.Row with a Dim and then define the row. For example:
Code: 
Sub test()
  Dim wdApp As Object, wd As Object, ac As long
  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
   
  Set wd = wdApp.Documents.Open("C:\myfiles\MSWord\Car Information Page.doc")
   
  wdApp.Visible = True
  ac = ActiveCell.Row
  With wd
    .FormFields("Brand").Result = Cells(ac, "B")
    .FormFields("Model").Result = Cells(ac, "C")
    .FormFields("Chasis").Result = Cells(ac, "D")
    .FormFields("Engine").Result = Cells(ac, "E")
    .FormFields("Color").Result = Cells(ac, "F")
  End With
  
 Set wd = Nothing
 Set wpapp = Nothing
End Sub
 
Upvote 0
what I meant was lets say cell C15 has information in it, and that information I want to be transported to the formfield("Brand") on the Word Doc. How would I go about doing so?
 
Upvote 0
Cells or Range can be used or even bracket notation. While .Value is not needed, it is good form...
Code: 
.FormFields("Brand").Result = Cells(15, "C").Value
'or
.FormFields("Brand").Result = Range("C15").Value
'or
.FormFields("Brand").Result = [C15].Value
 
Upvote 0
perfect, thank you so much. I added a little bit more to it so here is the final code that I used.

Code: 
Private Sub CommandButton2_Click()
Dim wdApp As Object, wd As Object, ac As Long, ws As Worksheet
Set ws = Worksheets("Wire Template")
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
Set wd = wdApp.Documents.Open("C:\MypathHere\DOMESTICWTTEMPLATE.doc")
wdApp.Visible = True
With wd
        .formfields("AccountName").Result = ws.Range("C12").Value
        .formfields("AccountNumber").Result = ws.Range("e12").Value
        .formfields("Date").Result = ws.Range("b13").Value
        .formfields("Bank").Result = ws.Range("e21").Value
        .formfields("AccName").Result = ws.Range("e22").Value
        .formfields("AccNo").Result = ws.Range("e23").Value
        .formfields("ABA").Result = ws.Range("e24").Value
        .formfields("CC").Result = ws.Range("e25").Value
        .formfields("DDA").Result = ws.Range("e26").Value
        .formfields("Ref").Result = ws.Range("e27").Value
        .formfields("SI").Result = ws.Range("e28").Value
        .formfields("Amnt").Result = ws.Range("e29").Value
        .formfields("s1").Result = ws.Range("A37").Value
        .formfields("s2").Result = ws.Range("A39").Value
        .formfields("s3").Result = ws.Range("A41").Value
        .formfields("s4").Result = ws.Range("A43").Value
        .formfields("s5").Result = ws.Range("A45").Value
        .formfields("bs1").Result = ws.Range("A48").Value
        .formfields("bs2").Result = ws.Range("A50").Value
        .formfields("bs3").Result = ws.Range("A52").Value
        .formfields("bs4").Result = ws.Range("A54").Value
End With
Set wd = Nothing
Set wdApp = Nothing
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Open and print a section of word document as pdf
Replies
6
Views
208
StephenBart
S
S
  • Question
Mail Merge Macro Doesn't Connect Refresh
Replies
1
Views
69
StephenBart
S
B
  • Question
Excel Membership Data to Word Document
Replies
7
Views
241
Beaglebud
B
O
  • Solved
VBA Novice here: Opening a new Word Document from Excel
Replies
2
Views
241
Offroadracer_814
O
D
  • Question
Create empty table (with headings) in Word document after some text
Replies
5
Views
157
dwsteyl
D

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top