VBScript and XLCreate/Quit

hoangvo81 · Post by **hoangvo81** » Wed Mar 07, 2012 1:08 am

Hi,

was wondering if anyone can put some input on this:
Is there a simplier way to color a cell in MS other than going to call the VBScript to color the cell?

Currently i have to open up Excel in MS, add values to the cell, close it, call vbscript with the file name, rows, column, sheet - which then color that using Interior.ColorIndex, it then have and quit before the end of the subroutine.

Any inputs on having MS open up the file and input the data and color code the cell it just input the values into?

or any suggestion on better way to handle this?

Best I can think of without having to open/close excel over and over every time there's data input and color coding it would be to write the row/colums/sheetname into a text file and then at the end of it to read from the text file to pass the value to vbscript to color code it.

Thanks

Post by **Marcus Tettmar** » Wed Mar 07, 2012 4:42 pm

Firstly, why do you need to close and open the Excel file each time? Why can't you keep the Excel file open? Then just issue the VBScript code (presumably you are referring to VBScript code which access the Excel object model) to color the cell.

hoangvo81 · Post by **hoangvo81** » Wed Mar 07, 2012 5:02 pm

Thank for the help via chat
with what we disuss it should work
i m going to try it out.

hoangvo81 · Post by **hoangvo81** » Wed Mar 07, 2012 5:50 pm

marcus,

any suggestion to grab a workbook if there is 2 active workbook open?

Thanks,

Post by **Marcus Tettmar** » Wed Mar 07, 2012 5:52 pm

See:

http://support.microsoft.com/kb/288902

According to that you can use e.g.:

Set xlApp = GetObject("Book2").Application

Where Book2 is the workbook name.

hoangvo81 · Post by **hoangvo81** » Wed Mar 07, 2012 6:13 pm

that works, well.
this way i can keep both open and have the vbscript do all coloring while looping through our data and make records into xlsx

thanks for the help