VB in Excel: Testing whether a Workbook is open?
In itself, this would be easy, BUT .....
1) I don't know in which folder the User keeps that workbook, and
2) The workbook name is only partly known, the rest is wild-card.
That's why - in my code - I ask the user to open one of the workbooks, so that I can determine the path name.
But I need to check whether he/she really did. In plain English, I try to test:
If Workbook "<unknown path-name>\Payroll for <wild-card>.xls" is open, then determine its path....else complain....
I know that wild-card should be " * ", but "\Payroll*.xls" doesn't seem to work.
Hi "Wino", nice to see you again...
Yes, that would work, if I go through 365 possibilities, because the wildcard is actually a date, like "Payroll - Mar 17, 2009.xls"
- VBAXLManLv 61 decade agoFavorite Answer
For each BFile in Workbooks
NameS="Payroll for "
If UCase(Left( BFile.Name,Len(NameS))) = UCase(NameS) then
' Here we go, I found an opened workbook that meets the criteria
Change the NameS to yours, I already did it as you wanted
And add the lines to what to do if the macro finds one
VBAXLMan is always here to fill your Excel needs
- DragonsOfWineLv 71 decade ago
Would it work if you get the workbooks count value and then iterate through each workbook (excelapp.workbooks(index)) and check the name property of each?
- Anonymous4 years ago
Dim ws As Excel.Worksheet Dim sSheetName As String sSheetName = "My Sheet" With Excel.Workbooks("Book1") On blunders Resume next Set ws = .Sheets(sSheetName) On blunders GoTo 0 'Reset blunders checking If ws Is no longer something Then 'Create "My Sheet" worksheet Set ws = .Sheets.upload( After:=.Sheets(.Sheets.count variety)) ws.call = sSheetName end If: end With After this code runs, the object variable "ws" is the prevailing worksheet "My Sheet" or a sparkling worksheet called "My Sheet"