Macro opens files as it tries to access them !!!

[Berj]

Hi,

I have created a macro in an Excel file which uses the data from several
other Excel files and sheets. It gets the information from these other files
and puts them in the new file. The problem is that, the other files need to
be open at the time when the macro is running and trying to access them! If
they are closed, it gives an error as “RUN TIME ERROR ‘9’: SUBSCRIPT OUT OF
RANGEâ€.
I want the macro to run smoothly, accessing the data in the other files
without giving any errors and without even opening them. Because opening them
will slow down the system and I will see a lot of window switching in front
of my eyes!!
Is there a command which I can add at the beginning of the macro to disable
the opening of the files, at the same time enabling access to their data? Or
is there another way of solving this problem.
Please help, as it is very urgent.

Thanks in advance.
Berj

 

[Zone]

Maybe you could use a function that doesn't require the other workbook to be
open, like SUMPRODUCT. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#advantages
James

 

[ilia]

Open them. Here is an example of a sub that opens three workbooks by
a specified name. You could add code in-between to copy data from
them to current workbook, or whatever you need done (since you didn't
specify).

Public Sub openWorkbooks()
Dim fileNames(1 To 3) As String
Dim wkb As Excel.Workbook
Dim i As Long

Application.ScreenUpdating = False

fileNames(1) = "Book1.xls"
fileNames(2) = "Book2.xls"
fileNames(3) = "Book3.xls"

For i = LBound(fileNames) To UBound(fileNames)
On Error Resume Next
Set wkb = Application.Workbooks.Open _
(ThisWorkbook.Path & "\" & fileNames(i))
On Error GoTo 0
If wkb Is Nothing Then
Call MsgBox("Workbook " & fileNames(i) & " not found!")
Else
' copy data from current book
wkb.Close
Set wkb = Nothing
End If
Next i

Application.ScreenUpdating = True
End Sub