Check if a sheet exists in a file, without opening that file

[Guest]

Hi there,

The facts:
- I have a folder with 50-60 files, each file having more than 10 sheets;
- the files are pretty big (some of them 40mb+) and are linked to many other
files from various locations from the network;

What I would like to do is:
- to verify whether all files are comprising a sheet named for example "Abc";

The code currently used by me is doing this check, but it is necessary to
open each file from the respective folder. The issue is that due to big
number of files, size and links, the execution of this code takes few hours.
Thus, my code is useless.

The question: is it possible to make this test without opening the files?

Many thanks in advance,

Bogdan

 

[NickHK]

Check out using ADO. Then each workbook is viewed as a database, with
worksheets as tables.

NickHK

 

[Guest]

Thanks, but I do not know exactly how to use ADO. Can you help me with the
code?

Thank you very much in advance,

Bogdan

 

[Bob Flanagan]

You can try writing a formula to a cell that refers to the workbook and
sheet and cell A1 on the sheet. If it evaluates to a value, then the sheet
exists. If it comes back as an error value, then the sheet doesn't exist.
However, you may get prompted to select an alternate sheet.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Jean-Yves]

Hi,

First you need to make a reference to "Microsoft ActiveX Data Objects 2.x
Library
Then adapt the below codse to your needs

sub test
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim x As Integer, i As Integer, nrow As Integer

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'change the file name in here
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=R:\Statistics\STATIST\Current
Books\Statistics for the books.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
' the table/sheet name is follwed by a $ sign [BRUSSELS CS$]=>
[myworksheetBRUSSELS CS$]
'this recorset open command should give an erro when the table/sheet is not
presnet
rs.Open "Select [ABBR], [RADIO CALLSIGN], [TOTAL] from [BRUSSELS CS$] ORDER
BY [TOTAL] DESC, [ABBR] ;", cn, adOpenDynamic, adLockReadOnly
'count the records
x = 0
'Application.StatusBar = "Counting CS"
Do While rs.EOF = False
x = x + 1
rs.MoveNext
Loop
rs.MoveFirst

Do While rs.EOF = False
tbl.Cell(i, 1).Range.Text = rs.Fields("ABBR").Value
tbl.Cell(i, 2).Range.Text = rs.Fields("RADIO CALLSIGN").Value
tbl.Cell(i, 3).Range.Text = rs.Fields("TOTAL").Value
i = i + 1
rs.MoveNext
Loop
rs.Close

HTH
Regbards
J-Y

 

[Guest]

thank you very much. i'll try it and I'll let you know.

regards,

bogdan

Hi,

First you need to make a reference to "Microsoft ActiveX Data Objects 2.x
Library
Then adapt the below codse to your needs

sub test
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim x As Integer, i As Integer, nrow As Integer

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'change the file name in here
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=R:\Statistics\STATIST\Current
Books\Statistics for the books.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
' the table/sheet name is follwed by a $ sign [BRUSSELS CS$]=>
[myworksheetBRUSSELS CS$]
'this recorset open command should give an erro when the table/sheet is not
presnet
rs.Open "Select [ABBR], [RADIO CALLSIGN], [TOTAL] from [BRUSSELS CS$] ORDER
BY [TOTAL] DESC, [ABBR] ;", cn, adOpenDynamic, adLockReadOnly
'count the records
x = 0
'Application.StatusBar = "Counting CS"
Do While rs.EOF = False
x = x + 1
rs.MoveNext
Loop
rs.MoveFirst

Do While rs.EOF = False
tbl.Cell(i, 1).Range.Text = rs.Fields("ABBR").Value
tbl.Cell(i, 2).Range.Text = rs.Fields("RADIO CALLSIGN").Value
tbl.Cell(i, 3).Range.Text = rs.Fields("TOTAL").Value
i = i + 1
rs.MoveNext
Loop
rs.Close

HTH
Regbards
J-Y

Thanks, but I do not know exactly how to use ADO. Can you help me with the
code?

Thank you very much in advance,

Bogdan