HappyApple
New Member
- Joined
- Mar 16, 2009
- Messages
- 6
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
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
Code:
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