Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run XLS Macros from inside a VB script
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run XLS Macros from inside a VB script
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run XLS Macros from inside a VB script
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run XLS Macros from inside a VB script
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("A1:D1").Bold = True ws.Range("A2:D2").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 wrote: 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" wrote: 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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run XLS Macros from inside a VB script
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 "Andrew Taylor" wrote: 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("A1:D1").Bold = True ws.Range("A2:D2").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 wrote: 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" wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros inside Excel | Excel Worksheet Functions | |||
solving inside macros | Excel Programming | |||
How do I refer to other macros inside a macro code? | Excel Programming | |||
Macros inside macros, and pasting into macro code. | Excel Programming | |||
Anyone know how to do an EXCEL Script - details inside | Excel Programming |