How to find sheet names without opening files
 

Hello,
Is there a way to the name of the first sheet of each excel file in a
given folder. I know how to do this by opening each file, but I'd
like to be able to do it without opening each file.

thanks,
Andy

How to find sheet names without opening files
 

Andrew brought next idea :
Hello,
Is there a way to the name of the first sheet of each excel file in a
given folder. I know how to do this by opening each file, but I'd
like to be able to do it without opening each file.

thanks,
Andy

You can do this using ADO. You'll find a working example he

http://www.appspro.com/Tips/WorkbookTips.htm#WT1

under this Q:

How Can I Get a List of Worksheet Names From a Closed Workbook?

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

How to find sheet names without opening files
 

On Jul 12, 10:01*am, GS wrote:
Andrew brought next idea :

Hello,
Is there a way to the name of the first sheet of each excel file in a
given folder. *I know how to do this by opening each file, but I'd
like to be able to do it without opening each file.

thanks,
Andy

You can do this using ADO. You'll find a working example he

*http://www.appspro.com/Tips/WorkbookTips.htm#WT1

under this Q:

* How Can I Get a List of Work

That looks perfect. Thank you again Garry. I appreciate your help.

Andy

How to find sheet names without opening files
 

Andrew has brought this to us :
On Jul 12, 10:01*am, GS wrote:
Andrew brought next idea :

Hello,
Is there a way to the name of the first sheet of each excel file in a
given folder. *I know how to do this by opening each file, but I'd
like to be able to do it without opening each file.
thanks,
Andy

You can do this using ADO. You'll find a working example he

*http://www.appspro.com/Tips/WorkbookTips.htm#WT1

under this Q:

* How Can I Get a List of Work

That looks perfect. Thank you again Garry. I appreciate your help.

Andy

You're welcome! Glad to help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

How to find sheet names without opening files
 

Hi Garry

I receive a message when i click "getsheetnames" button.
What am i missing here ? Shall i have to install something?

The code is:
If Application.Version < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
szFullName & ";Extended Properties=Excel 8.0;"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
szFullName & ";Extended Properties=Excel 8.0;"
End If

Set objConnection = New ADODB.Connection
objConnection.Open szConnect <<<<<<<<<<<< Here, it hangs.

And the message is:
"Provider cannot be found. It may not be properly installed"

Thank you.

How to find sheet names without opening files
 

on 7/13/2011, darkblue supposed :
Hi Garry

I receive a message when i click "getsheetnames" button.
What am i missing here ? Shall i have to install something?

The code is:
If Application.Version < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
szFullName & ";Extended Properties=Excel 8.0;"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
szFullName & ";Extended Properties=Excel 8.0;"
End If

Set objConnection = New ADODB.Connection
objConnection.Open szConnect <<<<<<<<<<<< Here, it hangs.

And the message is:
"Provider cannot be found. It may not be properly installed"

Thank you.

It requires the necessary data provider for your particular version of
Excel. Check in the VBEToolsReferences dialog to make sure you have
either JET4.0 OR ACE12.0 installed. These should have been installed
when you installed MS Office, but may not be installed if your machine
is subject to IT rules for software installation.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

How to find sheet names without opening files
 

"GS" wrote in message
...
on 7/13/2011, darkblue supposed :
[ ]

It requires the necessary data provider for your particular version of
Excel. Check in the VBEToolsReferences dialog to make sure you have
either JET4.0 OR ACE12.0 installed. These should have been installed
when you installed MS Office, but may not be installed if your machine
is subject to IT rules for software installation.


???

On my copy of Excel 2010 none of the above are listed under
VBEToolsReferences.

There are, however, a series beginning

Microsoft ActiveX Data Objects ....

which appear to be a number of versions of the same library ... which
turns out to be the ADODB library.

Since I've never attempted to use this, I know very little ... maybe
choose the highest number (most current version)?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

How to find sheet names without opening files
 

Clif McIrvin explained :
"GS" wrote in message ...
on 7/13/2011, darkblue supposed :
[ ]

It requires the necessary data provider for your particular version of
Excel. Check in the VBEToolsReferences dialog to make sure you have
either JET4.0 OR ACE12.0 installed. These should have been installed when
you installed MS Office, but may not be installed if your machine is
subject to IT rules for software installation.


???

On my copy of Excel 2010 none of the above are listed under
VBEToolsReferences.

There are, however, a series beginning

Microsoft ActiveX Data Objects ....

which appear to be a number of versions of the same library ... which turns
out to be the ADODB library.

Since I've never attempted to use this, I know very little ... maybe choose
the highest number (most current version)?

The ADO library is a mandatory requirement since we can't use ADO
without it. However, the project itself has these refs in it and so
should persist to the current version installed. I thought this was
automagic when our projects use/ref these libs!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

