Limiting Data Imported from Access to Excel

[Mark]

The code below pulls in a query from Access to my Excel spreadsheet.
It works great, but I'd like to limit the amount of data it brings in.
I've used parametric queries before - but they limit you to reading
data from one fixed file. In this case the Access file is read from a
cell in an Excel spreadsheet and used in the code below.

For example, one column in the Access query is named SALE_LOT.
Is there any way to modify the Access query so it could limit records
to SALE_LOT = "189:002"?

Here's the code. Thanks in advance! Mark

Private Sub CommandButton1_Click()
Worksheets("data").Range("A1:HH50000").Clear
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
strDB = Worksheets("input").Range("C4") '<<<<this is the Access
path/file
Set xlApp = Application
Set ap = CreateObject("Access.Application")
ap.OpenCurrentDatabase (strDB)
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
'begin import from "master oneline detail 2"
xlApp.Sheets("data").Select
rst.Open "Select * From [master oneline detail 2]", cnt
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("Data")
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst
rst.Close
' end import
cnt.Close
Set ap = Nothing
Set rst = Nothing
Set cnt = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub

 

[Dick Kusleika]

Mark

This line

rst.Open "Select * From [master oneline detail 2]", cnt

should be changed so that the SQL argument contains the criteria. Set up a
query in Access returning the records you want and look at the SQL view to
see the proper string.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

The code below pulls in a query from Access to my Excel spreadsheet.
It works great, but I'd like to limit the amount of data it brings in.
I've used parametric queries before - but they limit you to reading
data from one fixed file. In this case the Access file is read from a
cell in an Excel spreadsheet and used in the code below.

For example, one column in the Access query is named SALE_LOT.
Is there any way to modify the Access query so it could limit records
to SALE_LOT = "189:002"?

Here's the code. Thanks in advance! Mark

Private Sub CommandButton1_Click()
Worksheets("data").Range("A1:HH50000").Clear
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
strDB = Worksheets("input").Range("C4") '<<<<this is the Access
path/file
Set xlApp = Application
Set ap = CreateObject("Access.Application")
ap.OpenCurrentDatabase (strDB)
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
'begin import from "master oneline detail 2"
xlApp.Sheets("data").Select
rst.Open "Select * From [master oneline detail 2]", cnt
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("Data")
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst
rst.Close
' end import
cnt.Close
Set ap = Nothing
Set rst = Nothing
Set cnt = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub

 

[Mark Roach]

Thanks, Dick. Great suggestion. It worked wonderfully.

Mark

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas