About Us

Mike

Email me and i'll send you an example

"Al" wrote:

I've understood most of what you've advised, but the need to "add a reference
to the Active X Data" has lost me!
Your offier of a small database and excel file as an example would be much
appreciated! THanks!

Al

"Mike" wrote:

Well Al
I'm at the "walking stage" of programming. But i have been pulling data
from a
mdb into Excel for more than 2 years. The code needs to be put into a module.

To do this you will need hit Atl+F11 this will bring you to the Visual Basic
editor.

In the Project window to the left you will see the name of your workbook.
Right click and select Insert then Module. Paste the code that I have posted
into this module. You will also need to add a reference to the Active X Data
objects 2.0 Library.

You need to change a couple of things in the code like these lines
Source=C:\PathToYourMdb\Ilsa.mdb
sSQL = "SELECT Field1, Field2 From TableName"
rs.Fields("Field1").Value
rs.Fields("Field2").Value
Good luck

Also If you would like me to I could create a small database and excel file
as an example to send to you.
"Al" wrote:

Mike, I'm at the "crawling stage" of VBA knowledge and proficiency....can you
further explain how I must use your code to my application? Should I further
explain the spreadsheet and d/base that I have?

"Mike" wrote:

Sub getDataFromaccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim rowNumber As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=C:\PathToYourMdb\Ilsa.mdb;Persist Security
Info=False"
'sSQL = "Replace with your query"
sSQL = "SELECT Field1, Field2 From TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
rowNumber = 2 'Starting Row Number for data
Do While (Not rs.EOF)
Range("A" & rowNumber) = rs.Fields("Field1").Value
Range("B" & rowNumber) = rs.Fields("Field2").Value
rowNumber = rowNumber + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

"Al" wrote:

I'd like to pull out of an Access database various data, e.g. my Excel
spreadsheet produces calcs of company costs against personnel by multiplying
the (hours by Mr X) x (Salary of Mr X)

This is repeated for hundreds of employees.

Appreciate some pointers how to achieve this?
Al

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Data from Access to Excel	Paul Dennis	Excel Discussion (Misc queries)	8	October 10th 06 06:33 PM
linking data in excel and access	John V	Links and Linking in Excel	2	July 12th 06 05:41 PM
Access Form In An Access Report (SubForm) Question	Gary	Links and Linking in Excel	0	January 27th 06 05:54 AM
Excel Queries using Access data	baconroll	Excel Discussion (Misc queries)	1	October 12th 05 05:19 PM
export access to excel. change access & update excel at same time	fastcar	Excel Discussion (Misc queries)	0	June 24th 05 09:27 PM

Menu

About Us