_ExcelBookAttach using partial string

[rosaz]

Hello,

Does anyone know a way to attach to an Excel book when I only know the first part of the name of the workbook - i.e. "MyWorkbook version ****.xlsx"?

Thanks!

[Reg2Post]

If using the _ExcelBookAttach, according to the function notes and help file:

Set the $s_mode parameter to "filename" for the workbook name or "title" for the application.caption (Title of the Excel Window).

Example: _ExcelBookAttach("MyWorkbook.xls", "Filename")

[rosaz]

Hmm, this doesn't seem to be working. It works when I type the full name of the workbook in, but I'd like it to allow the end of the file name to vary.

Also - I forgot to add this before - I would just grab the active Excel window, but there will be two spreadsheets open - the other spreadsheet with a defined name that makes it easy to attach, but which means I can't just attach the active Excel window to the variable-name spreadsheet.

[Reg2Post]

Misunderstood the question and didn't know you meant wildcards with the "****" and do not think that you can pass those into the string value for the function since it will just be part of the filename which are illegal file name characters. But you probably already knew that.

Maybe do a StringRegExp or StringInStr check first?

[MrMitchell]

Here's my stab at it... let me know if you have questions. The idea is you basically ask Excel Application to tell you which Workbooks are currently open and they are returned as filenames. There are any number of better ways to do it but hopefully you get the idea.

Dim $sTitleToMatch = "MyWorkbook version 4.2.2.1"
MsgBox(0, "", _FindWorkbook($sTitleToMatch))
;_FindWorkbook($sTitleToMatch) should return either a 0 which is no match found
;... or it will return a "Filename" which you can use in the _ExcelBookAttach function
Func _FindWorkbook($stringToMatch)
;Finds the first occurence of workbook that has $stringToMatch anywhere in it's filename
;You can tweak this to match based on a regex or whatever...
Local $oXLApp, $oXLWorkbooks, $sWorkbookName, $return
$oXLApp = ObjGet("", "Excel.Application")
$oXLWorkbooks = $oXLApp.Workbooks
For $oXLWorkbook In $oXLWorkbooks
  $sWorkbookName = $oXLWorkbook.Name
  ;Uncomment the next line if you want to see each workbook name it finds
;~   MsgBox(0, "", $sWorkbookName)
  If StringInStr($sWorkbookName, $stringToMatch, 0) Then
   $return = $sWorkbookName ;Returns 'FileName' used in _ExcelBookAttach
   ExitLoop ;Exit loop after we find a match
  EndIf
Next
If Not $return Then $return = 0
$oXLWorkbooks = ""
$oXLApp = ""
Return $return
EndFunc

Edit: Added AutoIt tags around code

Edited March 26, 2012 by MrMitchell