Subject out of range

[Xentis]

In Book1 I run macro1 with the following code:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open("C:\Program\Book2.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objWorkbook.Application.Run "'Book2.xls'!macro2"

objWorkbook.SaveAs ("Book3.xls")
objExcel.Quit

The opened workbook Book2 runs macro2 which needs information from the
first workbook
It contains the line:

Year = Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")

When both files are opened and i run macro2 within Book2 everything
works fine. But when i run macro 1 it gives me a 'Subject out of
range'. Anyone a solution?

Thanks,

Xentis

 

[NickHK]

I think you are making this more difficult for yourself with the creation of
a new instance of Excel.
objExcel is completely independnt of the instance your run the code in, i.e.
the instance running Book1.

As such, this sentence "The opened workbook Book2 runs macro2 which needs
information from the first workbook", does not mean what you think, as Book2
IS the first workbook in that instance (ignoring hidden WBs).

Drop all the objExcel code and open the file in the current instance:
Set objWorkbook = Workbooks.Open("C:\Program\Book2.xls")
etc..

NickHK

 

[Xentis]

NickHK, thanks for your answer.
It is an option to lose the objExcel code but I rather not because of
two reasons.
First it shows the excel file which is being opened and as macro one
repeats this excersize for 18 times that is not preferred.
Second it prompts wheter to overwrite Book3.xls. As it does that 18
times you would have to give an answer 18 times so i would like to
avoid that as well.

If you know of a way of getting this done within the objExcel, I'd
really appreciated it.

Thanks,

Xentis

 

[NickHK]

Those obstacles can be overcome:
- If you hide WB2 (Windows>Hide), then save it from the VBE, making sure it
is the active WB/Project, when opened it will remain hidden, same as
Personal.xls normally is.
- If you wish to overwrite Book3, use .DisplayAlerts=False and .Save, If
not, .Close SaveChange:=False

Using the 2 instances means WB2 has no knowledge of WB1 (unless you pass
some reference to the inititing Excel instance) and you are dealing with
cross-process code which is very expensive CPU-wise.

NickHK