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.
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';"
.Provider = sProvider
.ConnectionString = "Data Source=" & sDataSource
sqlGetTbl = "SELECT * FROM " & sDataTable
Set rs = cn.Execute(sqlGetTbl)
.TypeText FullName & Chr(11) & Street & Chr(11) & City & ", " & St & " " & Zip
Loop Until rs.EOF
Set cn = Nothing
Set rs = Nothing