Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting Data Imported from Access to Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting Data Imported from Access to Excel
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. "Mark" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting Data Imported from Access to Excel
Thanks, Dick. Great suggestion. It worked wonderfully.
Mark Mark Roach Vice President Technical Resources The Oil & Gas Asset Clearinghouse Houston, Texas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up an Excel file to be imported into Access | Excel Discussion (Misc queries) | |||
Excel 2007 - security / limiting access | Excel Discussion (Misc queries) | |||
How to convert imported Access Hyperlinks to Excel and KEEP the li | Excel Worksheet Functions | |||
Question about limiting access to functions in Excel | Excel Discussion (Misc queries) | |||
Limiting access to excel worksheets | Excel Discussion (Misc queries) |