Passing the current Excel File as an object to a VBA Script

[gpapp]

I'm opening an existing Excel file in Macro Script using ExecuteFile. My goal is to store a (large) amount of data into different arrays in VBA. I'm sure I'm missing a step. I can't get the VBA script to recognize the window object. How do I get the current object into VBSCript? An example follows:


let>strow=4
let>erow=8
ExecuteFile>fname
wait>2.0
VBSTART
sub getdata(fname, strow, erow)
nwin = windows.count
msgbox("num of windows =" & nwin)
msgbox("file name = " & fname)
windows(fname).activate
msgbox ("start row = " & strow & " ending row = " & erow)
' Set myfs = CreateObject("Scripting.FileSystemObject")
' myfs.thisworkbook.select
msgbox ("this workbook is " & activeworkbook.name )
for i = strow to erow
application.statusbar = "selecting row " & i
range("A" & i).select
next
end sub
VBEND
VBRUN>getdata,%fname%,%strow%,%erow%

[Captive]

I haven't checked the code itself, but the structure should be:

VBSTART
[... vbscript functions / subs / etc ...]
VBEND

[ Regular code here..]
[ In your case, the initial LET> line, etc can go here...]
let>strow=4
let>erow=8
ExecuteFile>fname
wait>2.0
VBRUN>getdata,%fname%,%strow%,%erow%

[Guest]

Thanks for the reply.

When I adjusted my Macro I was able to pass the filename into it as suggested.

The problem is this: when ExecuteFile is run, the spreadsheet opens up and the focus is clearly within the open spreadsheet. But I can't get the VBA script to do anything with this spreadsheet INSIDE the VBA code. The Macro is giving me messages like "missing Object".

[support]

You are refering to non-existant objects, methods and properties which I assume belong to the Excel application. But nowhere in your script do you create or get these objects. In other words, your script does not know about the objects you are referring to - you have not told it where they come from.

e.g. you refer to "activeworkbook.name" but activeworkbook means nothing to VBScript. Activeworkbook is a property of the Excel object. So first you must create or get the excel object and then refer to it as a property of that object.

Also, by the way, this is VB SCRIPT - Visual Basic Scripting Edition - NOT VBA - Visual Basic for Applications. You cannot just take VBA from within Excel and put it into a VBScript and expect it to work for two reasons:

1) it is VBA not VBScript and the two may not necessarily share functions etc
2) VBA code that is within Excel knows about the Excel object, it's objects, methods and properties. Take that code OUT of Excel and those objects, methods and properties no longer exist unless you create or GET them from a running instance. It's a bit like expecting VBA code that runs in Excel to work in Access. Excel's objects that you refer to in Excel VBA dont exist in Access so VBA code for Excel won't work in VBA for Access *unless you explicitly create those objects or get them from a running instance*.