Need to import select Access data into Excel using two variables

G

Guest

I have a large amount of data in Access, more than Excel can manage. The
data consists of many columns; has Col_1 as unique key 1,2,3,4,5,6,7,8 etc…
and contains data that includes Col_2, Col_3, Col_4 etc….

It takes a combination of Col_2 and Col_3 to get the data I want. Col_2 has
duplicate values and Col_3 makes the subset unique.

One more thing… Col_3, Col_4 etc…may have blanks throughout the table and
can’t be changed. I don’t own it.

I want to be able to open Excel, type “Number from Col_2†into Col_A ,
“(Number fromCol_3) or (blank)â€into Col_B and have the Col_4, Col_5 etc… data
auto-populate from Access.

Col_1___Col_2__Col_3__Col_4__Col_5
1______34356____5____John____ABC
2______34356__(blank)_Tim____(blank)
3______78974____6___(blank)__FGH
4______24568__(blank)_Jack___XYZ
5______59764__(blank)_Bob____wds
6______59764____1____Jim____wds
7______59764____2____Sam____wfs
8______59764____4____Tim____wxs
9______59764____6____Jo_____mjk

Mark
 
R

Rowan Drummond

This may get you started:

Sub ImpData()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColIndex As Integer
Dim DBFullName As String
Dim TableName As String
Dim eRow As Long
Dim i As Long
Dim Val1 As Long
Dim Val2 As Variant

DBFullName = "C:\Temp\db1.mdb" '<< change as required
TableName = "Test" '<< change as required

eRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To eRow

Val1 = Cells(i, 1).Value
Val2 = Cells(i, 2).Value

' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
If Val2 = Empty Then
.Open "SELECT Col_2, Col_3, Col_4, Col_5 FROM " _
& TableName & _
" WHERE [Col_2] = " & Val1 & _
" and Col_3 is Null" _
, cn, , , adCmdText
Else
.Open "SELECT Col_2, Col_3, Col_4, Col_5 FROM " _
& TableName & _
" WHERE [Col_2] = " & Val1 & _
" and Col_3 = " & Val2 _
, cn, , , adCmdText
End If

Cells(i, 1).CopyFromRecordset rs

End With
Next i

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Also see:

http://www.erlandsendata.no/english/index.php?d=envbadacimportado

Hope this helps
Rowan
 
R

Rowan Drummond

PS you will need to set a reference to the Microsoft ActiveX Data
Objects Library using Tools>References in the VBE.

Rowan said:
This may get you started:

Sub ImpData()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColIndex As Integer
Dim DBFullName As String
Dim TableName As String
Dim eRow As Long
Dim i As Long
Dim Val1 As Long
Dim Val2 As Variant

DBFullName = "C:\Temp\db1.mdb" '<< change as required
TableName = "Test" '<< change as required

eRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To eRow

Val1 = Cells(i, 1).Value
Val2 = Cells(i, 2).Value

' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
If Val2 = Empty Then
.Open "SELECT Col_2, Col_3, Col_4, Col_5 FROM " _
& TableName & _
" WHERE [Col_2] = " & Val1 & _
" and Col_3 is Null" _
, cn, , , adCmdText
Else
.Open "SELECT Col_2, Col_3, Col_4, Col_5 FROM " _
& TableName & _
" WHERE [Col_2] = " & Val1 & _
" and Col_3 = " & Val2 _
, cn, , , adCmdText
End If

Cells(i, 1).CopyFromRecordset rs

End With
Next i

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Also see:

http://www.erlandsendata.no/english/index.php?d=envbadacimportado

Hope this helps
Rowan
I have a large amount of data in Access, more than Excel can manage.
The data consists of many columns; has Col_1 as unique key
1,2,3,4,5,6,7,8 etc… and contains data that includes Col_2, Col_3,
Col_4 etc….

It takes a combination of Col_2 and Col_3 to get the data I want.
Col_2 has duplicate values and Col_3 makes the subset unique.

