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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Check out using ADO. Then each workbook is viewed as a database, with
worksheets as tables.

NickHK
 
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
 
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
 
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
 
thank you very much. i'll try it and I'll let you know.

regards,

bogdan

Jean-Yves said:
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

Bogdan said:
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
Click to expand...
Click to expand...
 
Back
Top