Scripting Excel different behavior than Interactive?
 

We run a number of Excel spreadsheets via a scheduling system to do overnight
batch calcs. Each of the spread sheets contain a macro called "RunReport"

I've recently built a new machine and installed Office and all seems fine.
If I load the spreadsheet and manually run the macro "RunReport" it all works
as expected.

But if I run a spreadsheet using a VB Script (snippet below), I get a
circular reference. Has anyone seen this before or have any thoughts on the
matter?

I can't imagine it's the sheet as it runs just fine on the old box
interactively and via a script.

New box Excel Version 9.0.3821 SR-1
Old box Excel Version 9.0.2720

Did the service pack break something?

==================================
Dim Excel
Dim SheetPath

Set objNet = WScript.CreateObject("WScript.Network")
SheetPath = "D:\Data\Test\"
Set Excel = CreateObject("Excel.Application")
Excel.Application.Visible = True
Excel.Application.Workbooks.Open SheetPath + "Test.xls", 0
Excel.Application.Run("Test.xls!RunReport")
.....

Scripting Excel different behavior than Interactive?
 

I think the answer will depend on what RunReport does.

If you rely on the activecell/activesheet, maybe you're not in the spot that you
think you should be in.



Prezent Tenz wrote:

We run a number of Excel spreadsheets via a scheduling system to do overnight
batch calcs. Each of the spread sheets contain a macro called "RunReport"

I've recently built a new machine and installed Office and all seems fine.
If I load the spreadsheet and manually run the macro "RunReport" it all works
as expected.

But if I run a spreadsheet using a VB Script (snippet below), I get a
circular reference. Has anyone seen this before or have any thoughts on the
matter?

I can't imagine it's the sheet as it runs just fine on the old box
interactively and via a script.

New box Excel Version 9.0.3821 SR-1
Old box Excel Version 9.0.2720

Did the service pack break something?

==================================
Dim Excel
Dim SheetPath

Set objNet = WScript.CreateObject("WScript.Network")
SheetPath = "D:\Data\Test\"
Set Excel = CreateObject("Excel.Application")
Excel.Application.Visible = True
Excel.Application.Workbooks.Open SheetPath + "Test.xls", 0
Excel.Application.Run("Test.xls!RunReport")
....

--

Dave Peterson

Scripting Excel different behavior than Interactive?
 

Prezent,

What happens if you change your code to the following?...

'-----------------------------------------------------------
Sub Test()
Dim appExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim SheetPath As String

'Set objNet = WScript.CreateObject("WScript.Network") ' ?

SheetPath = "D:\Data\Test\"

Set appExcel = New Excel.Application
appExcel.Visible = True ' probably not necessary
Set objWB = appExcel.Workbooks.Open(SheetPath & "Test.xls", 0)
appExcel.Run ("Test.xls!RunReport")

'do more stuff

objWB.Close savechanges:=False
Set objWB = Nothing
appExcel.Quit
Set appExcel = Nothing
End Sub
'-----------------------------------------------------------

Jim Cone
San Francisco, CA


"Prezent Tenz" wrote in message
...
We run a number of Excel spreadsheets via a scheduling system to do overnight
batch calcs. Each of the spread sheets contain a macro called "RunReport"

I've recently built a new machine and installed Office and all seems fine.
If I load the spreadsheet and manually run the macro "RunReport" it all works
as expected.

But if I run a spreadsheet using a VB Script (snippet below), I get a
circular reference. Has anyone seen this before or have any thoughts on the
matter?

I can't imagine it's the sheet as it runs just fine on the old box
interactively and via a script.

New box Excel Version 9.0.3821 SR-1
Old box Excel Version 9.0.2720

Did the service pack break something?

==================================
Dim Excel
Dim SheetPath

Set objNet = WScript.CreateObject("WScript.Network")
SheetPath = "D:\Data\Test\"
Set Excel = CreateObject("Excel.Application")
Excel.Application.Visible = True
Excel.Application.Workbooks.Open SheetPath + "Test.xls", 0
Excel.Application.Run("Test.xls!RunReport")