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
Andrew brought next idea :
You can do this using ADO. You'll find a working example here:
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
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.
GS said:on 7/13/2011, darkblue supposed : [ ]
It requires the necessary data provider for your particular version of
Excel. Check in the VBE>Tools>References 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.
GS said:on 7/13/2011, darkblue supposed : [ ]
It requires the necessary data provider for your particular version of
Excel. Check in the VBE>Tools>References 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
VBE>Tools>References.
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 said:GS said:on 7/13/2011, darkblue supposed : [ ]
It requires the necessary data provider for your particular version
of Excel. Check in the VBE>Tools>References 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
VBE>Tools>References.
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)?
saying that user defined object was not defined. Setting a reference toSet objConnection = New ADODB.Connection
GS said:Clif McIrvin explained :GS said:on 7/13/2011, darkblue supposed : [ ]
It requires the necessary data provider for your particular version
of Excel. Check in the VBE>Tools>References 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
VBE>Tools>References.
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>
GS said:Clif McIrvin explained :on 7/13/2011, darkblue supposed :
[ ]
It requires the necessary data provider for your particular version of
Excel. Check in the VBE>Tools>References 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
VBE>Tools>References.
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.
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.