Jump to content
Sign in to follow this  
rosaz

_ExcelBookAttach using partial string

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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")

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 by MrMitchell

Share this post


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
Sign in to follow this  

×