Discussion on:

10 steps to transferring Word form data to an Excel sheet

How does Excel know what PhoneList$ is and where to put the rows?

Thanks for sharing.

Here is how I needed to change your code to fit my needs, but when I hit tab at the last FormField to start the macro, I get a syntax error in the "INSERT INTO" section, and who knows what will happen even after I get that syntax message fixed. Can anyone help with this code?

Sub TransferToExcel()
'Transfer a single record from the form fields to an Excel workbook.
Dim doc As Document
Dim strStoreNumber As String
Dim strSurveyorName As String
Dim strSurveyDate
Dim strStoreLocation
Dim strContactPhone
Dim strCableTestedValidated
Dim strNumberGoodLines
Dim strNumberBadLines
Dim strSQL As String
Dim cnn As ADODB.Connection
'Get data.
Set doc = ThisDocument
On Error GoTo ErrHandler
strStoreNumber = Chr(39) & doc.FormFields("StoreNumber").Result & Chr(39)
strPhone = Chr(39) & doc.FormFields("SurveyorName").Result & Chr(39)
strSurveyDate = Chr(39) & doc.FormFields("SurveyDate").Result & Chr(39)
strStoreLocation = Chr(39) & doc.FormFields("StoreLocation").Result & Chr(39)
strContactPhone = Chr(39) & doc.FormFields("ContactPhone").Result & Chr(39)
strCableTestedValidate = Chr(39) & doc.FormFields("CableTestedValidated").Result & Chr(39)
strNumberGoodLines = Chr(39) & doc.FormFields("NumberGoodLines").Result & Chr(39)
strNumberBadLines = Chr(39) & doc.FormFields("NumberBadLines").Result & Chr(39)

'Define sql string used to insert each record in the destination workbook.
'Don't omit the $ in the sheet identifier.
strSQL = "INSERT INTO [SiteSurveyData$]" _
& " (StoreNumber, SurveyorName, SurveyDate, StoreLocation, ContactPhone, CableTestedValidated, NumberGoodLines, NumberBadLines)" _
& " VALUES (" _
& strStoreNumber & ", " _
& strSurveyorName & ", " _
& strSurveyDate & ", " _
& strStoreLocation & ", " _
& strContactPhone & ", " _
& strCableTestedValidated & ", " _
& strNumberGoodLines & ", " _
& strNumberBadLines _
& ")"
Debug.Print strSQL
'Define connection string and open connection to destination workbook file.
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=C:\Users\Documents\POS Survey Database.xlsx;" & _
"Extended Properties=Excel 8.0;"
.Open
'Transfer data.
.Execute strSQL
End With
Set doc = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly, "Error"
On Error GoTo 0
On Error Resume Next
cnn.Close
Set doc = Nothing
Set cnn = Nothing
End Sub