Solved

Get worksheet names...

Posted on 2004-09-09
22
1,337 Views
Last Modified: 2010-07-27
C:\Data.xls is open.

From vb 6 form, I want to read worksheet names in this open instance of C:\Data.xls

Note: I don't want to open another instance of it to read the worksheet names.

Thanks.
0
Comment
Question by:Mike Eghtebas
  • 8
  • 5
  • 4
  • +2
22 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 12021496
Hi eghtebas,

Use

Set appExcel = GetObject(,"Excel.Application")

This will get the active instance of excel. You can then use:

For Each sht In appExcel.ActiveWorkbook
 Msgbox sht.name
Next

To show the worksheet names.

Tim Cottee
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12021775
Hi Tim,

Is there any way for us to choose what application.  To illustrate, say we have two Excel Object open.

C:\File1.xls, C:\File2.xls , and C:\File3.xls in Excel Object-1 (Excel Object-1 has three workbooks in it).

C:\File4.xls is open in Excel Object-2 (Excel Object-2 has only one workbooks in it).

And, we don't know which one ia active (Excel Object-1 or Excel Object-2).

With this setup and assumption, I would like to read the worksheets names in C:\File2.xls (which happens to be in Excel Object-1.  Also, Excel Object-1 may or may not be active).

I increased points to 500 for change of scope.

Best Regards,

Mike
0
 
LVL 34

Expert Comment

by:flavo
ID: 12022803
Hey Mike!

Function GetWS()

Dim xl As Object
Dim ws As String
Dim oSheet As Object

ws = "test.xls"

Set xl = GetObject(, "Excel.application")

For Each oSheet In xl.workbooks(ws).sheets
    Debug.Print oSheet.Name
Next


End Function

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12022804
change "text.xls" to the name of the workbook to get the sheets in

Dave
0
 
LVL 15

Accepted Solution

by:
ameba earned 500 total points
ID: 12022974
GetObject alone cannot be used to select app.
When Excel application is started, it registers itself in the global Running Object Table (ROT).
The task might be possible using Matthew Curland's code from his book "Advanced Visual Basic 6: Power Techniques for Everyday Programs", or using something like this (from devx newsgroup), it's for Word:
----------------------------------------------------------------------
You will need a type library with the ROT interfaces (the OLELIB.TLB that's
in my site have those interfaces). Once you have the type library, you can
use the following code to enumerate all Word.Application objects and save
the documents:

Dim oROT As IRunningObjectTable
Dim oEnmMk As IEnumMoniker
Dim oMK As IMoniker
Dim oBC As IBindCtx
Dim lPtr As Long
Dim sName As String
Dim oApplication As Word.Application
Dim oDocument As Word.Document

   ' Get the ROT object
   Set oROT = GetRunningObjectTable

   ' Create a bind context object
   Set oBC = CreateBindCtx

   ' Get the ROT enumerator
   Set oEnmMk = oROT.EnumRunning

   ' Enumeate all registered objects
   Do While oEnmMk.Next(1, oMK) = 0

      ' Get the object name
      lPtr = oMK.GetDisplayName(oBC, Nothing)
      sName = SysAllocString(lPtr)
      CoTaskMemFree lPtr

      ' !{000209FF-0000-0000-C000-000000000046} is
      ' the Word.Application object
      If sName = "!{000209FF-0000-0000-C000-000000000046}" Then

         ' Get the object instance
         Set oApplication = oROT.GetObject(oMK)

         ' Save each document
         For Each oDocument In oApplication.Documents
            oDocument.Save
         Next

      End If

   Loop

   Set oROT = Nothing

--
Eduardo A. Morcillo (MS-MVP)
----------------------------------------------------------------------

his site: http://www.mvps.org/emorcillo/vb6/index.shtml
0
 
LVL 10

Expert Comment

by:anv
ID: 12023655
here's the code..

uses ADOX and ADO

Dim ax As ADOX.Catalog 'Stores the Details of a workbook
Dim xl As ADOX.Tables 'Stores all worksheets
Dim tl As ADOX.Table 'Represents single worksheet
Dim cn As ADODB.Connection 'Connection to excel workbook

Private Sub Command1_Click()
 Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\abc.xls;" & _
"Extended Properties=Excel 8.0;"
    .Open
End With
Set ax = New ADOX.Catalog
ax.ActiveConnection = cn
Set xl = ax.Tables
For Each tl In xl
    MsgBox tl.Name
Next
End Sub
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12023715
Hi ameba,

