PDA

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

snb
09-02-2014, 01:45 AM
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