Tony has attended:
Excel VBA Advanced course
Download Access Database to Excel using VBA
Anthony,
I am trying to write some code to download data from access to excel using VBA code. I have tried to use the example we used on the course to come up with the basic framework but I am finding difficulty with code in between the .Movefirst and .Movenext section. I am a little confused because it is here we used Class Objects and I really need to just use some basic variables here to get my head round it. Please can you let me know what I can do to complete the code. I have included my code below. Thanks in advance. Tony
Sub GetData()
Const ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\Finance\TC\Client Profitability\Client Profitability data.mdb;Persist Security Info=False"
Const SQL As String = "SELECT * FROM MidasdataQuery"
Dim Con1 As ADODB.Connection
Dim Recordset As ADODB.Recordset
Con1.Open
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
.MoveFirst
THIS IS WHERE I AM UNABLE TO WRITE THE CORRECT CODE TO PULL THE DATA
.MoveNext
End With
Con1.Close
End Sub
RE: Download Access Database to Excel using VBA
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
