Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

In the VBE select Tools - References - Microsoft ActiveX Data Objects
Recordset ?.? Library
--
HTH...

Jim Thomlinson


"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

You need to add reference to your VBProject module. In the VBA IDE,
Tools-References. Look for something like "Microsoft ActiveX Data Objects
2.6 Library". The version number will depend on what's installed in your
computer.

"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

Using the same approach, I would have chosen

Microsoft ActiveX Data Objects 2.x Library (for me, the version was 2.8 -
use the latest you have)

which is the ADODB library

Jim's recommendation is the ADOR library, so it would not have a definition
for ADODB.Connection as requested.

--
Regards,
Tom Ogilvy

"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

Hi Tom and thanks for your answer. Today I posted this question (maybe you
saw it):

"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?"

I kindly ask you for help me on this, because I cannot figure out how to do
it. Someone suggested me to use ADO, but...

Many thanks in advance,

Bogdan


"Tom Ogilvy" wrote:

Using the same approach, I would have chosen

Microsoft ActiveX Data Objects 2.x Library (for me, the version was 2.8 -
use the latest you have)

which is the ADODB library

Jim's recommendation is the ADOR library, so it would not have a definition
for ADODB.Connection as requested.

--
Regards,
Tom Ogilvy

"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

Jake Marx posted this code which looks at all files in a directory and lists
their sheet names to the immediate window in the VBE (View=Immediate window
in the VBE). It uses late binding to create references to the ADOX and ADODB
libraries, so that should eliminate that requirement for you. You should be
able to modify this code to reflect which files contain ABC and which don't:
If not, contact me at and we can work it out.


To use the code, write a calling procedure like

Sub Main()
Demo "C:\Myfolder"
end sub

Jake's code: ---------------------

Sub Demo(rsFolderPath As String)
Dim fso As Object
Dim fil As Object
Dim vWSNames As Variant
Dim v As Variant


Set fso = CreateObject("Scripting.FileSystemObject")


For Each fil In fso.GetFolder(rsFolderPath).Files
If StrComp(fil.Type, "Microsoft Excel " & _
"Worksheet", vbTextCompare) = 0 Then
Debug.Print fil.Path
vWSNames = mvGetWSNames(fil.Path)
For Each v In vWSNames
Debug.Print " " & CStr(v)
Next v
End If
Next fil


Set fso = Nothing
End Sub


Private Function mvGetWSNames(rsWBPath As String) _
As Variant
Dim adCn As Object
Dim axCat As Object
Dim axTab As Object
Dim asSheets() As String
Dim nShtNum As Integer


Set adCn = CreateObject("ADODB.Connection")
Set axCat = CreateObject("ADOX.Catalog")


With adCn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & rsWBPath & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
.CursorLocation = 3
.Open
End With


Set axCat.ActiveConnection = adCn


For Each axTab In axCat.Tables
ReDim Preserve asSheets(0 To nShtNum)
asSheets(nShtNum) = Left$(axTab.Name, _
Len(axTab.Name) - 1)
nShtNum = nShtNum + 1
Next axTab


mvGetWSNames = asSheets


Set axCat = Nothing
adCn.Close
Set adCn = Nothing
End Function


--
Regards,
Tom Ogilvy

"Bogdan" wrote:

Hi Tom and thanks for your answer. Today I posted this question (maybe you
saw it):

"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?"

I kindly ask you for help me on this, because I cannot figure out how to do
it. Someone suggested me to use ADO, but...

Many thanks in advance,

Bogdan


"Tom Ogilvy" wrote:

Using the same approach, I would have chosen

Microsoft ActiveX Data Objects 2.x Library (for me, the version was 2.8 -
use the latest you have)

which is the ADODB library

Jim's recommendation is the ADOR library, so it would not have a definition
for ADODB.Connection as requested.

--
Regards,
Tom Ogilvy

"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

You are absolutely correct. I was going from memory which is not all it is
cracked up to be. That being said I normally choose 2.5 or 2.7 (now that I
actually take a look at the list) as it has all of the functionallity and
tends to be more backwards compatible with lesser versions of XL. IIRC 2.8
only came into being with service Pack 2 (?) of XL 2000 and as a result there
are still a bunch of users who may not have it. But I think 2.8 was a
security patch (I think) so if all of your users have it then use it...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Using the same approach, I would have chosen

Microsoft ActiveX Data Objects 2.x Library (for me, the version was 2.8 -
use the latest you have)

which is the ADODB library

Jim's recommendation is the ADOR library, so it would not have a definition
for ADODB.Connection as requested.

--
Regards,
Tom Ogilvy

"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan

This solution worked very well for me.

But (and you knew there had to be one), how do I make sure each of my users
has these objects checked?
--
Adios,
Clay Harryman


"Tom Ogilvy" wrote:

Using the same approach, I would have chosen

Microsoft ActiveX Data Objects 2.x Library (for me, the version was 2.8 -
use the latest you have)

which is the ADODB library

Jim's recommendation is the ADOR library, so it would not have a definition
for ADODB.Connection as requested.

--
Regards,
Tom Ogilvy

"Bogdan" wrote:

Hi there,

Today I posted a question and I received an answer containing reference to
ADODB.Connection. When I try to run the code, the following message occurs:

"User-defined type not defined"

And I have no clue how to fix it.

Can anyone help me on this?

Many thanks in advance,

Bogdan