How to find sheet names without opening files
 

"Clif McIrvin" wrote in message
...
"GS" wrote in message
...
on 7/13/2011, darkblue supposed :
[ ]

It requires the necessary data provider for your particular version
of Excel. Check in the VBEToolsReferences dialog to make sure you
have either JET4.0 OR ACE12.0 installed. These should have been
installed when you installed MS Office, but may not be installed if
your machine is subject to IT rules for software installation.


???

On my copy of Excel 2010 none of the above are listed under
VBEToolsReferences.

There are, however, a series beginning

Microsoft ActiveX Data Objects ....

which appear to be a number of versions of the same library ... which
turns out to be the ADODB library.

Since I've never attempted to use this, I know very little ... maybe
choose the highest number (most current version)?



I suppose I jumped the gun and posted too soon. When trying to
duplicate OP's error, I received a compile error on
Set objConnection = New ADODB.Connection
saying that user defined object was not defined. Setting a reference to
a Microsoft ActiveX Data Objects library both compiled and ran ... but
as I never saw the OP's error IDK if my observation is relevant or not.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

How to find sheet names without opening files
 

"GS" wrote in message
...
Clif McIrvin explained :
"GS" wrote in message
...
on 7/13/2011, darkblue supposed :
[ ]

It requires the necessary data provider for your particular version
of Excel. Check in the VBEToolsReferences dialog to make sure you
have either JET4.0 OR ACE12.0 installed. These should have been
installed when you installed MS Office, but may not be installed if
your machine is subject to IT rules for software installation.


???

On my copy of Excel 2010 none of the above are listed under
VBEToolsReferences.

There are, however, a series beginning

Microsoft ActiveX Data Objects ....

which appear to be a number of versions of the same library ... which
turns out to be the ADODB library.

Since I've never attempted to use this, I know very little ... maybe
choose the highest number (most current version)?

The ADO library is a mandatory requirement since we can't use ADO
without it. However, the project itself has these refs in it and so
should persist to the current version installed. I thought this was
automagic when our projects use/ref these libs!<g



I suppose that's the reason for all the library versions, then. When
you open an old project referencing it's library, that library version
is still available -- so the reference is not broken by a new Office
version.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

How to find sheet names without opening files
 

Clif McIrvin formulated the question :
"GS" wrote in message ...
Clif McIrvin explained :
"GS" wrote in message
...
on 7/13/2011, darkblue supposed :
[ ]

It requires the necessary data provider for your particular version of
Excel. Check in the VBEToolsReferences dialog to make sure you have
either JET4.0 OR ACE12.0 installed. These should have been installed when
you installed MS Office, but may not be installed if your machine is
subject to IT rules for software installation.


???

On my copy of Excel 2010 none of the above are listed under
VBEToolsReferences.

There are, however, a series beginning

Microsoft ActiveX Data Objects ....

which appear to be a number of versions of the same library ... which
turns out to be the ADODB library.

Since I've never attempted to use this, I know very little ... maybe
choose the highest number (most current version)?

The ADO library is a mandatory requirement since we can't use ADO without
it. However, the project itself has these refs in it and so should persist
to the current version installed. I thought this was automagic when our
projects use/ref these libs!<g



I suppose that's the reason for all the library versions, then. When you
open an old project referencing it's library, that library version is still
available -- so the reference is not broken by a new Office version.

I believe that's normally the case but it may also update to the newer
version if the 'design time' version doesn't exist.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

How to find sheet names without opening files
 

Check in the VBEToolsReferences dialog to make sure you have
either JET4.0 OR ACE12.0 installed.

Hi Garry,
Sorry for being nuisance but what are the exact names of these
references ?
I don't seem to have them in my refecences list.
I have WindowsXP, OfficeXP on my machine.
Thank you.

How to find sheet names without opening files
 

It happens that darkblue formulated :
Check in the VBEToolsReferences dialog to make sure you have
either JET4.0 OR ACE12.0 installed.

Hi Garry,
Sorry for being nuisance but what are the exact names of these
references ?
I don't seem to have them in my refecences list.
I have WindowsXP, OfficeXP on my machine.
Thank you.

Since the project was built with the correct refs, you shouldn't need
to do anything. If they aren't listed then they probably weren't
installed but it's more likely they were removed since they get
installed by default unless the install was done as a 'Custom
Installation'.

The names of the required libs are listed in the comments above the
'GetSheetNames' procedure...

''' * Microsoft ActiveX Data Objects 2.5 Library
''' * Microsoft ADO Ext. 2.5 for DDL and Security

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

How to find sheet names without opening files
 

Thank you Garry.
Kind regards