title: Exporting Data from Word Form to a new row on Excel Online Table Using VBA on word
body: <p>I want to create a Word form document that is completed daily and exports data to an Excel table I have in a sharepoint. My plan is to have a button at the bottom of the survey that runs a macro. This macro would send all data in the word form document to a new row of the excel to be stored. It's essentially the same as this: <a href="https://www.techrepublic.com/blog/10-things/10-steps-to-transferring-word-form-data-to-an-excel-sheet/" rel="nofollow noreferrer">https://www.techrepublic.com/blog/10-things/10-steps-to-transferring-word-form-data-to-an-excel-sheet/</a> However, this guide is outdated and I run an issue at the &quot;cnn.close&quot; where it gives me an &quot;Object Variable or With block variable not set&quot; run-time error (91). Any help would be appreciated, I've been hitting my head for a few weeks trying to find a solution. Thank you!</p>
  <pre><code>Sub TransferToExcel()
  'Transfer a single record from the form fields to an Excel workbook.
    Dim doc As Document
    Dim strCompanyName As String
    Dim strPhone As String
    Dim strSQL As String
    Dim cnn As ADODB.Connection
    'Get data.
    Set doc = ActiveDocument 'ThisDocument
    On Error GoTo ErrHandler
    strCompanyName = Chr(39) &amp; doc.FormFields(&quot;txtCompanyName&quot;).Result &amp; Chr(39)
    strPhone = Chr(39) &amp; doc.FormFields(&quot;txtPhone&quot;).Result &amp; Chr(39)
    'Define sql string used to insert each record in the destination workbook.
    'Don't omit the $ in the sheet identifier.
    strSQL = &quot;INSERT INTO [PhoneList$]&quot; _
      &amp; &quot; (CompanyName, Phone)&quot; _
      &amp; &quot; VALUES (&quot; _
      &amp; strCompanyName &amp; &quot;, &quot; _
      &amp; strPhone _
      &amp; &quot;)&quot;
    Debug.Print strSQL
    'Define connection string and open connection to destination workbook file.
    Set cnn = New ADODB.Connection
    With cnn
      .Provider = &quot;Microsoft.ACE.OLEDB.12.0&quot;
      .ConnectionString = &quot;Data Source=E:\Examples\Sales.xlsx;&quot; &amp; _
        &quot;Extended Properties=Excel 8.0;&quot;
      .Open
      'Transfer data.
      .Execute strSQL
    End With
    Set doc = Nothing
    Set cnn = Nothing
    Exit Sub
  ErrHandler:
    MsgBox Err.Number &amp; &quot;: &quot; &amp; Err.Description, _
      vbOKOnly, &quot;Error&quot;
    On Error GoTo 0
    On Error Resume Next
    cnn.Close
    Set doc = Nothing
    Set cnn = Nothing
  End Sub
  </code></pre>