View Full Version : VBA Code - Data from Access to excel
Bigyin
09-01-2014, 10:50 AM
Hi,
I have searched your forum and looked in Google but cant find what I am looking for. I am not the best with VBA hence why im asking for some help!
what I am looking to do is create code so that i call it to run a query within access to pull data into Excel.
something im looking to do would be a bit of code like below.
Importdata(database1,query1,sheet1,1,1);
Database1 - would be the filename of the database
Query1 - would be the query to run within access
Sheet1 - would be the sheet within excel I want the data to go
1,1 - would be were the data within that sheet the data would go, so this would start from a1.
I would save all the databases within the same folder and all have the same password, so could set a global variable of path where the databases would be saved?
Hope someone can help, and thanks in advance!
mancubus
09-01-2014, 12:17 PM
welcome to vbax.
try this.
Sub Import_Access_Table(strDB As String, strQuery As String, strSheet As String)
'requires a reference to Microsoft Activex Data Objects X.X Library
'from Tools - References in VBE
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws As Worksheet
Dim FieldNum As Long, i As Integer
cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDB & ";"
rst.Open strQuery, cnt
FieldNum = rst.Fields.Count
Set ws = Worksheets(strSheet)
With ws
.Cells.Clear
For i = 0 To FieldNum - 1
.Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
.Cells(2, 1).CopyFromRecordset rst
End With
End Sub
you can call it like this:
Sub import_test()
Dim accDb As String, accQuery As String, xlSheet As String
accDb = "C:\Users\Me\Documents\ms_access\my_access_db.accdb"
accQuery = "SELECT * FROM MyAccessTableName"
xlSheet = "Sheet1"
Call Import_Access_Table(accDb, accQuery, xlSheet)
End Sub
mancubus
09-02-2014, 12:10 AM
maybe...
Public Const lRow As Long = 8
Public Const lCol As Long = 3
Public Const dbPath As String = "C:\Users\Me\Documents\ms_access\"
Public Const sUser As String = "Admin"
Public Const sPass As String = "MyPassword"
Sub Import_Access_Table( _
strDB As String, _
strQuery As String, _
strSheet As String, _
Optional lngRow As Long = 1, _
Optional lngCol As Long = 1, _
Optional strUser As String = "", _
Optional strPass As String = "")
'requires a reference to Microsoft Activex Data Objects X.X Library
'from Tools - References in VBE
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim FieldNum As Long, i As Integer
cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";User Id=" & strUser & ";Password=" & strPass
rst.Open strQuery, cnt
FieldNum = rst.Fields.Count
With Worksheets(strSheet)
For i = 0 To FieldNum - 1
.Cells(lngRow, lngCol).Offset(, i).Value = rst.Fields(i).Name
Next i
.Cells(lngRow, lngCol).Offset(1).CopyFromRecordset rst
End With
End Sub
Sub import_test()
Dim accDb As String, accQuery As String, xlSheet As String
accDb = dbPath & "eks_pls.accdb"
accQuery = "SELECT * FROM MyAccessTableName"
xlSheet = "Sheet1"
Call Import_Access_Table(accDb, accQuery, xlSheet, lRow, lCol, sUser, sPass)
End Sub
where
c00 is the fullname of the Access file
c01 is the name of the table (or Query) in the database you want to retrieve
Sheet1 is the codename of the sheet you want the data to be imported in.
Sub M_snb()
c00 = "G:\Access\fiets.mdb"
c01 = "Q_test"
With Sheet1.ListObjects.Add(0, "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & c00 & """", , , Range("$A$1")).QueryTable
.CommandType = 3
.CommandText = c01
.Refresh False
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.