Jump to content

_ExcelBookAttach using partial string

Recommended Posts

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")

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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"
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
If Not $return Then $return = 0
$oXLWorkbooks = ""
$oXLApp = ""
Return $return

Edit: Added AutoIt tags around code

Edited by MrMitchell
Link to post
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...