Exporting Excel Forms to Word

[HappyApple]

Hello,

I have spent a long while trying to figure out what i'm sure is a very simple problem. I have searched the archives and found nothing to specifically address my question.

Basically I am trying to export the data entered into an Excel form into the fields of a Word document template, so that i can print it according to a particular design.

I have managed to write the code successfully enough that the data is transferrred across to the right fields in Word, but I can't seem to work out how to transfer the subsequent rows. It is currently only exporting the second row (after the header).

Could anyone possibly look at my code and tell me how i can export the new values every time they are entered in the form and not just the first line? Thank you in advance for your help.

James

Private Sub CancelButton_Click()
    Unload Me
End Sub
Private Sub Save1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("RA")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
  .End(xlUp).Offset(1, 0).Row
 
'validate data
If Me.DateBox1.Value = "" Then
        MsgBox "Please enter the date.", vbExclamation, "Date"
        Me.DateBox1.SetFocus
        Exit Sub
End If
If Me.TimeBox1.Value = "" Then
        MsgBox "Please enter the time.", vbExclamation, "Time"
        Me.DateBox1.SetFocus
        Exit Sub
End If
If Me.TransportBox1.Value = "" Then
        MsgBox "Please enter the Transport Company.", vbExclamation, "Transport Company"
        Me.DateBox1.SetFocus
        Exit Sub
End If
If Me.CustomerBox1.Value = "" Then
        MsgBox "Please enter the Customer name.", vbExclamation, "Customer Name"
        Me.DateBox1.SetFocus
        Exit Sub
End If
If Me.RABox1.Value = "" Then
        MsgBox "Please enter the RA No.", vbExclamation, "Return Authorisation No."
        Me.DateBox1.SetFocus
        Exit Sub
End If
If Me.OperatorBox1.Value = "" Then
        MsgBox "Please enter your name.", vbExclamation, "Operator"
        Me.DateBox1.SetFocus
        Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.DateBox1.Value
ws.Cells(iRow, 3).Value = Me.TimeBox1.Value
ws.Cells(iRow, 4).Value = Me.TransportBox1.Value
ws.Cells(iRow, 5).Value = Me.CustomerBox1.Value
ws.Cells(iRow, 6).Value = Me.RABox1.Value
ws.Cells(iRow, 7).Value = Me.OperatorBox1.Value
'clear the data
Me.DateBox1.Value = ""
Me.TimeBox1.Value = ""
Me.TransportBox1.Value = ""
Me.CustomerBox1.Value = ""
Me.RABox1.Value = ""
Me.OperatorBox1.Value = ""
Me.DateBox1.SetFocus
'export the data to Word
Dim wdApp As Object, wd As Object, ac As Long
Set ws = Worksheets("RA")
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:\Documents and Settings\James\Desktop\RALabel.doc")
wdApp.Visible = True
With wd
        .formfields("Code").Result = ws.Range("A2").Value
        .formfields("Date").Result = ws.Range("B2").Value
        .formfields("Time").Result = ws.Range("C2").Value
        .formfields("Company").Result = ws.Range("D2").Value
        .formfields("Customer").Result = ws.Range("E2").Value
        .formfields("RA").Result = ws.Range("F2").Value
        .formfields("Operator").Result = ws.Range("G2").Value
 
End With
Set wd = Nothing
Set wdApp = Nothing
End Sub

 

[NdNoviceHlp]

I'm guessing that you will have to use...

.formfields("Operator").Result = Cstr(ws.Range("A" & iRow))

HTH. Dave

 

[HappyApple]

Hi Norie and Dave,

Thank you SO much for your help. finally after a bit more fiddling got it working (there was also issues with the fields in Word).

in the end i used the following code:

.formfields("Code").Result = ws.Cells(iRow, "A").Value

works just as i want.

so thanks again!