VBA SQL Connection to Excel from Word returns no records

Question

Trying to figure out how to read data from an Excel spreadsheet into a Word document is giving me fits. I work with Office 365. I connected to the workbook and sheet using a couple of articles I read, but no records were returned. I used the ActiveX Data Objects 2.8 Library as a reference. The code returns a -1 when it reaches the message box.

Here is what I have for code at this point.

Sub CreateLetter()

Dim rs As ADODB.Recordset, rsCount As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sqlGetTbl As String
Dim sDataSource As String, sDataTable As String
Dim sProvider As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
sDataSource = "D:\spreadsheetname.xlsx"
sDataTable = "[Donor Contact List$]"

sProvider = "Microsoft.ACE.OLEDB.16.0;"
sDataSource = sDataSource & ";Extended Properties = 'Excel 12.0 Xml;HDR=Yes';"

With cn
    .Provider = sProvider
    .ConnectionString = "Data Source=" & sDataSource
    .Open
End  With

sqlGetTbl = "SELECT * FROM " & sDataTable
Set rs = cn.Execute(sqlGetTbl)
MsgBox rs.RecordCount

Do
    With Selection
        .TypeText FullName & Chr(11) & Street & Chr(11) & City & ", " & St & "  " & Zip
        .TypeParagraph
    End With
Loop Until rs.EOF

rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing

End Sub

Answer 1

What follows functions for me. Include IMEX=1 in the connection string to indicate that the data source has headers in order to obtain the recordset information from the test data source I used.

The code in question has additional problems:

1. It's necessary to use rs.MoveFirst to "initialize" the recordset (get it to the first record.
2. It's necessary to use rs.MoveNext in each iteration of the loop, otherwise the code will "get stuck" on the first record and go into an infinite loop.
3. It's safer to use Do While Not rs.EOF for the test for exiting the loop in case there really are no records in the recordset.
4. It's necessary to specify the fields from the recordset. Fullname, city, street, etc. are meaningless, at least in the code in the question.
5. Always release objects (set to Nothing) in the reverse order they were created. It's not possible to release the connection before the recordset is released - the active recordset will keep the connection "live".

My sample code:

Sub CreateLetter()

    Dim rs As ADODB.Recordset, rsCount As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim sqlGetTbl As String
    Dim sDataSource As String, sDataTable As String
    Dim sProvider As String

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    sDataSource = "D:\spreadsheetname.xlsx"
    sDataTable = "[Donor Contact List$]"

    sProvider = "Microsoft.ACE.OLEDB.16.0;"
    sDataSource = sDataSource & ";Extended Properties = 'Excel 12.0 Xml;HDR=Yes;IMEX=1';"

    With cn
        .Provider = sProvider
        .ConnectionString = "Data Source=" & sDataSource
        .Open
    End With

    sqlGetTbl = "SELECT * FROM " & sDataTable
    cn.CursorLocation = adUseClient
    Set rs = cn.Execute(sqlGetTbl)
    MsgBox rs.RecordCount
    rs.MoveFirst

    Do While Not rs.EOF
        With Selection
            .TypeText rs.Fields("Fullname").value & Chr(11) & rs.Fields("street").value & Chr(11) & rs.Fields("city").value & ", " & st & "  " & rs.Fields("Zip").Value
            .TypeParagraph
        End With
        rs.MoveNext
    Loop

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Answer 2

I think you need to specify a data range not just the sheet. that is:

sDataTable = "[Donor Contact List$A1:N567]"