Accessing Closed Workbook

[Loomah]

Hi All

I'm aware of how properties of closed workbooks can be accessed using OLE
Document Properties Object Library but is it possible to obtain sheet names
and other such things (used range, sheet names etc) of a closed workbook
without opening?

TIA
;-)

 

[Bob Phillips]

Loomah,

You can get the sheet names with this function

Example Call

IfSheetExists("c:\myTest\Testfile_1.xls","Sheet1")

can be called from VBA or a worksheet

Function IfSheetExists(fName As String, sh As String) As Boolean
Dim objConn As Object
Dim objCat As Object
Dim tbl As Object
Dim iRow As Long
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

IfSheetExists = False

With ActiveSheet

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fName & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open sConnString
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn

iRow = 1
For Each tbl In objCat.Tables
sTableName = tbl.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces are enclosed by single
quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If
'Worksheet names always end in the "$" character
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
If sh = Mid$(sTableName, iStartpos, cLength - (iStartpos +
iTestPos)) Then
IfSheetExists = True
Exit For
End If
End If
Next tbl
End With

objConn.Close
Set objCat = Nothing
Set objConn = Nothing

End Function





--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Loomah]

Bob
Thanks for the reply. It wasn't exactly what I was looking for but I
thought I'd be able to adapt it, which I nearly have done. What I'm looking
for is the name of the first worksheet as the returned value.

As I say I tought I had it but (because I don't fully understand your code)
I've run into problems. The problem being that the tables (it appears) are
not indexed in the order the appear in the workbook but rather in the order
they were created.

Below is what I have so far but do you have any further suggestions or is
there a completely different approach?
Is there any way to count the number of sheets in a book in this way and use
the sheet indexes?

TIA

Function GetSheetName(fName As String) As String
Dim objConn As Object
Dim objCat As Object
Dim tbl As Object
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer


sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fName & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open sConnString
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn

sTableName = objCat.tables(0).Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces are enclosed by single
quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If
'Worksheet names always end in the "$" character
'if it is always the case is the test necessary?
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
GetSheetName = Mid$(sTableName, iStartpos, cLength -
(iStartpos + iTestPos))
End If

objConn.Close
Set objCat = Nothing
Set objConn = Nothing

End Function