How to run XLS Macros from inside a VB script

[Sam Raymond]

I'm using a VBS script to generate a CSV file.
I'd like to tune up the script in such a way that it will format the output
so that it opens nicely in XL. To do so, I've recorded a macro for
formatting the data, but the problem is that the file gets re-written each
time I run the VBS script.

I'd like to know if I can write the code from the XL macro directly into the
VBS script. Anyone know how to do this?

In the past, I've been able to open an existing spreadsheet and run an
existing macro using a VBS script, but this is different, because the output
file is overwritten each time the data is generated, so I can't store a macro
inside the spreadsheet. I need to store the 'macro' inside the VBS code.

Thanks! Sam

 

[Andrew Taylor]

You can use CreateObject to create an instance of Excel,
which has all the functionality you need:

set XL = CreateObject("excel.application")
XL.visible = True ' if you need to see what's happening
set wb= XL.workbooks.open("myfile.csv")
' etc etc
XL.Quit

 

[Sam Raymond]

Andrew,

Thanks so much for the reply.

Actually, I've gotten through that part already. I can either launch the XL
file behind the scenes, or make it viewable. My problem lies in the ETC area
of your thread.

I'm trying to figure out how to launch some macro-like code from inside the
VB script.

I can't store the macro inside the spreadsheet, because it's a new sheet
each time. The macro-like code I'm writing is basically formatting type
information.

So, I'm going to launch XL like you mention, then I want to run the
macro-like VB. I can handle the rest...

 

[Andrew Taylor]

Sam,
Sorry not to have been more explicit - as an example of fomatting you
could do something like:

set wb= XL.workbooks.open("myfile.csv")
set ws = wb.sheets(1)
ws.Range("A11").Bold = True
ws.Range("A22").Italic = True
ws.Range("C3") = "Hello, world"


... so the wb and ws objects have exactly the same properties and
methods that they would have if they were defined within a VBA
macro.

Hope this makes sense - ask again if you're still having trouble.

Andrew

 

[Sam Raymond]

Andrew,

Thanks again for the followup.

I'm still battling through the syntax, but you've now gotten me on the right
track. Thanks so much for the suggestions.

Regards,

Sam Raymond