Hi,

I have an excel workbook which the user updates once a week by
pressing a "synchronise" button.

This calls some code which opens a shared file on a network, filters
the (60000+) records on the sheet and copies the filtered records
(approx 3000) back into the original workbook.

Problems -

Too many people trying to "synchronise" at the same time can cause the
"synchronise" process to take up to half an hour. Even with only 1
person trying the access the file the network is slow and can take 5 -
10 mins.

I therefore figured my solution would be to use ADO (new to me but I
did some research) to copy a named range without opening the file (on
the presumption that lots of people would be able to do this at once,
and if the code was not having to physically open the file it would
speed it up). I thought instead of filtering I would just copy ALL of
the data from the file and bring it back to my workbook,then filter it
in place.

I used code from rondebruin.

It works excellently if I use a small range - however there are 60000+
rows of data in the file that I need to copy and it fails seemingly
because the range is too large.

My thoughts;

Can I filter the workbook without opening it to only copy the required
range? Or is there a way I can copy all of the data without it
failing.

Many thanks in advance

How is the ADO recordSet opened?

You can use such SQL statement to filter out unwanted records:

MyRecordSet.Opne "SELECT Col1, Col2... FROM [Range]$ WHERE col1=....",
connectionString,...

As long as you define the condition in "WHERE" clause fine enough, you could
only retrieve the records you need, not whole 60000 each time.


"keri" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I have an excel workbook which the user updates once a week by
> pressing a "synchronise" button.
>
> This calls some code which opens a shared file on a network, filters
> the (60000+) records on the sheet and copies the filtered records
> (approx 3000) back into the original workbook.
>
> Problems -
>
> Too many people trying to "synchronise" at the same time can cause the
> "synchronise" process to take up to half an hour. Even with only 1
> person trying the access the file the network is slow and can take 5 -
> 10 mins.
>
> I therefore figured my solution would be to use ADO (new to me but I
> did some research) to copy a named range without opening the file (on
> the presumption that lots of people would be able to do this at once,
> and if the code was not having to physically open the file it would
> speed it up). I thought instead of filtering I would just copy ALL of
> the data from the file and bring it back to my workbook,then filter it
> in place.
>
> I used code from rondebruin.
>
> It works excellently if I use a small range - however there are 60000+
> rows of data in the file that I need to copy and it fails seemingly
> because the range is too large.
>
> My thoughts;
>
> Can I filter the workbook without opening it to only copy the required
> range? Or is there a way I can copy all of the data without it
> failing.
>
> Many thanks in advance
>

My code (or not at all mine - thanks to ron)

(obviously the path in this first sub isn't correct - nor the range I
am trying to get - "a1:s5000" should be "a1:s60000" but obviously I
can't do that. I would want to filter the dourcerange by column C
(value to equal 0804).

Sub File_in_network_folder()
getrange("C:\documents and settings\here
\desktop","south.xls","south","a1:s5000",sheets("sheet4").range("a1")
end sub

Sub GetRange(FilePath as String, FileName as String, SheetName as
String, SourceRange as String, DestRange as Range)

Dim start
Application.Goto.DestRange
Set DestRange =
DestRange.Resize(Range(SourceRange).Rows.Count,Range(SourceRange).Columns.Count)

With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName
& "'!" & SourceRange

Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

..copy
..PasteSpecial xlPasteValues
..Cells(1).select
Application.CutCopyMode = False
End With
End Sub

Hi Keri

If you remove this part do you have the same problem then

> Start = Timer
> Do While Timer < Start + 2
> DoEvents
> Loop
>
> .copy
> .PasteSpecial xlPasteValues
> .Cells(1).select
> Application.CutCopyMode = False


Why not open the file for a big range
You can use code to do this ?



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> My code (or not at all mine - thanks to ron)
>
> (obviously the path in this first sub isn't correct - nor the range I
> am trying to get - "a1:s5000" should be "a1:s60000" but obviously I
> can't do that. I would want to filter the dourcerange by column C
> (value to equal 0804).
>
> Sub File_in_network_folder()
> getrange("C:\documents and settings\here
> \desktop","south.xls","south","a1:s5000",sheets("sheet4").range("a1")
> end sub
>
> Sub GetRange(FilePath as String, FileName as String, SheetName as
> String, SourceRange as String, DestRange as Range)
>
> Dim start
> Application.Goto.DestRange
> Set DestRange =
> DestRange.Resize(Range(SourceRange).Rows.Count,Range(SourceRange).Columns.Count)
>
> With DestRange
> .FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName
> & "'!" & SourceRange
>
> Start = Timer
> Do While Timer < Start + 2
> DoEvents
> Loop
>
> .copy
> .PasteSpecial xlPasteValues
> .Cells(1).select
> Application.CutCopyMode = False
> End With
> End Sub
>

I still have the same problem if I remove that part.

Opening the file is the problem as many people are accessing at the
same time and the time it takes can be up to half an hour to open the
file, filter the data and copy the filtered range which is what I was
doing.

I'm trying everything as this is V urgent, i'm now working on
microsoft query but cannot seem to pass variables for the path and
filename to it.

Have you try ADO
http://www.rondebruin.nl/ado.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>I still have the same problem if I remove that part.
>
> Opening the file is the problem as many people are accessing at the
> same time and the time it takes can be up to half an hour to open the
> file, filter the data and copy the filtered range which is what I was
> doing.
>
> I'm trying everything as this is V urgent, i'm now working on
> microsoft query but cannot seem to pass variables for the path and
> filename to it.
>

This was the first thing I tried. My problem is that I get an out of
memory error if I try to copy everything that is in the external file
(60000 + rows) and I do not know how to use ADO (or if it is possible)
to only select records matching certain criteria (without opening the
file) (which would return approx 3000 rows)

Where is the code using ADO, as you mentioned in previous post?


"keri" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> My code (or not at all mine - thanks to ron)
>
> (obviously the path in this first sub isn't correct - nor the range I
> am trying to get - "a1:s5000" should be "a1:s60000" but obviously I
> can't do that. I would want to filter the dourcerange by column C
> (value to equal 0804).
>
> Sub File_in_network_folder()
> getrange("C:\documents and settings\here
> \desktop","south.xls","south","a1:s5000",sheets("sheet4").range("a1")
> end sub
>
> Sub GetRange(FilePath as String, FileName as String, SheetName as
> String, SourceRange as String, DestRange as Range)
>
> Dim start
> Application.Goto.DestRange
> Set DestRange =
> DestRange.Resize(Range(SourceRange).Rows.Count,Range(SourceRange).Columns.Count)
>
> With DestRange
> .FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName
> & "'!" & SourceRange
>
> Start = Timer
> Do While Timer < Start + 2
> DoEvents
> Loop
>
> .copy
> .PasteSpecial xlPasteValues
> .Cells(1).select
> Application.CutCopyMode = False
> End With
> End Sub
>