[RESOLVED] how to run VBA code from batch file

[scorpioy]

To run the Excel VBA code, the only way I know so far is to open the Excel file, click Alt+F8, then click 'run' button, or use Alt+F11, and run from there.

Is there any way to call and run it from batch file in Windows or cron in Linux?

Thanks.

[westconn1]

in windows you can run vb like code using the windows scripting host
any textfile saved with .vbs extension will run the code within, when double clicked, from this you can create objects to read /write file or registry, automate office applications, or other activex dlls

many standard vb functions and methods will work, but some have to be coded differently

[Hack]

How about taking it out of VBA and putting it into a language that would permit you to create a compiled .Exe?

[scorpioy]

I just tried to put my VBA code from Excel Module to an external file with extension as .vbs. But when I double click it, it pops up an error: Expected end of statement, Code: 800A0401. It says it is a compilation error.

It runs well inside Excel file. How come it is a compilation error when saved to an external file.

How should I solve this?

[Siddharth Rout]

I need to see your code...

I could be wrong but I feel you might be early binding. If yes, then switch to late binding...

Can I see your code?

[scorpioy]

My code is relatively simple. It's reading some input files and produce some output files.

Dim inputBook As Excel.Workbook
Dim outputBook As Excel.Workbook

Dim inputApp, outputApp As Excel.Application
Set inputApp = New Excel.Application

...

Set inputBook = inputApp.Workbooks.Open(inputFileName)

Is there some problem?

[Siddharth Rout]

Yeah I was right... you are early binding... you need to use latebinding

for example

Dim inputBook As Object
Dim outputBook As Object

Dim inputApp, outputApp As Object
Set inputApp = CreateObject("Excel.Application") 
.
.
. And so on..

Check put Si's tutorial here on late binding...

Hope this helps...

[scorpioy]

Thanks for the help.
I actually have another concern. My code is running against some configuration value I specifed inside the Excelf file itself. So when I copy the code out. I still want my code to read the configuration values. So is there a way to ask Excel file to run at all?

[Siddharth Rout]

I still want my code to read the configuration values.

Can you elaborate on this?

[scorpioy]

My program is reading and writing files. So I specified file paths and names in the Excel sheet, and I have VBA code in Modules to run on it.

During run time, my code will read in those file info from the Excel sheet itself, and execute accordingly.

It's like I put some constant values in the Excel sheet (and read them during execution), instead of defining constant inside the code.

Is this clear enough?

[westconn1]

once you create and object instance of excel you can open workbooks and basically do anything that you can do in excel code (with some differences), see below,
if you want to read or write to text files you need to use FSO as vb file io does not work in vb scripting

variables in vbs should not be typed

dim oxl, owb, osht
set oxl = createobject("excel.application")
set owb = oxl.workbooks.open(somepath&filename)
set osht = owb.sheets("mysheet")
msgbox osht.range("a1").value

you can also create an object of a specific workbook with out the excel object
set owb = createobject("somepathandfilename.xls")
or if the workbook is already open use
set owb = getobject("somepathandfilename.xls")