Hi all,

I would like to know if it´s possible to import data from access usin
directly a variable in VBA excel instead of pasting all values in m
worksheet.

Thanks a lot

--
internaci
-----------------------------------------------------------------------
internacio's Profile: http://www.excelforum.com/member.php...fo&userid=3096
View this thread: http://www.excelforum.com/showthread.php?threadid=50630

sure is -- help covers it.
In the IDE set a reference to the Microsoft ActiveX Data Obects Library
you probably have v2.7

Adapt the code below, which should be pasted into a standatd module.
The variable MyFile is assigned the Access Databas enaem that I'm using. You
should replace this with the full path etc of the Access database that you
want to use
You should also edit the SQL script accordingly...as my demo is pretty
simple, so is my sql
run the code and a connection is made to the database, the recordset in
memory gets populated according to the sql script. From the recordset fields,
the field names are dropped intio the sheet as table headers and finally the
data is copied directlyto the sheet



Option Explicit

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long

MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Cells.Clear

For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next

Range("A2").CopyFromRecordset rst

rst.Close
con.Close

Set rst = Nothing
Set con = Nothing

End Sub

"internacio" wrote:


Hi all,

I would like to know if it´s possible to import data from access using
directly a variable in VBA excel instead of pasting all values in my
worksheet.

Thanks a lot.


--
internacio
------------------------------------------------------------------------
internacio's Profile: http://www.excelforum.com/member.php...o&userid=30960
View this thread: http://www.excelforum.com/showthread...hreadid=506308

In Patrick's code, he is writing the information to the worksheet. You
could put it in an array with something like

Dim v as Variant

v = Application.Transpose(rst)

or work with rst directly.

--
Regards,
Tom Ogilvy

"Patrick Molloy" wrote in message
...
sure is -- help covers it.
In the IDE set a reference to the Microsoft ActiveX Data Obects Library
you probably have v2.7

Adapt the code below, which should be pasted into a standatd module.
The variable MyFile is assigned the Access Databas enaem that I'm using.
You
should replace this with the full path etc of the Access database that you
want to use
You should also edit the SQL script accordingly...as my demo is pretty
simple, so is my sql
run the code and a connection is made to the database, the recordset in
memory gets populated according to the sql script. From the recordset
fields,
the field names are dropped intio the sheet as table headers and finally
the
data is copied directlyto the sheet



Option Explicit

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long

MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Cells.Clear

For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next

Range("A2").CopyFromRecordset rst

rst.Close
con.Close

Set rst = Nothing
Set con = Nothing

End Sub

"internacio" wrote:


Hi all,

I would like to know if it´s possible to import data from access using
directly a variable in VBA excel instead of pasting all values in my
worksheet.

Thanks a lot.


--
internacio
------------------------------------------------------------------------
internacio's Profile:
http://www.excelforum.com/member.php...o&userid=30960
View this thread:
http://www.excelforum.com/showthread...hreadid=506308