Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

filesearch problem 4

Status
Not open for further replies.
grobbu

grobbu

Programmer
Jun 25, 2002
40
0
0
BE
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 :
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
 
Sort by date Sort by votes
Are you wanting to get this to work in 2007?

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

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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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 [smile]

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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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 [wink])).

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 [smile])

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.

 
Upvote 0 Downvote
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 [sad].

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.

 
Upvote 0 Downvote
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):
Code: 
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 [wink]) 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.

 
Upvote 0 Downvote
One quick initial point, HQ. You state:
HarleyQuinn said:
as it's outside of Excel the constants will need to be declared, in Excel they already are
Click to expand...
but I believe they are only in versions of Excel that already support FileSearch. Certainly my version of Excel 2007 doesn't have them.
 
Upvote 0 Downvote
Right you are Mr Strong, thanks for keeping me honest [blush]

Constant Declarations for this version (needed if you're late binding the DLL):
Code: 
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.

 
Upvote 0 Downvote
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? [rofl]

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
 
Upvote 0 Downvote
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 [smile]

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 [wink]

You didn't force me per se [wink] 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!).
Gerry said:
as your skills have far greater depth than mine.
Click to expand...
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 [wink].
Code: 
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.
Click to expand...
Really? [wink] Thanks, don't hear that very often [smile]

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.

 
Upvote 0 Downvote
Me said:
you can put these lines of code in a .bat file, run it
Click to expand...
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 [wink]

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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I tested it and it looks to work without any problem!
no need for that error handling :)

tx a lot HarleyQuinn

grt
grob
 
Upvote 0 Downvote
Glad to help grob [smile]

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

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.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

office365
  • Locked
  • Question
i have a problem with a button sele 2
Replies
15
Views
181
combo
combo
EliseFreedman
  • Locked
  • Question
Excel 2016 formatting problem
Replies
3
Views
50
strongm
strongm
baran121
  • Locked
  • Question
vb6 runnnig problem
Replies
2
Views
57
baran121
baran121
remeng
  • Locked
  • Question
Word Form values exported to Excel problem
Replies
17
Views
177
macropod
macropod
Mike.and.H
  • Locked
  • Question
Syntax problem using Selection.GoTo method in AttachMate macro working with MS Word
Replies
4
Views
81
combo
combo

Part and Inventory Search

Sponsor

Back
Top