Counting Open work books

Very simple i am sure all i need is a bit of code to count the number of open workbooks.

Incase you are curius why i am finishing my dad's work off, being as he uses the command to open the previous workbook if there are any other workbooks open when it starts it will crash part the way threw running, so i am adding a message which pops up if there are other workbooks open and then close them if i can be bothered.

Quote:

---

Originally Posted by machinist

Very simple i am sure all i need is a bit of code to count the number of open workbooks.

Incase you are curius why i am finishing my dad's work off, being as he uses the command to open the previous workbook if there are any other workbooks open when it starts it will crash part the way threw running, so i am adding a message which pops up if there are other workbooks open and then close them if i can be bothered.

---

Have you tried this:

VB Code:

Workbooks.Count

Cheers,

RyanJ

Its best to use GetObject and CreateObject since Workbooks.Count will only detect workbooks that are opened inside a particular Excel instance.
If they are all opened from the start menu and not Excel's Open or New menu then it will not work.

VB Code:

Private Sub Form_Load()
    On Error Resume Next
    Dim oApp As Object
    Set oApp = GetObject
    If Not oApp Is Nothing Then
        'Attached to previous instance
    Else
        Set oApp = CreateObject("Excel.Application")
    End If
End Sub

May have an error since I typed it into the reply box and not VB. :)

Quote:

---

Originally Posted by RobDog888

Its best to use GetObject and CreateObject since Workbooks.Count will only detect workbooks that are opened inside a particular Excel instance.
If they are all opened from the start menu and not Excel's Open or New menu then it will not work.

VB Code:

Private Sub Form_Load()
    On Error Resume Next
    Dim oApp As Object
    Set oApp = GetObject
    If Not oApp Is Nothing Then
        'Attached to previous instance
    Else
        Set oApp = CreateObject("Excel.Application")
    End If
End Sub

May have an error since I typed it into the reply box and not VB. :)

---

Thanks for that tip, I'm just starting to try and learn VBA so just about everything is new too me and I love it :D

Cheers,

RyanJ

No prob. :D

The Workbooks collection is a bit misleading. It all depends on how the user opens the workbooks. When I first started with it I thought it contained
a collection of all instanciated workbooks, but not. :( It would have made things a bit easier if it was.