ExcelBanter - View Single Post

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