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

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

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

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....tml#advantages
James

"Berj" wrote in message
...
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

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

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


On Nov 28, 12:56 am, Berj wrote:
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