One more thing… Col_3, Col_4 etc…may have blanks throughout the table
and can’t be changed. I don’t own it.

I want to be able to open Excel, type “Number from Col_2†into Col_A ,
“(Number fromCol_3) or (blank)â€into Col_B and have the Col_4, Col_5
etc… data auto-populate from Access.

Col_1___Col_2__Col_3__Col_4__Col_5 1______34356____5____John____ABC
2______34356__(blank)_Tim____(blank) 3______78974____6___(blank)__FGH
4______24568__(blank)_Jack___XYZ 5______59764__(blank)_Bob____wds
6______59764____1____Jim____wds
7______59764____2____Sam____wfs
8______59764____4____Tim____wxs
9______59764____6____Jo_____mjk

Mark
Click to expand...
Click to expand...
 
G

Guest

Works really well, fast... Thank you!!

Rowan Drummond said:
PS you will need to set a reference to the Microsoft ActiveX Data
Objects Library using Tools>References in the VBE.

Rowan said:
This may get you started:

Sub ImpData()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColIndex As Integer
Dim DBFullName As String
Dim TableName As String
Dim eRow As Long
Dim i As Long
Dim Val1 As Long
Dim Val2 As Variant

DBFullName = "C:\Temp\db1.mdb" '<< change as required
TableName = "Test" '<< change as required

eRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To eRow

Val1 = Cells(i, 1).Value
Val2 = Cells(i, 2).Value

' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
If Val2 = Empty Then
.Open "SELECT Col_2, Col_3, Col_4, Col_5 FROM " _
& TableName & _
" WHERE [Col_2] = " & Val1 & _
" and Col_3 is Null" _
, cn, , , adCmdText
Else
.Open "SELECT Col_2, Col_3, Col_4, Col_5 FROM " _
& TableName & _
" WHERE [Col_2] = " & Val1 & _
" and Col_3 = " & Val2 _
, cn, , , adCmdText
End If

Cells(i, 1).CopyFromRecordset rs

End With
Next i

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Also see:

http://www.erlandsendata.no/english/index.php?d=envbadacimportado

Hope this helps
Rowan
I have a large amount of data in Access, more than Excel can manage.
The data consists of many columns; has Col_1 as unique key
1,2,3,4,5,6,7,8 etc… and contains data that includes Col_2, Col_3,
Col_4 etc….

It takes a combination of Col_2 and Col_3 to get the data I want.
Col_2 has duplicate values and Col_3 makes the subset unique.

One more thing… Col_3, Col_4 etc…may have blanks throughout the table
and can’t be changed. I don’t own it.

I want to be able to open Excel, type “Number from Col_2†into Col_A ,
“(Number fromCol_3) or (blank)â€into Col_B and have the Col_4, Col_5
etc… data auto-populate from Access.

Col_1___Col_2__Col_3__Col_4__Col_5 1______34356____5____John____ABC
2______34356__(blank)_Tim____(blank) 3______78974____6___(blank)__FGH
4______24568__(blank)_Jack___XYZ 5______59764__(blank)_Bob____wds
6______59764____1____Jim____wds
7______59764____2____Sam____wfs
8______59764____4____Tim____wxs
9______59764____6____Jo_____mjk

Mark
Click to expand...
Click to expand...
Click to expand...
 
T

travismorien

It can also be done without VBA from the user interface.

Data > Import external data > New database query.

Select Access Database, navigate to the .mdb file and you can set up
the query you want.

To do a parameter query (using a cell as the reference), put the value
of the query you want into square brackets. Instead of just typing "2"
if you want to run a query such that the value is 2, type "[2]".
Actually you can put any arbitrary text in there, so "[blah]" will work
just as well.

Close and return data to Acess, but before you press the final ok click
on parameters, you can choose the option there to take a cell value as
the input.

VBA is more powerful, but that's the slack way of doing it.

Travis
 
Top