Get worksheet names...
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.
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.
ASKER
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
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
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
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
change "text.xls" to the name of the workbook to get the sheets in
Dave
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
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
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
ASKER
Hi anv,
You have very interesting piece of code. I am trying it now.
Thanks,
Mike
You have very interesting piece of code. I am trying it now.
Thanks,
Mike
ASKER
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
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
>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
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
btw
thanx for "I owe you a case of beer now."
:))
thanx for "I owe you a case of beer now."
:))
ASKER
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
next i
Next
You may want to take a look at these question:
https://www.experts-exchange.com/questions/21123461/About-hidden-instance-of-excel.html
https://www.experts-exchange.com/questions/21123590/About-handle-vb-6.html
Thanks,
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
next i
Next
You may want to take a look at these question:
https://www.experts-exchange.com/questions/21123461/About-hidden-instance-of-excel.html
https://www.experts-exchange.com/questions/21123590/About-handle-vb-6.html
Thanks,
For i = 1 to tl.columns.count
Debug.Print tl.Columns(i).Properties(0
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
Debug.Print tl.Columns(i).Properties(0
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
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
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
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
next i
Next
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
next i
Next
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
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
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...
ASKER
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
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
ASKER
Hi anv,
I still owe you a case of beer. Send an email in my profile.
Mike
I still owe you a case of beer. Send an email in my profile.
Mike
ASKER
On this link (unrelatred to above question):
https://www.experts-exchange.com/questions/21126594/Workbook-not-visible.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",
Set m_app = GetObject(,"Ecel.Applicati
'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?
https://www.experts-exchange.com/questions/21126594/Workbook-not-visible.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",
Set m_app = GetObject(,"Ecel.Applicati
'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?
Thanks :-)
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
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
Use
Set appExcel = GetObject(,"Excel.Applicat
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