Hi,

I'm re-posting as my last post was probably confusing.

I need to copy data from a workbook stored on a network drive accessed
through a secure VPN server. I have managed to do this fine, however
the retrieval takes over 4 mins - plenty of time for the connection to
drop - plus Excel stops responding whilst the copy is happening.

The workbook I am copying from has approx 10 columns x 50000 rows in 1
worksheet. It may be accessed by up to 50 people at one time meaning I
would prefer not to have to open the worksheet.

I need to do one of 2 alternatives - whichever will give me the
quickest result.

Either copy the whole worksheet back to my file
Or
Filter the data in the worksheet to select only the required data
(that matches a criteria) and copy the required data back to my file
(approx 10 columns x 2000 rows).

I currently have this working by using code to do the equivalent of a
'get external data' database query, but like I said it is slow.

Yesterday I found some code that copies the formula of cell A1 in the
worksheet back to my workbook, then copies the formulas down, however
this seems to be limited to about 8000 rows before I get an 'out of
memory' error. This method was much quicker and would be great if I
could filter the records to copy the formula of the 2000 records I
required - however I can't see a way to do this without opening the
workbook.

If anybody has any suggestions I would really appreciate it.

Hi Anon,

Have a look at Ron's site under copy/merge/data.

He has some examples for getting data from closed & open workbooks and
l seem to remember one is specifically for networks.

I have used 1 of the examples to import approx 6000 rows from 13
closed workbooks and it takes about 20 seconds.

http://www.rondebruin.nl/tips.htm

Regards

Michael beckinsale