topic Re: in VBA Forum

Finding files using VBA and Office components

Anonymous — Tue, 30 Sep 2003 23:39:47 GMT

FileSearch is a member of Office library, eg you have to refer in your
project to Microsoft Office 9.0 Object Library (MSO9.dll or something like
this) to have acces to it.
Usualy I use like this:
____
Dim OfficeRef As Object
Set OfficeRef = CreateObject("Excel.Application")
With OfficeRef.FileSearch
.NewSearch
.LookIn = "D:\" ' or whatever
.SearchSubFolders = True ' or False
.FileName = "*.dwg; *.tif; *.gif; *.dxf" ' here is the problem
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
End If
End With
------
" .FileName = "*.dwg; *.tif; *.gif; *.dxf" " it works only in
Office 2000 and previous. I think that Office XP not longer support this
usage and return only ".dwg" files (after a long time and maibe because this
is the first in the list).
Of course I still want to use this code because is faster instead of using
something like this:
______
With OfficeRef.FileSearch
....
.FileName = "*.dwg"
End With
'And again
With OfficeRef.FileSearch
....
.FileName = "*.tif"
End With
'etc
--------
In huge directories the fist code is taking less than 2 minutes and the
second about 14 minutes.
Does anybody have some ideea of still using this in Office XP (and why did
they regress :o);)?
Sugestion: We can add custom built-in constants in .FileType member of this
object?

Mihai

Re: Finding files using VBA and Office components

Anonymous — Wed, 01 Oct 2003 02:25:26 GMT

Using another application to search for files is, in my opinion, a bit
extreme. When this application is Excel...

Take a look at the scripting component. You'll find it under references as
Microsoft Scripting Runtime.

--
Rune Wold
Take the dog for a walk to reply

--
"mihai" wrote in message
news:9FD4D6FED3CCFA2453A9632FE69C4D57@in.WebX.maYIadrTaRb...
> FileSearch is a member of Office library, eg you have to refer in your
> project to Microsoft Office 9.0 Object Library (MSO9.dll or something like
> this) to have acces to it.
> Usualy I use like this:
> ____
> Dim OfficeRef As Object
> Set OfficeRef = CreateObject("Excel.Application")
> With OfficeRef.FileSearch
> .NewSearch
> .LookIn = "D:\" ' or whatever
> .SearchSubFolders = True ' or False
> .FileName = "*.dwg; *.tif; *.gif; *.dxf" ' here is the problem
> If .Execute() > 0 Then
> For i = 1 To .FoundFiles.Count
> MsgBox .FoundFiles(i)
> Next i
> End If
> End With
> ------
> " .FileName = "*.dwg; *.tif; *.gif; *.dxf" " it works only in
> Office 2000 and previous. I think that Office XP not longer support this
> usage and return only ".dwg" files (after a long time and maibe because
this
> is the first in the list).
> Of course I still want to use this code because is faster instead of using
> something like this:
> ______
> With OfficeRef.FileSearch
> ....
> .FileName = "*.dwg"
> End With
> 'And again
> With OfficeRef.FileSearch
> ....
> .FileName = "*.tif"
> End With
> 'etc
> --------
> In huge directories the fist code is taking less than 2 minutes and the
> second about 14 minutes.
> Does anybody have some ideea of still using this in Office XP (and why did
> they regress :o);)?
> Sugestion: We can add custom built-in constants in .FileType member of
this
> object?
>
> Mihai
>
>

Re: Finding files using VBA and Office components

Anonymous — Wed, 01 Oct 2003 08:27:41 GMT

Use Window's api. Never open another app to provide functionality that
you don't have by default in your current app. XP did not digress, you
did by accessing it as opposed to 2000 which has a lesser load on your
memory.
___________________________
Mike Tuersley
AutoCAD Clinic
Rand IMAGINiT Technologies

Re:

Anonymous — Thu, 02 Oct 2003 22:55:43 GMT

Well, OK. I didn't know (yet) how to use Windows API (some good
documentation anywhere?). Did this fully solve the problem meaning searching
multiple file types once?

Otherwise, my aplication already and usually use Excel for exporting -
importing data for many other purposes, but this older code stop running
corectly because of upgrading to XP.
> XP did not digress, you
> did by accessing it as opposed to 2000 which has a lesser load on your
> memory.
If this mean that I have not enough memory resources, well, my computer is
1.7 Ghz and 1 GB Ram. Should be enough ... for a while.

