• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

VB.NET, Excel, Tracking Multiple Instances of

I want to do something like this:

Dim appExcel As Excel.Application
Dim wBook As Array of Excel.Workbook  ' wrong
Dim wSheet As Excel.Worksheet

appExcel = openExcel()
wBook.Add (objXL.Workbooks.Add)           ' wrong
wSheet = wBook.Item(0).Worksheets(1)    ' wrong

Friend Function openExcel() As Excel.Application
        Dim appExcel As Excel.Application
        On Error Resume Next
        appExcel = GetObject(, "Excel.Application")
        If Err.Number = 0 Then
        Else
            appExcel = CreateObject("Excel.Application")
            Err.Clear()
        End If
        On Error GoTo 0
        Return appExcel
End Function

but that is obviously not correct. I will not know how many workbooks need to be created as part of the overall application. The openExcel part works fine and I can restrict opening of excel to one process but I can't seem to find the right way to define a array / collection / whatever of excel.workbook's.

This is in relationship to question posed in http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20947041.html

Help.
0
Knomaze
Asked:
Knomaze
  • 2
  • 2
1 Solution
 
Arthur_WoodCommented:
Notice how the Excel object was used in the other question:

        Dim objXL As Excel.Application
        Dim objWB As Excel.Workbook
        Dim objWS As Excel.Worksheet

        objXL = New Excel.Application
        objWB = objXL.Workbooks.Add
        objWS = objWB.Worksheets(1)

you do NOT create a direct instance of the WorkBooks object itself, in fact you CAN'T.  Rather you use the internal instance of the WorkBooks object as a FACTORY class, to create individual Workbook objects using the WorkBooks.Add method.  Does that make it clearer for you?

AW
0
 
KnomazeAuthor Commented:
Nope - This part I already know as its my code.

I want to keep track of the workbooks that I open and uniquely identify them without having to save them to disk first. I'd like to be able to track of them in a list or array or some such collection that I can simply just add additional elements to or take elements away from as I add and remove workbooks from the excel application.

I'd love to just be able to say

Dim objWB() As Excel.WorkBook

and just keep adding workbooks to the array but I see that VB.NET doesn't allow you to do this even for basic element types like strings - or at least not that I've been able to get to work. Unfortunately my VB books which explains alot of this stuff are sitting where I can't get to them and the online resources for VB.NET seem non-existent.
0
 
Arthur_WoodCommented:
create objWB as an ArrayList  and then you can add instances of the WorkBook object to the ArrayList. like this:

Dim objWB as New ArrayList

objWB.Add(objXL.Workbooks.Add)
0
 
KnomazeAuthor Commented:
WOO HOO!

I thought I tried that already - must have mis-typed something because it certainly wasn't working yesterday. Seems to be okay now - user error I guess.

Thanks,
K
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now