Excel COM - changing spreadsheet names & tab colors Topic is solved

[jarhead]

I have a script that, after generating an Excel workbook, changes the name of the second spreadsheet depending on the name of the spreadsheet and also changes the color tabs of multiple spreadsheets. My tabs in my workbook typically look like this:

image.png (2.91 KiB) Viewed 221 times

After the scripts are run, the tabs look like this:

image.png (4.53 KiB) Viewed 221 times

The Cover Page and PAYMENT INSTRUCTIONS sheets always exist. The 123 sheet represents a facility and there is always one facility (the number varies depending on the facility) but each facility included in the Workbook gets its own tab identified by the facility number. The DESCRIPTION sheet may or may not exist.

I'm trying to figure out the best way to check all the spreadsheet names and act on each sheet depending on the name. So, if the worksheet is a three digit number, it means its a facility so I would want to change it to the facility name (there are 10 different facilities) and change the tab color. I leave the Cover Page sheet as is. I change the PAYMENT INSTRUCTIONS tab and DESCRIPTION tab (if it exists) to different colors.

Currently, for 123 sheet, I use the following to change it from the number value assigned the facility to the facility name and changes the tab color:

Code: Select all

appExcel := ComObjCreate("Excel.Application")
MC := appExcel.sheets(2).name

if (MC="123")
{
	appExcel.sheets(2).name := "The General Building"
	appExcel.Sheets(2).Tab.Color := 0xFF901E
}

else if (MC="456")
{
	appExcel.sheets(2).name := "Another Building"
	appExcel.Sheets(2).Tab.Color := 0x00FFFF
}

For the PAYMENT INSTRUCTIONS and DESCRIPTION tabs, I use the following:

Code: Select all

appExcel.Sheets("DESCRIPTION").Tab.Color := 0x32CD32
appExcel.Sheets("PAYMENT INSTRUCTIONS").Tab.Color := 0xFF901E

So I'm looking for the best way to combine into one script. So whether there is one facility or three, it would change the name & tab color and if the DESCRIPTION sheet exists, changes the tab color.

[Datapoint]

Here's one way to do it by looping through the sheets.
You don't need the arrays though, I just used them to organize it a bit. You could replace the array with if-statements or switch. if (sht.Name = x)...

Code: Select all

...

tabs := { 123: ["The General Building", 0xFF901E]
		, 456: ["Another Building", 0x00FFFF]
		, "DESCRIPTION": [false, 0x32CD32]
		, "PAYMENT INSTRUCTIONS": [false, 0xFF901E]}

for sht in appExcel.Sheets {
	if (tabs[sht.Name]) {
		sht.Tab.Color := tabs[sht.Name].2
		if (tabs[sht.Name].1)
			sht.Name := tabs[sht.Name].1
	}
}

[jarhead]

Thank you @Datapoint... I knew I was overthinking it. Works perfectly.