VBA; Excel2007; application.workbooks.count is not counting all open workbooks

Example Case with a personal.xlsb file in Excel's start-up folder, but closed:

Step 1: Close all open Excel application windows, even application windows
that have no visible workbooks.
Step 2: Click on the Excel icon in the quick-launch portion of the desktop
task bar.
Step 3: After an Excel application window opens with a workbook titled
''Book1", Click on the task bar's Excel icon again.
Step 3b: At this point, if a message box titled 'File in Use' appears and
says 'PERSONAL.xlsb is locked for editing', click the 'Read Only' button.
Step 4: After an Excel application window opens with a workbook titled
''Book2", open the Visual Basic editor window (alt-F11).
Step 5: After the Visual Basic editor window opens, enable the Immediate
window (ctrl-G).
Step 6: In the Immediate window, type 'msgbox application.workbooks.count',
then press enter.
Step 6b: At this point, the message box would display '2' on my PC. Click
the 'OK' button. (I think it should display '3')
Step 7: In the Immediate window, type 'msgbox
application.workbooks.item(1).name', then press enter.
Step 7b: At this point, the message box would display 'PERSONAL.xlsb' on my
PC. Click the 'OK' button.
Step 8: In the Immediate window, type 'msgbox
application.workbooks.item(2).name', then press enter.
Step 8b: At this point, the message box would display 'Book2' on my PC.
Click the 'OK' button.
Step 9: In the Immediate window, type 'msgbox
application.workbooks.item(3).name', then press enter.
Step 9b: At this point, the message box would display 'Run-time error '9':
Subscript out of range' on my PC. Click the 'OK' button. (I think this
iteration should of displayed 'Book1')

Can anyone else replicate this condition?
Greg

You can only get the workbook count in one instance of Excel at a time. The
way you described it, it looks like Book1 is in the first instance of Excel.
But I could be wrong.

"greg.campeau" wrote:

VBA; Excel2007; application.workbooks.count is not counting all open workbooks

Example Case with a personal.xlsb file in Excel's start-up folder, but closed:

Step 1: Close all open Excel application windows, even application windows
that have no visible workbooks.
Step 2: Click on the Excel icon in the quick-launch portion of the desktop
task bar.
Step 3: After an Excel application window opens with a workbook titled
''Book1", Click on the task bar's Excel icon again.
Step 3b: At this point, if a message box titled 'File in Use' appears and
says 'PERSONAL.xlsb is locked for editing', click the 'Read Only' button.
Step 4: After an Excel application window opens with a workbook titled
''Book2", open the Visual Basic editor window (alt-F11).
Step 5: After the Visual Basic editor window opens, enable the Immediate
window (ctrl-G).
Step 6: In the Immediate window, type 'msgbox application.workbooks.count',
then press enter.
Step 6b: At this point, the message box would display '2' on my PC. Click
the 'OK' button. (I think it should display '3')
Step 7: In the Immediate window, type 'msgbox
application.workbooks.item(1).name', then press enter.
Step 7b: At this point, the message box would display 'PERSONAL.xlsb' on my
PC. Click the 'OK' button.
Step 8: In the Immediate window, type 'msgbox
application.workbooks.item(2).name', then press enter.
Step 8b: At this point, the message box would display 'Book2' on my PC.
Click the 'OK' button.
Step 9: In the Immediate window, type 'msgbox
application.workbooks.item(3).name', then press enter.
Step 9b: At this point, the message box would display 'Run-time error '9':
Subscript out of range' on my PC. Click the 'OK' button. (I think this
iteration should of displayed 'Book1')

Can anyone else replicate this condition?
Greg

Thanks.
And thanks for the quick reply.
Greg

"JLGWhiz" wrote:

You can only get the workbook count in one instance of Excel at a time. The
way you described it, it looks like Book1 is in the first instance of Excel.
But I could be wrong.

"greg.campeau" wrote:

VBA; Excel2007; application.workbooks.count is not counting all open workbooks

Example Case with a personal.xlsb file in Excel's start-up folder, but closed:

Step 1: Close all open Excel application windows, even application windows
that have no visible workbooks.
Step 2: Click on the Excel icon in the quick-launch portion of the desktop
task bar.
Step 3: After an Excel application window opens with a workbook titled
''Book1", Click on the task bar's Excel icon again.
Step 3b: At this point, if a message box titled 'File in Use' appears and
says 'PERSONAL.xlsb is locked for editing', click the 'Read Only' button.
Step 4: After an Excel application window opens with a workbook titled
''Book2", open the Visual Basic editor window (alt-F11).
Step 5: After the Visual Basic editor window opens, enable the Immediate
window (ctrl-G).
Step 6: In the Immediate window, type 'msgbox application.workbooks.count',
then press enter.
Step 6b: At this point, the message box would display '2' on my PC. Click
the 'OK' button. (I think it should display '3')
Step 7: In the Immediate window, type 'msgbox
application.workbooks.item(1).name', then press enter.
Step 7b: At this point, the message box would display 'PERSONAL.xlsb' on my
PC. Click the 'OK' button.
Step 8: In the Immediate window, type 'msgbox
application.workbooks.item(2).name', then press enter.
Step 8b: At this point, the message box would display 'Book2' on my PC.
Click the 'OK' button.
Step 9: In the Immediate window, type 'msgbox
application.workbooks.item(3).name', then press enter.
Step 9b: At this point, the message box would display 'Run-time error '9':
Subscript out of range' on my PC. Click the 'OK' button. (I think this
iteration should of displayed 'Book1')

Can anyone else replicate this condition?
Greg