Excel2007; workbooks.count is not counting all open workbooks
 

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

Excel2007; workbooks.count is not counting all open workbooks
 

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

Excel2007; workbooks.count is not counting all open workbooks
 

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