"Mike Tuersley" wrote in message
news:MPG.19e4ca75fe072b88989783@discussion.autodesk.com...
> Use Window's api. Never open another app to provide functionality that
> you don't have by default in your current app. XP did not digress, you
> did by accessing it as opposed to 2000 which has a lesser load on your
> memory.
> ___________________________
> Mike Tuersley
> AutoCAD Clinic
> Rand IMAGINiT Technologies

Re:

Anonymous — Thu, 02 Oct 2003 23:05:22 GMT

My aplication already and usually use Excel for exporting -
importing data for many other purposes, but this older code stop running
corectly because of upgrading to XP.
Microsoft Scripting Runtime Class does not contain any member/method for
searching files (or is my mistake)?

"Rune Wold" wrote in message
news:7580CB1BF4F7509429F8299BB0F1DAEB@in.WebX.maYIadrTaRb...
> Using another application to search for files is, in my opinion, a bit
> extreme. When this application is Excel...
>
> Take a look at the scripting component. You'll find it under references as
> Microsoft Scripting Runtime.
>
> --
> Rune Wold
> Take the dog for a walk to reply
>
> --
> "mihai" wrote in message
> news:9FD4D6FED3CCFA2453A9632FE69C4D57@in.WebX.maYIadrTaRb...
> > FileSearch is a member of Office library, eg you have to refer in your
> > project to Microsoft Office 9.0 Object Library (MSO9.dll or something
like
> > this) to have acces to it.
> > Usualy I use like this:
> > ____
> > Dim OfficeRef As Object
> > Set OfficeRef = CreateObject("Excel.Application")
> > With OfficeRef.FileSearch
> > .NewSearch
> > .LookIn = "D:\" ' or whatever
> > .SearchSubFolders = True ' or False
> > .FileName = "*.dwg; *.tif; *.gif; *.dxf" ' here is the problem
> > If .Execute() > 0 Then
> > For i = 1 To .FoundFiles.Count
> > MsgBox .FoundFiles(i)
> > Next i
> > End If
> > End With
> > ------
> > " .FileName = "*.dwg; *.tif; *.gif; *.dxf" " it works only in
> > Office 2000 and previous. I think that Office XP not longer support this
> > usage and return only ".dwg" files (after a long time and maibe because
> this
> > is the first in the list).
> > Of course I still want to use this code because is faster instead of
using
> > something like this:
> > ______
> > With OfficeRef.FileSearch
> > ....
> > .FileName = "*.dwg"
> > End With
> > 'And again
> > With OfficeRef.FileSearch
> > ....
> > .FileName = "*.tif"
> > End With
> > 'etc
> > --------
> > In huge directories the fist code is taking less than 2 minutes and the
> > second about 14 minutes.
> > Does anybody have some ideea of still using this in Office XP (and why
did
> > they regress :o);)?
> > Sugestion: We can add custom built-in constants in .FileType member of
> this
> > object?
> >
> > Mihai
> >
> >
>
>

Re:

Anonymous — Thu, 02 Oct 2003 23:05:22 GMT

I use the Scripting Runtime because I find it the simplest but aparently the best method is the Windows API. Anyhow this may get you started:



Private Sub GetFiles(strPath As String, strFile As String, blnSearchSub as Boolean)

Dim objFSO As Scripting.FileSystemObject

Dim objFolder As Scripting.Folder

Dim objFile As Scripting.File

Dim objSubDirs As Scripting.Folders

Dim objLoopFolder As Scripting.Folder

Set objFSO = New Scripting.FileSystemObject

Set objFolder = objFSO.GetFolder(strPath)

If objFolder.Path <> "C:\System Volume Information" Then

    For Each objFile In objFolder.Files

        If objFile.Name Like strFile = True Then



            ‘do something with the file



        End If

    Next objFile

    If blnSearchSub = True

        Set objSubDirs = objFolder.SubFolders

        For Each objLoopFolder In objSubDirs

            Call GetFiles(objLoopFolder.Path, strFile, True)

        Next objLoopFolder

    End if 

End If

End Sub

Regards - Nathan

Re:

Anonymous — Thu, 02 Oct 2003 23:05:22 GMT

Use "Option Compare Text" in your general declarations.