Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel Spreadsheet Names
#1
Is there a way to get a list of all the spreadsheets open in Excel? I can only get the worksheets.
An old blog on QM coding and automation.

The Macro Hook
#2
Macro Macro83
Code:
Copy      Help
ExcelSheet es.Init
Excel.Workbook b
foreach b es.ws.Application.Workbooks
,out b.Name
#3
thanks. I thought you could use a string to call the workbook; is that not the case?


Macro Macro
Code:
Copy      Help
ExcelSheet es.Init
Excel.Workbook b
_s="spreadsheet name.xlsx"
es.Init("worksheet name" 2 _s)
An old blog on QM coding and automation.

The Macro Hook
#4
gets sheet1 from workbook book3
Macro Macro1569
Code:
Copy      Help
ExcelSheet es.Init("sheet1" 2 "book3.xls")
#5
that's what I've been doing but as one of me spreadsheets changes each week and may not know how the name changes at the end, I was hoping to regex the prefix and then feed in the variable.
An old blog on QM coding and automation.

The Macro Hook
#6
Member function ExcelSheet.FindWorkbook
Code:
Copy      Help
function! $name [flags] [str&getName] [Excel.Workbook&getWorkbook] ;;flags: 2 wildcard, 4 beginning, 8 end, 16 partial, 32 rx

;Finds an open workbook.
;Returns 1 if found, 0 if not.

;name - workbook name, like "book1.xls". Case insensitive.
;flags - how to compare name. Default: must match exactly. For example, use flag 32 if name is regular expression.
;getName - variable that receives name of the found workbook.
;getWorkbook - variable that receives COM interface of the found workbook.

;NOTES
;The variable can be not initialized. Then this function calls Init().

;EXAMPLE
;ExcelSheet es; str name
;if(!es.FindWorkbook("book?.xls" 2 name)) end "not found"
;es.Init("" 2 name)


if(!ws) Init
Excel.Workbook b
foreach b ws.Application.Workbooks
,_s=b.Name
,if SelStr(flags|1 _s name)
,,if(&getName) getName=_s
,,if(&getWorkbook) getWorkbook=b
,,ret 1

err+ end _error
#7
Rats...everything seems to work just fine. I get a return of '1' on the 'FindWorkbook' function and the correct name of the spreadsheet gets passed but it doesn't actually pull Excel to the forefront (though when it is visible and another spreadsheet is showing I can see that it is switching the visible spreadsheet to the correct one just not bringing Excel up to the act place.

Shouldn't 'Init' still do that or did I monkey something up?
An old blog on QM coding and automation.

The Macro Hook
#8
flag 2 activated workbook in Excel. To activate Excel use act.
#9
Thanks!
will do.
An old blog on QM coding and automation.

The Macro Hook


Forum Jump:


Users browsing this thread: 1 Guest(s)