A
anon
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?
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?