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!
 
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

J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
520
Logit
Logit
A
  • Solved
Automate data export from multiple word tables into excel
Replies
8
Views
461
NdNoviceHlp
NdNoviceHlp
S
  • Solved
Comparison between two excel tabs
Replies
2
Views
108
SJord
S
M
  • Question Question
Data Transfer from Excel to PowerPoint and getting PDF file of PPT
Replies
1
Views
330
Sequoyah
Sequoyah
D
  • Question Question
How to embed a non-breaking space in Excel data being used for a mail merge in Word.
2
Replies
12
Views
150
HighAndWilder
H

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