Jump to content

Excel_BookAttach doesn't attach to the given title?


Ricskal
 Share

Recommended Posts

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,

Link to comment
Share on other sites

6 minutes ago, FrancescoDiMuro said:

Hi @Ricskal, and welcome to the AutoIt forums :welcome:
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?

Link to comment
Share on other sites

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

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?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...