Excel - Select/change value for same cell in multiple sheets (if they exist) in same workbook Topic is solved

[jarhead]

I process multiple Excel files per day. Each workbook typically has two sheets. The first sheet is a cover page so I don't change anything. Cell B1 of the second sheet contains a SSN without dashes (333224444). I run the following script that takes the SSN and replaces the first five numbers with an X:

Code: Select all

x1 := ComObjActive("Excel.Application")
cell2 := x1.Sheets(2).Range("B1") 
cell2 .value := RegExReplace(cell2.text, "\d{5}", "XXXXX")

With a recent change to the generated Excel files I receive, workbooks can now have between two to four sheets. What is the best way to run the above script on all sheets beginning with the second sheet if they exist? There is always a second sheet but not always a third or fourth. So sheets 2-4 all contain the SSN in cell B1. Thanks!

[mikeyww]

Code: Select all

XL := ComObjActive("Excel.Application")
Loop, 3
 Try cell2 := XL.Sheets(A_Index + 1).Range("B1"), cell2.Value := RegExReplace(cell2.Text, "\d{5}", "XXXXX")

[flyingDman]

For all sheets (if they exist) other than the 1st one:

Code: Select all

xl := ComObjActive("excel.application")
for sheet in xl.sheets
	if (a_index > 1)
		sheet.range("B1").value := "xxxxx" substr(sheet.range("B1").text,6)

[jarhead]

Thank you both!

[flyingDman]

Because there is always an other way:

Code: Select all

xl := ComObjActive("excel.application")
for sheet in xl.sheets
	(sheet.index > 1) && sheet.range("B1").value := "xxxxx" substr(sheet.range("B1").text,6)