VB in Excel: Testing whether a Workbook is open?

Question

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?

Update:

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"

VBAXLMan · Accepted 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
Next

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

Anonymous · Answer

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 · Answer

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"

Trending News

VB in Excel: Testing whether a Workbook is open?

3 Answers