Download Access Database to Excel using VBA | Excel forum

Hi Tony, thanks for your query. Here's a slightly simpler verson of attaching to a database, which bolts the data straight onto the worksheet, rather than going via an instance of the Sale object. Note that you'll have to adjust the connection string accordingly and use out Northwind for Excel database for this to work.

Here's the subroutine:

******************
Sub GetData()


Dim intTargetRowCount As Integer


intTargetRowCount = 2

Const ConnString As String _
= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Documents and Settings\Training0\Desktop\Northwind For Excel.mdb;Persist Security Info=False"

Const SQL As String = "SELECT * FROM qryOrdersForExcel"

Dim Con1 As ADODB.Connection
Dim Recordset As ADODB.Recordset

Set Con1 = New ADODB.Connection
Con1.ConnectionString = ConnString

Con1.Open


''''''''''''''''''''''''
'now do the same process for the recordset itself

Set Recordset = New ADODB.Recordset

Call Recordset.Open(SQL, Con1)


With Recordset

If .BOF And .EOF Then

MsgBox "There are no records"
Exit Sub

End If


'headings

ActiveSheet.Cells(1, 1) = "Salesperson"
ActiveSheet.Cells(1, 2) = "Customer"
ActiveSheet.Cells(1, 3) = "Product"
ActiveSheet.Cells(1, 4) = "Date of Sale"
ActiveSheet.Cells(1, 5) = "Price Per Unit"
ActiveSheet.Cells(1, 6) = "Quantity"
ActiveSheet.Cells(1, 7) = "Total"



''''''here we go to the first record

.MoveFirst

Do While Not .EOF

ActiveSheet.Cells(intTargetRowCount, 1).Value = .Fields("LastName")
ActiveSheet.Cells(intTargetRowCount, 2).Value = .Fields("CompanyName")
ActiveSheet.Cells(intTargetRowCount, 3).Value = .Fields("ProductName")
ActiveSheet.Cells(intTargetRowCount, 4).Value = .Fields("OrderDate")
ActiveSheet.Cells(intTargetRowCount, 5).Value = .Fields("UnitPrice")
ActiveSheet.Cells(intTargetRowCount, 6).Value = .Fields("Quantity")
ActiveSheet.Cells(intTargetRowCount, 7).Value = .Fields("OrderLine")

intTargetRowCount = intTargetRowCount + 1

.MoveNext

Loop

End With

Con1.Close

End Sub


******************

Hope this helps,

Anthony