by Sam Blagoslovenskiy (5 Submissions)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 30th May 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Sending data to Excel using ADO
VBcoders
Private Sub Command1_Click()
Dim cn As object
Dim s As object
Dim a As Object
Dim w As Object
Dim ws As Object
Dim q As Object
set cn = createobject("ADODB.Connection")
set s = createobject("ADODB.Recordset")
cn.ConnectionString = <your connection string>
cn.Open
s.CursorLocation = adUseClient
s.CursorType = adOpenStatic
s.LockType = adLockReadOnly
s.Source = Text1
s.Open , cn, , , adCmdText
Set a = CreateObject("Excel.Application.9")
Set w = a.Workbooks.Add()
Set ws = w.Worksheets(1)
ws.Activate
ws.Range("A1").Select
a.Visible = True
Set q = ws.QueryTables.Add(s, ws.Range("A1"))
q.BackgroundQuery = False
Set q.Recordset = s
q.Refresh
Set q = Nothing
Set ws = Nothing
Set w = Nothing
Set a = Nothing
End Sub