How do you determine whether an excel workbook is open?

[LeighAnne]

I have written some code that exports calender information from outlook into an excel workbook. The workbook refresh's at set periods of time.

The problem I have is when the macro re-runs it opens another execl workbook, so I need some code that will determine whether or not it is already open.

Anyone got any ideas ? (Part of the code is below)


'Pick up the Excel document
strsheet = "C:\My Documents\Retail Tel.xls"
Set objExcelApp = Application.CreateObject("Excel.Application&quot

If "The workbook 'strSheet' is not open" Then
objExcelApp.workbooks.Open (strsheet)
end if
Set objExcelBook = objExcelApp.ActiveWorkbook
Set objExcelSheets = objExcelBook.Worksheets
Set objExcelSheet = objExcelBook.Sheets(1)
objExcelSheet.Activate

 

[xlbo]

For Each wb In Application.Workbooks
if wb.name = strSheet then
msgbox "Workbook Open"
else
msgbox "Workbook Not open"
end if
next

HTH Rgds
~Geoff~

 

[LeighAnne]

Geoff,

Thanks for the reply but I've already tried this.

I get "Run-time error 438, Object doesn't support this property or method"

Leigh-Anne

 

[dwilson01]

LeighAnne,

There is only one little thing that I saw as a possible problem with Geoff's code, and that is the wb.name doesn't give the path. This would cause the routine to never find a match for the open workbook and strSheet. Geoff's code works great in both excel 97 & 2000 with a slight modification (see below). It may be some other part of your code that is causing your problem. If you could post all off your code, we may be able to track down the problem. Also, it would be helpful to know the version of software.

strsheet = "C:\My Documents\Retail Tel.xls"

For Each wb In Application.Workbooks
if wb.path & "\" & wb.name = strSheet then
msgbox "Workbook Open"
else
msgbox "Workbook Not open"
end if
next

 

[rudo]

If your code is correct and still doesn't work, try to see if your libraries are activated. In the vb screen, you will find them in the menu Tools (I work with a Dutch version, where this menu is called Extra, so I am not 100 % sure you call it Tools in the english version. If you have a menu Tools, then it should be it, if not it's the 8th menu from the left !!! LOL) In the first option tou will find the libraries. Be sure at least the following are activated (mentioned versions or with higher version numbers if available):
VBA
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office 9.0 Object Library
Microsoft Forms 2.0 Object Library

I hope that will do, regards,

Rudo