Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve value from closed workbook without using recordsets
Hi,
I have a sheet that needs to retrieve values from a workbook over a network. The sheet is large and a lot of information needs to be filtered and retrieved. I have tried various methods (as below) as the speed is the main issue. Today I have found a quick method but need some help adjusting it to my needs if possible. The first method I used was to open the workbook, filter the sheet and copy the filtered values back into my sheet. The opening of the workbook slowed the process down dramatically. Secondly I used a query table as code below. Again this was not quick (I am retrieving approx 2000 records from a total of about 50000 records in the sheet), and took about 4 - 5 mins to retrieve the filtered 2000 records. With Selection.QueryTable .Connection = _ "ODBC;DSN=Excel Files;DBQ= " & PATHANDNAMEANDTYPE & ";DefaultDir= " & PATH & ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;" ..CommandText = Array( _ "SELECT `" & FILNAMES & "`.`Siebel Id`, `" & FILNAMES & "`.Outlet, `" & FILNAMES & "`.`Street Address`, `" & FILNAMES & "`.Locality, `" & FILNAMES & "`.Town, `" & FILNAMES & "`.County, `" & FILNAMES & "`.`Outer Postcode`, `" & FILNAMES & "`.`Inner Postcode`, `" & FILNAMES & "`.`Phone No#`, " _ , _ "`" & FILNAMES & "`.`Outlet Status`, `" & FILNAMES & "`.Tenure, `" & FILNAMES & "`.`Primary Streetmap`, `" & FILNAMES & "`.`Last BDE Visit`, `" & FILNAMES & "`.Owner, `" & FILNAMES & "`.Operator, `" & FILNAMES & "`.`BDE Territory`, `" & FILNAMES & "`.Region, `" & FILNAMES & "`.Division" & Chr(13) & "" & Chr(10) & "FRO" _ , _ "M `" & PATHANDNAME & "`.`" & FILNAMES & "` `" & FILNAMES & "`" & Chr(13) & "" & Chr(10) & "WHERE (`" & FILNAMES & "`.`BDE Territory`='" & BDENO & "')" _ ) .Refresh BackgroundQuery:=False End With Today I found this code; Dim x x = "a1:r8000" With ActiveSheet.Range(x) ..FormulaArray = "='" & path & "\[" & filname & "]" & shtname & "'!" & .Value = .Value End With This gets the first 8000 records in about 1 min 30, however I cannot filter the records. (I cannot retrieve all of the records and then filter them as I get an error telling me I am out of memory if I try and get more than 8000 records at once). I only want records that have a certain value in one column. Is it possible to do this using this (quick) code, or do I have to revert to my old method and learn to be patient? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using ADO Recordsets to pull data from a closed Excel WB. | Excel Programming | |||
How can I retrieve a spreadsheet closed out of (not saved)? | Excel Worksheet Functions | |||
How can I retrieve a spreadsheet closed out of (not saved)? | Excel Worksheet Functions | |||
How can I retrieve a file that was closed without saving? | Excel Discussion (Misc queries) | |||
Retrieve Data from closed workbooks - Ron de Bruin | Excel Programming |