I located OLELIB.TLB and OLELIB2.TLB but cann't find regtlib.exe to register them a instruction requires.  Where can I possibly find regtlib.exe?

Re:> You will need a type library with the ROT interfaces ...

Does this mean I have to declare something in a module?

Lastly, this -->  "!{000209FF-0000-0000-C000-000000000046}"
being for Word, What is the one for excel?  I guess I could find it using:

.
.
CoTaskMemFree lPtr

MsgBox sName          '<------ here

Set oApplication = oROT.GetObject(oMK)
.
.

Regards,

Mike


0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12023723
Hi anv,

You have very interesting piece of code.  I am trying it now.

Thanks,

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12023753
Btw, how can I find field info (name and datatype) for tables:

ADOX.Catalog 'Stores the Details of a workbook
ADOX.Tables 'Stores all worksheets

Using a table def?

Also, I guess I have to add to my references as well, yes?

I owe you a case of beer now.

Mike
0
 
LVL 10

Expert Comment

by:anv
ID: 12023772
>Also, I guess I have to add to my references as well, yes?
yes the files reference fro ADOX is Microsoft ADO ext. 2.5 for DDL and security

u can use this for name and type of fields...

tl.Columns(i).Properties(0).Name & tl.Columns(i).Properties(0).Type
0
 
LVL 10

Expert Comment

by:anv
ID: 12023776
btw
thanx for "I owe you a case of beer now."

:))
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12024073
Hi anv,

Would I be adding a loop to read the field names?

Dim i as integer
Set xl = ax.Tables
For Each tl In xl

    Debug.Print  tl.Name    

    for i=1 to 10          '<-- would do trail and error to find upper limit
                                '      unless I could use 'While...' somehow

        Debug.Print tl.Columns(i).Properties(0).Name & tl.Columns(i).Properties(0).Type

   next i
Next

You may want to take a look at these question:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21123461.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21123590.html

Thanks,
0
 
LVL 34

Expert Comment

by:flavo
ID: 12024186
For i = 1 to tl.columns.count

 Debug.Print tl.Columns(i).Properties(0).Name & tl.Columns(i).Properties(0).Type


Not sure off hand if Columns(i) starts at 0 or 1 so it may be

for i = 0 to tl.columns.Count -1


Dave
0
 
LVL 10

Expert Comment

by:anv
ID: 12024200
hi eghtebas
yes u'll need to add a loop...

as given by flavo... the Columns(i) starts at 0

u'll have to loop thu all columns of all worksheets..

cheers
0
 
LVL 34

Expert Comment

by:flavo
ID: 12024218
thought so (wasnt 100% sure)

full code

Dim i as integer
For Each tl In xl

    Debug.Print  tl.Name    

    for i= 0 to tl.columns.count -1

        Debug.Print tl.Columns(i).Properties(0).Name & tl.Columns(i).Properties(0).Type

   next i
Next
0
 
LVL 15

Expert Comment

by:ameba
ID: 12025412
Hi eghtebas,

To register type library, add it to VB references using Project, References dialog. If that doesn't work, or if you have more than one olelib.tlb file, you can register or unregister type library using code from:
http://www.devx.com/vb2themax/Tip/18443
    UnregisterTypeLib "d:\program files\edanmo's vb page\shell extensions\olelib.tlb"

Clsid can be determined using utility like IROTViewer (included in Visual Studio), or using code - in previous sample change declaration from Word.Application to "As Object" and check type of object - if TypeOf oApplication is Excell.Application, you can print sName.
Here is another sample, hope it can help to get what you want:

Private Sub Form_Click()
    Dim oROT As IRunningObjectTable
    Dim oEnmMk As IEnumMoniker
    Dim oMK As IMoniker
    Dim oBC As IBindCtx
    Dim lPtr As Long
    Dim sName As String
    Dim obj As Object
    Dim oExcel As Excel.Application, oWb As Excel.Workbook, sht As Excel.Worksheet

    ' Get the ROT object
    Set oROT = GetRunningObjectTable
   
    ' Create a bind context object
    Set oBC = CreateBindCtx
   
    ' Get the ROT enumerator
    Set oEnmMk = oROT.EnumRunning
   
    ' Enumeate all registered objects
    Do While oEnmMk.Next(1, oMK) = 0
        Set obj = oROT.GetObject(oMK)
        If TypeOf obj Is Excel.Application Then
            Set oExcel = obj
            Print oExcel.Workbooks.Count & " workbooks:"
            For Each oWb In oExcel.Workbooks
                Print oWb.FullName
                If oWb.IsAddin Then
                    Print , "addin"
                Else
                    For Each sht In oWb.Worksheets
                        Print , sht.Name
                    Next
                End If
            Next
        End If
    Loop
    Set oROT = Nothing
