# [ADO Excel] How to use ADO retrieve Excel Worksheet name and some information

• ### Question

• I want to retrieve Excel worksheet name and total count of sheets
Thursday, September 7, 2006 12:41 PM

### All replies

• To retrieve Excel worksheet name and total count of sheets is by Excel.Application, but not by AdoDb:

Dim mExcel as new Excel.Application, wb as Workbook, ws as worksheet, YrXlsFilNam as string
YrXlsFilNam = "...."
set mExcel.workbooks.open(YrXlsFilNam)
debug.print wb.worksheets.count
for each ws in wb.worksheets
debug.print ws.name
next

Friday, September 8, 2006 10:00 AM
• Mr Choy,

Please have a look at my blog. I have written a post on how to retrieve the worksheet names and count using ADOX.

Sunday, September 10, 2006 1:52 PM
• Hello,

Well, I managed to solve it by using an ADODB object
thanks to "Desertphile" in newsgroup 'comp.programming'
He posted the code below.

Just copy the program below in a VB6 project and place the missing objects on your form and then it will show itself.

Regards,

Henk

Option Explicit

Private Sub btnExample_Click()
Dim cn As ADODB.Connection, c As Integer, d As Integer

MousePointer = 11
DoEvents

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\My
Documents\productmaster.xls;" & "Extended Properties=Excel 8.0;"
.Open
End With

rsT.Open "SELECT * from [Sheet1$A3:U5861]", cn, adOpenStatic c = rsT.RecordCount If c > 0 Then rsT.MoveFirst Open "C:\smp\pm.txt" For Output As #1 For d = 1 To c If Not IsNull(rsT.Fields(0).Value) Then List2.AddItem rsT.Fields(0).Value Else List2.AddItem "" End If rsT.MoveNext Next rsT.Close cn.Close Close MousePointer = 0 DoEvents End Sub Private Sub Command1_Click() Dim cn As ADODB.Connection, c As Integer, d As Integer Dim rsT As ADODB.Recordset Set cn = New ADODB.Connection With cn .Provider = "Microsoft.Jet.OLEDB.4.0" '.ConnectionString = "Data Source=C:\desertphile\sundata.xls;" & "Extended Properties=Excel 8.0;" .ConnectionString = "Data Source=C:\My Documents\productmaster.xls;" & "Extended Properties=Excel 8.0;" .CursorLocation = adUseClient .Open End With Set rsT = New ADODB.Recordset 'rsT.Open "SELECT * from [Print-This$A1:A7]", cn, adOpenStatic
'rsT.Open "SELECT * from [Print-This$]", cn, adOpenStatic rsT.CursorLocation = adUseClient rsT.Open "SELECT * from [Sheet1$A3:E373]", cn, adOpenStatic

c = rsT.RecordCount

If c > 0 Then rsT.MoveFirst

For d = 1 To c
If Not IsNull(rsT.Fields(0).Value) Then
Else
End If
rsT.MoveNext
Next
rsT.Close
cn.Close

End Sub

Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
'.ConnectionString = "Data Source=C:\job\rancho.xls;" &
"Extended Properties=Excel 8.0;"
'.ConnectionString = "Data Source=C:\desertphile\sundata.xls;"
& "Extended Properties=Excel 8.0;"
'.ConnectionString = "Data Source=C:\My
Documents\productmaster.xls;" & "Extended Properties=Excel 8.0;"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\test\db1.mdb;"
.Open
End With

intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Count
For t = 1 To intTblCnt
strTbl = rsT.Fields("TABLE_NAME").Value
List1.AddItem vbTab & "Table #" & t & ":    " & strTbl
For f = 0 To intTblFlds - 1
List1.AddItem vbTab & rsT.Fields(f).Name & _
vbTab & rsT.Fields(f).Value
Next
Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty,
strTbl, Empty))
intColCnt = rsC.RecordCount
intColFlds = rsC.Fields.Count
For c = 1 To intColCnt
strCol = rsC.Fields("COLUMN_NAME").Value
List1.AddItem vbTab & vbTab & "Column #" & c & ": " &
strCol
List1.AddItem vbTab & vbTab & "--------------------"
For f = 0 To intColFlds - 1
List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
vbTab & rsC.Fields(f).Value & Str\$(f)
Next
List1.AddItem vbTab & vbTab & "--------------------"
rsC.MoveNext
Next
rsC.Close
rsT.MoveNext
Next
rsT.Close
cn.Close
End Sub
Tuesday, September 19, 2006 11:01 AM
• Dear Johnson,

I have used the above in my excel file by copying this into visual basic editor.

Actually I want the all woksheets name of my file in a sheet.

Suppose if i have one file named c:\a.xls having four different sheets. Can you please tell me how to apply this code into the file.

I am new to this thing please advice me in full, means i have to just copy the thing which you will give me.

Please write sub () and endsub in this code also so that i can understand in full. Because I have already tried the above but error is coming

Sub a()

Dim mExcel As New Excel.Application, wb As Workbook, ws As Worksheet, YrXlsFilNam As String
YrXlsFilNam = "c:\a.xls"
set mExcel.workbooks.open(YrXlsFilNam)  (Error is coming in this line)
Debug.Print wb.Worksheets.Count
For Each ws In wb.Worksheets
Debug.Print ws.Name
Next

End Sub

Regards,

Kamal

• Proposed as answer by Tuesday, March 2, 2010 3:27 PM
Wednesday, September 20, 2006 1:04 PM
• Dear Johnson,

I have used the above in my excel file by copying this into visual basic editor.

Actually I want the all woksheets name of my file in a sheet.

Suppose if i have one file named c:\a.xls having four different sheets. Can you please tell me how to apply this code into the file.

I am new to this thing please advice me in full, means i have to just copy the thing which you will give me.

Please write sub () and endsub in this code also so that i can understand in full. Because I have already tried the above but error is coming

Sub a()

Dim mExcel As New Excel.Application, wb As Workbook, ws As Worksheet, YrXlsFilNam As String
YrXlsFilNam = "c:\a.xls"
set mExcel.workbooks.open(YrXlsFilNam)  (Error is coming in this line)
Debug.Print wb.Worksheets.Count
For Each ws In wb.Worksheets
Debug.Print ws.Name
Next

End Sub

Regards,

Kamal

I know this is a very old post, but I also got the same error, and the code below will work in Access.

Dim mExcel As New Excel.Application, wb As Workbook, ws As Worksheet, YrXlsFilNam As String
YrXlsFilNam = "c:\a.xls"
Set wb = mExcel.Workbooks.Open(YrXlsFilNam)
Debug.Print wb.Worksheets.Count
For Each ws In wb.Worksheets
Debug.Print ws.Name
Next
• Proposed as answer by Tuesday, March 2, 2010 3:29 PM
Tuesday, March 2, 2010 3:29 PM
• Hello Derek,

Regards.

Daniel
Tuesday, March 2, 2010 6:38 PM
• Daniel,

try looking here  www.dsmyth.net/wiki

DATABASE CODE GENERATOR

Upload Projects to share or get help on and post the generated links here in the forum
www.srsoft.us
Tuesday, March 2, 2010 11:56 PM
• Thanks Derek,

It's down by now. I'll try later on.

Regards.

Daniel
Wednesday, March 3, 2010 6:32 PM