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