End Sub
0
 
LVL 15

Expert Comment

by:ameba
ID: 12025599
No, sorry, that doesn't work correctly, it needs more testing.  I suggest checking the sName for each item returned by enumerator and more experiments...
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12029144
I was luck to have such a experties on this question:

Tim,
I didn't get feedback from you on having few excel objects each with number of workbook not knowing what workbook is active.

anv,
I was and am very exited about your solution.  My exitment is because I have been opening a worksheet to extrat its property setting; and here it seemed, I will be able to read them all off of a table directly (although I had to load the workbook itself  first).  I will post a new question to follow up with your solution (I am not getting any field names, etc.).

Dave,
Thank you for your input on the solution provided by anv.  Please participate on the new question I will post shrtly (will provide a link).

ameba,
Although no solution at hand, but I think your answer is the closest solution as far as this question is conrned.  I will have some additional question later on (will post new questions).

With Regards

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12029160
Hi anv,

I still owe you a case of beer.  Send an email in my profile.

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 12029607
On this link (unrelatred to above question):

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21126594.html

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA"(ByVal lpClassName As String, ByVal lpWindowName As Long) As Long

Public Sub RemoveAllExcelFiles()

On Error Resume Next
While CBool(FindWindow("XLMAIN",0))               \
Set m_app = GetObject(,"Ecel.Application")          |
'If m_app.visible=False Then                               |
    m_app.Quit                                                    >  When I remove 'exit sub line, it will loop While CBool...Wend
'End If                                                              |    forever, if I include this line...  this is the cause of the proplem above
'exit sub                                                           |    (maybe?)
Wend                                                              /

End Sub

I think this code could be utilized in solving the question on this thread.  What do you think?
0
 
LVL 15

Expert Comment

by:ameba
ID: 12029804
Thanks :-)
0
 
LVL 15

Expert Comment

by:ameba
ID: 15139656
Hi!  The code for Excel (http:#12025412) didn't work properly, it returned only one Excel, here is why:

Theoretically, you can iterate the ROT for each individual instance, but
Office applications do not register themselves if another instance is
already in the ROT because the moniker for itself is always the same, and
cannot be distinguished. This means that you cannot attach to any instance
except for the first. However, because Office applications also register
their documents in the ROT, you can successfully attach to other instances
by iterating the ROT looking for a specific document, attaching to this
document, and then getting the Application object from this document.
Ref: http://www.dotnet247.com/247reference/msgs/55/277921.aspx

Updated code:

' add reference to olelib.tlb and Excel
' Form1 code
Option Explicit

Private Sub Form_Click()
    Dim oExcel As Excel.Application, oWb As Excel.Workbook, oSht As Excel.Worksheet
    Dim col As Collection, s As String
   
    Set col = GetExcelApps() ' get collection of Excel applications
   
    s = col.Count & " Excel Applications:" & vbCr
    For Each oExcel In col
        s = s & vbCr & " - " & oExcel.Workbooks.Count & " Workbooks:" & vbCr
        For Each oWb In oExcel.Workbooks
            s = s & "    " & oWb.Name & " has " & oWb.Worksheets.Count & " worksheets:"
            For Each oSht In oWb.Worksheets
                s = s & " " & oSht.Name
            Next
            s = s & vbCr
        Next
    Next
    MsgBox s
End Sub

' returns collection of Excel applications
Function GetExcelApps() As Collection
    Dim oROT As IRunningObjectTable, oEnmMk As IEnumMoniker, oMK As IMoniker
    Dim obj As Object, oExcel As Excel.Application
    On Error Resume Next
   
    Set GetExcelApps = New Collection

    Set oROT = GetRunningObjectTable    ' Get the ROT object
    Set oEnmMk = oROT.EnumRunning       ' Get the ROT enumerator
    Do While oEnmMk.Next(1, oMK) = 0    ' Enumeate all registered objects
        Set obj = oROT.GetObject(oMK)   ' Get the object instance
        If Err Then
            Err.Clear
        Else
            If TypeOf obj Is Excel.Workbook Then
                Set oExcel = obj.Application
                ' add to collection; (ObjPtr only generates unique key)
                GetExcelApps.Add oExcel, "k" & ObjPtr(oExcel) '
                If Err Then Err.Clear
            End If
        End If
    Loop
End Function
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now