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

"Bogdan" <(E-Mail Removed)> wrote in message
news:E97C3FB1-CA5D-4574-8814-(E-Mail Removed)...
> 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

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

"NickHK" wrote:

> Check out using ADO. Then each workbook is viewed as a database, with
> worksheets as tables.
>
> NickHK
>
> "Bogdan" <(E-Mail Removed)> wrote in message
> news:E97C3FB1-CA5D-4574-8814-(E-Mail Removed)...
> > 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
>
>
>

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

"Bogdan" <(E-Mail Removed)> wrote in message
news:E97C3FB1-CA5D-4574-8814-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:5F301D81-AEA0-469E-8631-(E-Mail Removed)...
> 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
>
> "NickHK" wrote:
>
>> Check out using ADO. Then each workbook is viewed as a database, with
>> worksheets as tables.
>>
>> NickHK
>>
>> "Bogdan" <(E-Mail Removed)> wrote in message
>> news:E97C3FB1-CA5D-4574-8814-(E-Mail Removed)...
>> > 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
>>
>>
>>

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

regards,

bogdan

"Jean-Yves" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:5F301D81-AEA0-469E-8631-(E-Mail Removed)...
> > 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
> >
> > "NickHK" wrote:
> >
> >> Check out using ADO. Then each workbook is viewed as a database, with
> >> worksheets as tables.
> >>
> >> NickHK
> >>
> >> "Bogdan" <(E-Mail Removed)> wrote in message
> >> news:E97C3FB1-CA5D-4574-8814-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>