Problems with reading stuff from closed files

Hi,
I would like to create a single excel file, which is composed by fragments of different (closed) excel fies (it should take some data from the files).
Is that possible?
Thanks

Try to see this URL:
http://www.rondebruin.nl/copy7.htm

Regards,
Antonio

Hello,

Yesterday, I asked how to read stuff from closed files.
Someone kindly suggested the following link:

http://www.rondebruin.nl/copy7.htm
I tried that and it worked, the problem is that it's too slow.
In the main sub, it uses DoEvents (which yields the processor), which is what I believe that causes it to be slow.
1) Is it necessary to use the wait operation?
2) Do you know faster / other ways to read data from (many) other xls files?

Thanks a lot!

Hello,

Yesterday, I asked how to read stuff from closed files.
Someone kindly suggested the following link:

http://www.rondebruin.nl/copy7.htm
I tried that and it worked, the problem is that it's too slow.
In the main sub, it uses DoEvents (which yields the processor), which is what I believe that causes it to be slow.
1) Is it necessary to use the wait operation?
2) Do you know faster / other ways to read data from (many) other xls files?

Thanks a lot!

Hello bluesdata3,

It would help to know how slow is slow? There are other methods of data retrieval from a closed workbook, but selecting the best method depends on several factors. It would be best for you to post your workbook so a workable solution can be suggested.

Sincerely,
Leith Ross

Let's see if this is any faster

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 DestRnage
        .Formula = "='" & FilePath & "/[" & FileName & "]" & SheetName _
                        & "'!" & Range(SourceRange).Cells(1).Address(0,0)
        .Value = .Value
    End With
        Start = Timer
End Sub

one more thing, is it possible just to put the 'wait' section in a comment?
thanks

How about

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

    Dim Start, i As Long
    Application.Goto DestRange
    Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
                                     Range(SourceRange).Columns.Count)
    With DestRnage.Rows(1)
        For i = 1 To .Columns.Count
            .Cells(1,i).Formula = _
                   "='" & FilePath & "/[" & FileName & "]" & SheetName _
                        & "'!" & Range(SourceRange).Cells(1,i).Address(0,0)
        Next
        .AutoFill .Resize(DestRange.Rows.Count)
        .Resize(DestRange.Rows.Count).Value = Resize(DestRange.Rows.Count).Value
    End With
        Start = Timer
End Sub

Why ?
What you are doing is just reading the block of range into the same size of the range.
No need to use Array Formula. and Do Events

You code is using FormulaArray, which doesn't need to be an array formula.
Array formula needs more time to calculate.

Show me the code that you are calling this sub routine.