Re: read sheetnames with ADO

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 22:02:03 -0000

Jim,

This ignores *** names with embedded spaces, they get seen as type TABLE
in your solution. If you include a test for TABLE as well, it removes the
final ' not the $. See my response for 2 ways that cater for it.

Bob

"Jim Rech" <jrrech@hotmail.com> wrote in message
news:OnT5ryFwEHA.3872@TK2MSFTNGP11.phx.gbl...
> This seems to do the trick. Test it though.
>
> ''Needs 2 ADO references
> '' - ActiveX Data Objects
> '' - ADO Ext for DDL and Security
>
> Sub Demo()
> ReadSheetNames "c:\Filename.xls"
> End Sub
>
> ''Displays sheets names in a closed workbook
> Sub ReadSheetNames(TheCompleteFilePath As String)
> Dim cnn As New ADODB.Connection
> Dim cat As New ADOX.Catalog
> Dim tbl As ADOX.Table
>
> cnn.Open "Provider=MSDASQL.1;Data Source=" _
> & "Excel Files;Initial Catalog=" & TheCompleteFilePath
> cat.ActiveConnection = cnn
> For Each tbl In cat.Tables
> If tbl.Type = "SYSTEM TABLE" Then ''Type "TABLE" seems to return
> multicell ranges
> Debug.Print Left$(tbl.Name, Len(tbl.Name) - 1)
> End If
> Next tbl
>
> Set cat = Nothing
> cnn.Close
> Set cnn = Nothing
> End Sub
>
> --
> Jim Rech
> Excel MVP
> "farmer" <antwerpen@hotmail.vom> wrote in message
> news:jl5do0pektjjh4oep6pdtkt7igt2l0t8qh@4ax.com...
> | Hi,
> |
> | I was wondering wether it's possible to use ADO for retrieving
> | sheetnames from an excel workbook.
> |
> | Does anyone know where to find examples?
> |
> | I'm using VB 6.0 and excel 2003
> |
> | Help appreciated,
> |
> |
> | Farmer
> |
> |
>
>

Quantcast