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.

Any advice?


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"

3 Answers

  • 1 decade ago
    Favorite Answer

    Do this

    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

    ' line1

    ' line2

    end if


    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

    Enjoy it

    VBAXLMan is always here to fill your Excel needs

  • 1 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?

  • Anonymous
    4 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"

Still have questions? Get your answers by asking now.