filesearch problem 4

[grobbu]

Hello all,

as many of you probably know microsoft isn't invluding the filesearch object anymore in office 2007. There are some people writin a replacement. You can follow there efforts here :

http://www.tek-tips.com/viewthread.cfm?qid=1551548

Meanwhile I've got a big problem because our main tool isn't working anymore. I didn't write the ap and my vba skills just aren't enough to rewrite this. So my question..Can anybody help me out to rewrite this little piece of code to get it working again. I already tried but i just can't do it

this is the code:
With Application.FileSearch
.NewSearch
.LookIn = CurrentDbDir() + "Sjablonen\Documenten\"
.SearchSubFolders = True
'--------------------------------
'- Zoeken naar Word-sjablonen -
'--------------------------------
.FileName = "*.dot"
If .Execute(msoSortByFileName, msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Lengte = Len(.FoundFiles(i))
If Left$(.FoundFiles(i), 2) <> "~$" Then
Me.Sjabloon = Mid$(.FoundFiles(i), 28, (Lengte - 31))
Me.VolledigePath = .FoundFiles(i)
DoCmd.OpenQuery "qrySjabloonToevoegen"
Else
MsgBox "Else"
End If
Next i
Else
MsgBox "Er zijn geen bestanden gevonden"
End If
'--------------------------------
'- Zoeken naar Excel-sjablonen -
'--------------------------------
.FileName = "*.xlt"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Lengte = Len(.FoundFiles(i))
Me.Sjabloon = Mid$(.FoundFiles(i), 28, (Lengte - 31))
Me.VolledigePath = .FoundFiles(i)
DoCmd.OpenQuery "qrySjabloonToevoegen"
Next i
End If
End With

Thanks to all help!
grob

 

[HarleyQuinn]

Are you wanting to get this to work in 2007?

Plus, no pressure on me now the FileSearch replacement is being more widely publicised...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[grobbu]

he HarleyQuinn ,

yes I want to get this too work in 2007.
I posted a link in your other thread concerning this issue...but my skills are too little to get it working.

I wouldn't dare too put pressure on you

You'll get my and lots of others eternal tnaks if you could manage it

grt
grob

 

[HarleyQuinn]

It's not in a state to post now but I could probably give you a version that will achieve all the things your code currently does on Monday (without some of the full functionality of filesearch).

I have no doubt though that someone will have posted a different solution for you by then though

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[grobbu]

that would be great! Can't thank you enough

We'll see if their will be a solution from someone else before next week..

anyway..tx again

 

[fumei]

HQ: "I have no doubt though that someone will have posted a different solution for you by then though"

Really?



tum ti tum

tum ti tum tum

Must admit I am having fun deconstructing the class module (unfortunately using DSO) I got from the link posted in the other thread. <wriggle hand>Maybe yes...maybe no</wriggle hand>

I think I will mostly wait for HQ to post a solution, or mostly a solution...'cause I got money down that you are not going to able to fully replicate the functionality.

However, if it replicates 90% of the common uses of FileSearch - for example, like the OP posted - that would be grand.

Gerry

 

[HarleyQuinn]

OK, light jesting aside (from both threads), I've got a version of this that will do everything that the OP's code currently does (some of the sortby's aren't supported yet, the sorting algorythms are not very efficient, as it's proper early release there's no error handling, it's not commmented and I'm going to change alot about it for the final release (I already have for lots of bits but I'm not going to give everything away until it's finished )).

I'll be posting it after I've had my dinner (plus there's a DLL for people who don't have VB6 to compile it, if anyone would like to host that it would be much appreciated )

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

OK, I'm going to have to post the DLL tomorrow as the lad who's hosting it for me can't do it until he gets home from work .

It's tested with the OP's exact scenario and works correctly (well, the same). If I change the .Lookin to "C:\" it performs about 30 - 45 seconds faster (even with my current slow and nasty sorting algorithms) than native FileSearch over about 246,500 files.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

Here is the initial DLL created.

Supported features of this version:

NewSearch,
Lookin,
SearchSubFolders,
FileName,
Execute(only by FileName, asc or desc),
FileType (msoFileTypeExcelWorkbooks and msoFileTypeAllFiles).

Here's some example code to demonstrate the use of the dll (for the OP's situation, using both early and late binding. To use the early binding you'll need to add a reference to FileSearchHarley):

Dim fs as Object ' Late binding
Dim fs as FileSearchHarley.FileSearch

'Set fs = CreateObject("FileSearchHarley.FileSearch") ' Late binding

Set fs = New FileSearchHarley.FileSearch

    With fs
        .NewSearch
        .LookIn = CurrentDbDir() + "Sjablonen\Documenten\"
        .SearchSubFolders = True
        '--------------------------------
        '- Zoeken naar Word-sjablonen   -
        '--------------------------------
        .FileName = "*.dot"
        'If .Execute(1, 1) > 0 Then ' with late binding use either the numeric values or declare them as const (as it's outside of Excel the constants will need to be declared, in Excel they already are).
        If .Execute(msoSortByFileName, msoSortOrderAscending) > 0 Then
            For i = 1 To .FoundFiles.Count
                Lengte = Len(.FoundFiles(i))
                If Left$(.FoundFiles(i), 2) <> "~$" Then
                    Me.Sjabloon = Mid$(.FoundFiles(i), 28, (Lengte - 31))
                    Me.VolledigePath = .FoundFiles(i)
                    DoCmd.OpenQuery "qrySjabloonToevoegen"
                Else
                    MsgBox "Else"
                End If
            Next i
        Else
            MsgBox "Er zijn geen bestanden gevonden"
        End If
        '--------------------------------
        '- Zoeken naar Excel-sjablonen  -
        '--------------------------------
        .FileName = "*.xlt"
        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                Lengte = Len(.FoundFiles(i))
                Me.Sjabloon = Mid$(.FoundFiles(i), 28, (Lengte - 31))
                Me.VolledigePath = .FoundFiles(i)
                DoCmd.OpenQuery "qrySjabloonToevoegen"
            Next i
        End If
    End With

set fs = nothing

I'm aware that this isn't perfect (and there is alot of functionality I've excluded from this build as stated in previous posts) but it does work in this situation. As previously stated there is very little error handling at the moment, so if you try and break it there is a good chance you probably will in current state. It has held up when used correctly in test though.

Have a play with it, all feedback appreciated (esp. Gerry and strongm who've been there since Gerry almost forced me to write this ) as this is very much a work in progress.

Cheers

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[strongm]

One quick initial point, HQ. You state:

as it's outside of Excel the constants will need to be declared, in Excel they already are

but I believe they are only in versions of Excel that already support FileSearch. Certainly my version of Excel 2007 doesn't have them.

 

[HarleyQuinn]

Right you are Mr Strong, thanks for keeping me honest

Constant Declarations for this version (needed if you're late binding the DLL):

Const msoSortByFileName = 1
Const msoSortOrderAscending = 1
Const msoSortOrderDescending = 2
Const msoFileTypeAllFiles = 1
Const msoFileTypeExcelWorkbooks = 4

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[fumei]

So...in other words, for late binding you will need to have a previous version so you can figure out the constants. I, ahem, notice you did not include msoFileTypeWordDocuments in your list above.


Sniff.


It is 3 BTW. Is this in the DLL?

I did not force you to anything...I simply...encouraged you, as your skills have far greater depth than mine.

BTW: for other people who may wish to use the DLL, is a mention of regsvr32 needed here? Or just adding it via references?

Barnfather...what a lovely name.

Gerry

 

[HarleyQuinn]

Gerry,

Well, sort of. I'll post all the constants that are available with each release.

No word docs at the minute, it's specifically (pretty much) tailored to this problem but never fear! The version I'm looking at (and working on) now has all the FileTypes (and the FileTypes collection) that FileSearch currently has

Plus, what's the easiest way to work out things for late binding? That's right, early bind it until you know the object model and then remove the reference and late bind it

You didn't force me per se I'm glad you did encourage me though, this has been a learning experience for me and I've enjoyed it so far (and kept my full head of hair intact!).

as your skills have far greater depth than mine.

I think you're very much selling yourself short their Gerry!

OK, as it's a DLL I would advise registering it. You can do this through the cmd window and use regsvr32. I find this a bit long winded, so if you're able to edit your registry (and want to) you can put these lines of code in a .bat file, run it and it'll add a Register and Unregister option to you context menu when you right click on a DLL. I find this much easier .

REG ADD HKCR\dllfile\shell\Register\command /ve /d "regsvr32 \"%%1\"" /f
REG ADD HKCR\dllfile\shell\Unregister\command /ve /d "regsvr32 -u \"%%1\"" /f

Barnfather...what a lovely name.

Really? Thanks, don't hear that very often

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

you can put these lines of code in a .bat file, run it

This will work on an XP box, I haven't looked at the registry layout for Vista so I'm not going to advise just running it on a Vista box

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[fumei]

OK, as it's a DLL I would advise registering it. You can do this through the cmd window and use regsvr32. I find this a bit long winded"

My bolding. Really? What the heck is long winded about:

regsvr32 <path/filename>

Gerry

 

[fumei]

BTW: as I refuse to use 2007, or even countenance it on my computer, I can not make any comments about how this may work with 2007, which of course is the actual point.

I will leave the actual relevant comments to those - ummmm....people - who do use 2007.

That being said I will try and break it within the context of MY use of FileSearch.





Oooops. Look daddy, it fell down and can't get up!

Gerry

 

[HarleyQuinn]

Long winded might have been a bit of an overstatement but there is a lot more to it than clicking on a context menu item (might be lazy, but I've added most things I'd do within the cmd window to the context menu as from the end user point of view it takes out a step).

As I say, this isn't error handled and is an early prototype with limited functionality. What did you break about it in the context you use FileSearch (as feedback will help me make it work...)?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http

 

[grobbu]

my god..started the thread and reacting so sloooow

tx for all efforts HarleyQuinn!
I'll try it out tommorow and let you know how it worked out.

grt
grob

 

[grobbu]

I tested it and it looks to work without any problem!
no need for that error handling

tx a lot HarleyQuinn

grt
grob

 

[HarleyQuinn]

Glad to help grob

Work allowing I should have a much fuller version available sometime next week, with support for Word Documents!

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com