VBA SQL Connection to Excel from Word returns no records

0 votes

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
Nov 27, 2022 in Others by Kithuzzz
• 38,010 points
416 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Convert CASE WHEN logic from SQL to EXCEL

A combination of IF(), ISNUMBER(SEARCH()), and OR() ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
590 views
0 votes
1 answer
0 votes
1 answer

Paste special issue, From Excel to Word

Since the table in the word document ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 63,420 points
323 views
0 votes
1 answer

How to add text on new page in word doc via excel vba

First start with: objDoc.Range.Insertafter vbCr & Chr(12) & ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
1,401 views
0 votes
1 answer

How can I find and replace text in Word using Excel VBA?

Try this code Option Explicit Const wdReplaceAll = 2 Sub ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
3,529 views
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
366 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,420 points
540 views
0 votes
1 answer

Indent table pasted from Excel into Word

It is feasible to pick up the ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,420 points
333 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP