Word userform textbox to new line in excel

[davidboutche]

I have a word template that opens a series of userforms and processes that information in various ways

What I would like is a piece of code that takes the userform textbox contents, opens a specified excel workbook and inserts that information into the next free row.

UPDATE:

This is what I've managed to find and get working so far:

Sub CreateNewExcelWB()
' to test this code, paste it into a Word module
' add a reference to the Excel-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or
Set xlWB = xlApp.Workbooks.Open("V:\pndbook.xls")
' open an existing workbook
' example excel operations



With xlWB.activesheet
Range("A1").Value = UserForm1.officerbox.Text
End With
'xlWB.Close False ' close the workbook without saving
'xlApp.Quit ' close the Excel application



Set xlWB = Nothing
Set xlApp = Nothing
End Sub

What I need it to do now is insert a line in excel before copying the text across OR have it copy the text onto the next free line. I will eventually get it to copy more than one textbox across

[lucas]

David, it doesn't become completely clear what you are doing because you have commented code at the bottom of the page that shows closing the excel file without saving changes but maybe this will get you headed in the right direction.

Private Sub CommandButton1_Click()
' to test this code, paste it into a Word module
' add a reference to the Excel-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim LastRow As Object
Dim ws As Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or
Set xlWB = xlApp.Workbooks.Open("C:\Temp\pndbook.xls")
' open an existing workbook
' example excel operations



With xlWB.activesheet

Set ws = ActiveWorkbook.Worksheets("Sheet1")
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
End With
'xlWB.Close False ' close the workbook without saving
'xlApp.Quit ' close the Excel application



Set xlWB = Nothing
Set xlApp = Nothing
Unload Me
End Sub

[davidboutche]

:thumb That works really well with the below changes.

I've kept it as part of a module as I'm not sure yet which form it will be called from. I assume that is ok.

Sub UpdateExcelWB()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim LastRow As Object
Dim ws As Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
' Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or
Set xlWB = xlApp.Workbooks.Open("V:\pndbook.xls")
' open an existing workbook
' example excel operations



With xlWB.activesheet

Set ws = ActiveWorkbook.Worksheets("Sheet1")
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = UserForm1.officerbox.Text
LastRow.Offset(1, 1).Value = UserForm1.offencelocationCombo_Box.Text
LastRow.Offset(1, 2).Value = UserForm1.PNDbox.Text
LastRow.Offset(1, 3).Value = UserForm1.issuedatebox.Text
LastRow.Offset(1, 4).Value = UserForm1.offenceCombo_Box.Text
End With
xlWB.Close True 'Closes the workbook
xlApp.Quit ' close the Excel application



Set xlWB = Nothing
Set xlApp = Nothing

End Sub


Many thanks again.

[lucas]

It depends David. Usually it would be better to call it from the userform but you can also use it as a kind of function the way you have it and it can be called from several userforms.....if needed.

[lucas]

One additional note. This line:
Set LastRow = ws.Range("a65536").End(xlUp)
is fine for excel 2003 but it won't cover all the rows in 2007....

[davidboutche]

That's fine. The organisation this works for has mostly Office 2000 with only limited machines on 2003.

I'm assuming "a65536" stand for the maximum row? If that is the case, I will probably just change the file name each year.