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

 

[Norie]

Could you not just use Word mail merge for this with the Excel worksheet as the data source?

I don't see why you need to automate Word.

 

[HappyApple]

Hi,

Thanks for the prompt reply.

The data for this sheet will be being entered on a regular basis (i.e. every time a new delivery arrives in the warehouse). The idea is for a very simple form that the warehouse staff can use to enter data into a spreadsheet, which can then be automatically transferred to Word and printed out and attached to the delivery.

I was hoping for a quicker and more easy process than mail merge for the the staff to use to enter the data and print out the Word doc.

i hope this makes sense.

 

[Norie]

Why wouldn't mail merge be quick enough?

Might take some time to set up but once done there's not much more needed to do other than press a button or two.

 

[HappyApple]

Hi,

My idea was that the data would be entered in the form, then use one button click to save the data to the worksheet and export the data to the Word Document and then simply click Print when Word opens. That was how easy i was thinking.

is it not possible to write this into the existing code i have?

 

[Norie]

Well the reason it's only using the 2nd row is because that's what you are telling it to do.

The row number 2 is hard-coded here.

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")

 

[HappyApple]

yes, i left it like that because i didn't know what to do next. i attempted to code in a count, but it didn't work.

Forgive my hideous scripting

For lngindex = 1 To wd.formfields.Count
        wd.formfields("Code").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("Date").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("Time").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("Company").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("Driver").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("Supplier").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("PO").Result = ws.Cells(lngindex, 1).Value
Next
For lngindex = 1 To wd.formfields.Count
        wd.formfields("Operator").Result = ws.Cells(lngindex, 1).Value
Next

 

[Norie]

Why not use iRow instead of 2?

 

[HappyApple]

Like this?

I keep getting a Run Time error.

"Method 'Range' of object '_Worksheet' failed"

   .formfields("Code").Result = ws.Range("A", iRow).Value
        .formfields("Date").Result = ws.Range("B", iRow).Value
        .formfields("Time").Result = ws.Range("C", iRow).Value
        .formfields("Company").Result = ws.Range("D", iRow).Value
        .formfields("Customer").Result = ws.Range("E", iRow).Value
        .formfields("GOA").Result = ws.Range("F", iRow).Value
        .formfields("Operator").Result = ws.Range("G", iRow).Value

 

[Norie]

Well use Cells just like you did in the previous code.

Or Range like this.

Range("A" & iRow)