Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Download Access Database to Excel using VBA
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Download Access Database to Excel using VBA
Resolved · Urgent Priority · Version 2003
Reply Wed 29 Jun 2011, 12:06Delegate Tony said...
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
For upcoming training course dates see: Pricing & availability
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
|