Switching between open Excel Workbooks

General
#1

I have been trying to switch between open Excel workbooks to transfer data and the way Iused to do it did not work in the end I turned to ChatGPT . Turned out using names of the open workbooks just did not work and I had to use their index. It. seems very cumbersome and I wondered if anyone had a better solution.

tell application "Microsoft Excel"
	-- List all open workbooks by index
	set workbookCount to count of workbooks
	--set targetWorkbookName to "MasterSummary.xlsx"
	set targetWorkbookName to "book2.xlsx"
	
	set foundWorkbookIndex to 0
	set workbookNames to {}
	
	-- Iterate over workbooks by index
	repeat with i from 1 to workbookCount
		set wbName to name of workbook i
		set end of workbookNames to wbName
		if wbName is targetWorkbookName then
			set foundWorkbookIndex to i
			exit repeat
		end if
	end repeat
	
	if foundWorkbookIndex is not 0 then
		-- Activate the workbook by index
		set active workbook to workbook foundWorkbookIndex
		-- Add a delay
		delay 0.5
		
		-- Verify and get cell A1 value
		set cellValue to value of range "A1" of active sheet of workbook foundWorkbookIndex
		return cellValue
	else
		display dialog "Workbook '" & targetWorkbookName & "' is not open. Open workbooks: " & workbookNames as string
	end if
end tell
#2

Try this:

tell application "Microsoft Excel"
	
	set workbookNames to name of workbooks -- list of open workbook names
	set targetWorkbookName to "book2.xlsx" -- workbook to bring to front
	
	-- is target workbook open	
	if workbookNames contains targetWorkbookName then
		activate object workbook targetWorkbookName -- bring target workbook to front
	else
		-- if target workbook is not open, display list of open workbook names
		set AppleScript's text item delimiters to linefeed
		set paraNames to workbookNames as text
		tell me to display dialog "Workbook '" & targetWorkbookName & "' is not open." & linefeed & "Open workbooks: " & linefeed & paraNames as text
		set AppleScript's text item delimiters to {""}
	end if
end tell
#3

Perfect thank you. One line compared to what I had posted and believe me that was after hours of trying with ChatGPT.