ExcelBanter - Create Excel Document from a Dataset using VB.Net

In the IDE set a reference to Microsoft Active Data
Objects

In your code, dim a variable as a ADODB connection and
alother variable as an ADOB recordset. Here's a very
simple, but working example

Sub GetData()

Dim oConn As ADODB.Connection
Dim oRST As ADODB.Recordset
Dim sConnectionString As String
Dim sSQL As String
Dim ws As Worksheet
Dim i As Long

' Initialise variables
sConnectionString = _
"PROVIDER=MSDASQL;driver={SQL Server};" & _
"server=MyServer;uid=;pwd=;database=MyDatabaseName ;"

' set the SQL query command text:
sSQL = "SELECT * from Funds"

' create objects
Set oConn = New ADODB.Connection
Set oRST = New ADODB.Recordset

' connect to the database

With oConn
.ConnectionString = sConnectionString
.Open
End With

' fetch the data
oRST.Open sSQL, oConn, adOpenForwardOnly,
adLockOptimistic

'drop data into a new worksheet
With oRST
If Not .EOF Then
Set ws = Worksheets.Add
' get the field names as headers
For i = 1 To .Fields.Count
ws.Cells(1, i).Value = .Fields(i - 1).Name
Next
ws.Range("A2").CopyFromRecordset oRST
End If

.Close

End With

oConn.Close

Set oRST = Nothing
Set oConn = Nothing

End Sub

Make sure that you (a) set the correct text for MyServer
and MyDatabaseName in the connection string, and that you
can use NT security with the database.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi;
I am using Visual Studio 2003 and Office 2003. What
I
would like is on a button click, run query against a
database and take the returned dataset to create an
excel
document.

I am pretty sure it is possible, but I just don't know
how
to.

Any help will be greatly apprecicated.

thanks
Jacob
.