Write data from Word form to next available Excel row

[Domski]

Hi guys,

This is my first attempt at writing code in Word so please bear with me.

I've been asked to add a button to an existing Word form that when clicked logs some of the details to an Excel spreadsheet on the next available row.

I've got this far which works fine to write to specific cells:


Private Sub CommandButton1_Click()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open FileName:="H:\TestLog.xls"
With objExcel.Sheets("Sheet1")
.Range("A2").Value = ThisDocument.FormFields("Text11").Result
.Range("B2").Value = ThisDocument.FormFields("Dropdown1").Result
.Range("C2").Value = ThisDocument.FormFields("Text20").Result
.Range("D2").Value = ThisDocument.FormFields("Text16").Result
.Range("E2").Value = ThisDocument.FormFields("Text8").Result
End With
objExcel.ActiveWorkBook.Save
objExcel.Application.Quit
Set objExcel = Nothing
End Sub

In Excel I would normally use code like this to identify the next free row on a sheet:


writeRow = objExcel.sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

But when I add the code into Word I get a Compile error: Variable not defined referring to the xlUp part of the code.

Can anyone suggest how I can look at the sheet and work out which row is the next one available.

I should point out this is a cross post with MrExcel but because I'm new round here it won't let me add the link.

Any help would be greatly appreciated as I have spent a long time searching for an answer to this to no avail.

Dom

[Domski]

Okay, progress has been made.

I found I needed to install the Microsoft Excel 11.0 Object Library which meant I could use Excel VBA code in Word. I guess I thought this would be standard.

The code I now have is:


Private Sub CommandButton1_Click()
Dim objExcel As Object, writeRow As Long
Set objExcel = CreateObject("Excel.Application")
With objExcel
.Workbooks.Open FileName:="H:\TestLog.xls"
writeRow = .Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
With .Sheets("Sheet1")
.Range("A" & writeRow).Value = ThisDocument.FormFields("Text11").Result
.Range("B" & writeRow).Value = ThisDocument.FormFields("Dropdown1").Result
.Range("C" & writeRow).Value = ThisDocument.FormFields("Text20").Result
.Range("D" & writeRow).Value = ThisDocument.FormFields("Text16").Result
.Range("E" & writeRow).Value = ThisDocument.FormFields("Text8").Result
End With
.Application.DisplayAlerts = False
.Workbooks("TestLog.xls").Save
.Application.DisplayAlerts = True
.Workbooks("TestLog.xls").Close
.Quit

End With
Set objExcel = Nothing
End Sub

I have now found that if I run the code more than once I get an error:

Run-time error '1004':

Method 'Rows' of object '_Global' failed

Upon investigation it seems that the Excel.exe process is not shutting down until I quit Word and when I run the code a second time a second instance of Excel can be seem in the Windows Task Manager Processes list.

Can anyone suggest where I might be going wrong?

Thanks in advance,

Dom

[Domski]

Anyone?

Someone at work has suggested that I may need to set the file and sheet references to Nothing also but as I haven't set these as objects I'm not sure whether this is the case or even how to go about it.

I have set the code up so that the document is saved and closed not so it's very unlikely that someone would run the code twice but I would still like to know why this is happening.

Dom

[TonyJollans]

You should use .Rows - to make sure that the Excel Rows is used and not the Word Rows

[Domski]

Okay after no small amount of cursing and threatening to throw my PC out of the window I have finally got it working:


Sub Log_and_Save()
Dim XLapp As Object, XLbook As Object, XLsheet As Object, Wbook As String, lastRow As Long, fileName As String, myRange As Range, myRef As String, appOpen As Boolean

On Error GoTo ErrorHandler

' Set application, workbook and worksheet objects
Wbook = "L:\EMPADMIN\EVERYONE\Service Support\Customer Services\Reporting Request Forms\Reporting Log.xls"
appOpen = True

Set XLapp = GetObject(, "Excel.Application")
Set XLbook = XLapp.Workbooks.Open(Wbook)
Set XLsheet = XLbook.Worksheets("Sheet1")

' Pick up unique reference from form
myRef = ThisDocument.FormFields("Text21").Result

With XLsheet

' Find last completed row on reporting log
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

' write information to reporting log
.Range("A" & lastRow + 1).Value = ThisDocument.FormFields("Text21").Result
.Range("B" & lastRow + 1).Value = ThisDocument.FormFields("Text11").Result
.Range("C" & lastRow + 1).Value = ThisDocument.FormFields("Dropdown1").Result
.Range("D" & lastRow + 1).Value = ThisDocument.FormFields("Text20").Result
.Range("E" & lastRow + 1).Value = ThisDocument.FormFields("Text16").Result
.Range("F" & lastRow + 1).Value = ThisDocument.FormFields("Text8").Result

End With

' Close and save workbook and clear objects

Set XLsheet = Nothing
XLbook.Close SaveChanges:=True
Set XLbook = Nothing
If appOpen = False Then XLapp.Quit
Set XLapp = Nothing

' Save and close document
fileName = myRef & ".doc"

ThisDocument.SaveAs "L:\EMPADMIN\EVERYONE\Service Support\Customer Services\Reporting Request Forms\Report Requests\" & fileName

MsgBox "Report request logged and saved", vbInformation

ThisDocument.Close

' Error Handler
ErrorHandler:
If Err.Number = 429 Then
'Excel is not running, open Excel with CreateObject
Set XLapp = CreateObject("Excel.Application")
appOpen = False
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
End If

End Sub


Hopefully it'll save someone else the time and effort I've had to expend.

Laters,

Dom

[Thunderbuck]

Hi Dom,

I've got a similar project underway, though I've taken a different approach.

Like you, I want to automate the collection of data from a Word form into an Excel spreadsheet, but instead of doing it from the Word document, I'm building my spreadsheet so that I can select the Word form (from a File|Open dialog).

However, your code definitely points me in the right direction for actually extracting the data from the form. Thanks!