Can Excel access data from Access?!
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
|