Excel_BookAttach doesn't attach to the given title?

[Ricskal]

Hello everyone,

First time I post here, most of my issue's I could solve by searching this forum so thank you. I want to read a cell in the excelsheet I'm curently working in. I have a hyperlink to my script in the excelsheet itself. What it does:

1. Get title of active window.

2. Attach to workbook with that title.

3. Read cell b1 on the atcive sheet in that workbook.

local $vTitle = WinGetTitle("[ACTIVE]")
$oWorkbook = AttachWorkbook($vTitle)
$vResult = ReadWorkbook($oWorkbook, "B1")

Func AttachWorkbook($vTitle)
   Local $oWorkbook = _Excel_BookAttach($vTitle, "Title")
   If @error Then
      MsgBox(0, "Error", "Error attaching to workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      _Excel_BookClose($oWorkbook)
      Exit
   EndIf
      Return($oWorkbook)
EndFunc

Func ReadWorkbook($oWorkbook, $vExcelCel)
   Local $vResult = _Excel_RangeRead($oWorkbook, Default, $vExcelCel)
   If @error Then
      MsgBox(0, "Error", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      _Excel_BookClose($oWorkbook)
      Exit
   EndIf
      Return($vResult)
EndFunc

But when I have multiple workbooks open it attaches to another workbook with a different title?

Does someone have an idea what's going wrong?

Kind regards,

[FrancescoDiMuro]

Hi @Ricskal, and welcome to the AutoIt forums 
You could use _Excel_BookList() to get a list of all opened Workbooks.
In this way, you can then iterate through the returned array and do whatever you want
 

[Ricskal]

6 minutes ago, FrancescoDiMuro said:

Hi @Ricskal, and welcome to the AutoIt forums 
You could use _Excel_BookList() to get a list of all opened Workbooks.
In this way, you can then iterate through the returned array and do whatever you want
 

Thank you! So list all workbooks, search the array with the active window title and attach not with the title but with the file path? I will try that.

Do you know what is currently going wrong though? The function is described as: "Attaches to the first instance of a workbook where the search string matches based on the selected mode". I feel like I'm doing exactly that?

[Ricskal]

Alright, this is what I made of it and it works! Thank you for the listbook suggestion @FrancescoDiMuro

expandcollapse popup

Local $oWorkbook
Local $vResult
local $vFilename
Local $aWorkbookList
Local $vFilePath
local $vTitle

$vTitle = WinGetTitle("[ACTIVE]")
$vFilename = StringRight($vTitle,StringLen($vTitle)-18) ;I remove "Microsoft Excel - " from the title.
$aWorkbookList = ListExcelWorkbooks()
For $i = 0 To UBound($aWorkbookList) -1
   If StringRegExp($aWorkbookList[$i][1], $vFilename & ".*") Then
      $vFilePath = $aWorkbookList[$i][2] & "\" & $aWorkbookList[$i][1]
      $oWorkbook = AttachWorkbook($vFilePath)
      ExitLoop
   EndIf
Next
$vResult = ReadWorkbook($oWorkbook, $vExcelCel)

Func ReadWorkbook($oWorkbook, $vExcelCel) ;Read from workbook. Choose the open sheet, specific cell.
   Local $vResult = _Excel_RangeRead($oWorkbook, Default, $vExcelCel)
   If @error Then
      MsgBox(0, "Error", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      _Excel_BookClose($oWorkbook)
      Exit
   EndIf
      Return($vResult)
EndFunc

Func AttachWorkbook($vFilename) ;Attach to open workbook based on title.
   Local $oWorkbook = _Excel_BookAttach($vFilename)
   If @error Then
      MsgBox(0, "Error", "Error attaching to workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      _Excel_BookClose($oWorkbook)
      Exit
   EndIf
      Return($oWorkbook)
   EndFunc

Func ListExcelWorkbooks() ;Lists and returns a list of all open Excel workbooks.
   Local $aWorkbookList = _Excel_BookList()
   If @error Then
      MsgBox(0, "Error", "Error listing Excel workbooks." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      _Excel_Close($oExcel)
      Exit
   EndIf
      Return($aWorkbookList)
EndFunc

I still don't understand why attach with the title doesn't work though?

[FrancescoDiMuro]

@Ricskal
Sorry if I didn't reply to your previous answer, but I'm a little busy.
Happy to have helped

Edited February 18, 2019 by FrancescoDiMuro