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
(E-Mail Removed) 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
Similar Threads
