Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,986
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count open workbooks before closing Les Excel Programming 2 June 25th 07 05:11 AM
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. san Excel Programming 1 January 3rd 06 04:22 AM
count number of workbooks open Ronaldo Excel Programming 2 April 21st 05 01:00 PM
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed Frank Jones Excel Programming 2 June 15th 04 03:21 AM
Workbooks.Open closes other workbooks S. Daum Excel Programming 1 August 21st 03 07:47 PM


All times are GMT +1. The time now is 02:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"