Automate Word Form To Excel

[Domski]

Hi guys,

Someone has asked me to look at adding a bit of automation to a Word form and add some functionality to it to transfer some of the data on it to an Excel file. Basically I don't have a clue where to start so was hoping for a bit of advice.

The form currently is made up of text and fields from the Forms toolbar and comprises a few drop downs, some checkboxes and a bunch of text form fields.

As a minimum what I have been asked to do is when the form is opened by specific individuals a button is available that when clicked will transfer the data from 5 of the text form fields to the next available line on an existing Excel spreadsheet.

I guess my questions are:

• How straightforward is this to achieve
• Can it be done using Form Fields or do I need to change to items from the Control Toolbox
• Are there any resources out there that can point me in the right direction as my knowledge of Word VBA is almost non-existent and having searched I can't really find anything that looks like it does the same as what I'm after

Any suggestions appreciated.

Dom

 

[Domski]

I've been having a go and have managed to Word to transfer text from a Text Box to an existing spreadsheet:

Private Sub CommandButton1_Click()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open FileName:="H:\TestLog.xls"
objExcel.sheets("Sheet1").Range("A1").Value = TextBox1.Text
objExcel.ActiveWorkBook.Save
objExcel.Application.Quit
End Sub

I've also sussed that I can transfer data from a form field using:

objExcel.sheets("Sheet1").Range("A1").Value = ThisDocument.FormFields("Text1").Result

The only things I can't seem to get it to do is to specifiy a row to write the info to by using something like:

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

I get a Compile error: Variable not defined referring to the xlUp part of the code.

Any thoughts?

Dom

 

[schielrn]

Maybe in Word VBA under Tools-->References you need to check the Microsoft Excel Library Reference?

Hope that helps.

 

[Domski]

Okay, progress has been made.

You were right, 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

 

[schielrn]

Maybe you need to also set the sheet and workbook to nothing also? I had to use this in my code for something:

'Set oXLSheet = Nothing             'Disconnect from Excel (let the user take over)
'oXLBook.Close SaveChanges:= False  'Close (and disconnect from) the Workbook without saving
'Set oXLBook = Nothing
'oXLApp.Quit                        'Close (and disconnect from) Excel Application
'Set oXLApp = Nothing

Not sure if this will solve the problem or not. Hope that helps.

 

[Domski]

Back again. Been doing a bit of research and I think where I'm going wrong is creating an application object in the first place when I only need to create a sheet object. Unfortunately our network is down this afternoon so can't get at my files to test it out.

Will let you know how things go.

Dom

 

[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.

My next problem is getting Word to check the log to ensure the form is only logged once but I think I'll start another thread for that one.

Laters,

Dom