Hi all,

I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
memory stick and the files I'm searching are in the same USB in a folder
which is in the same folder as the file I'musing this code and I;m getting an
error "Object doesn't support this action". This is the code I'm using:

Dim FileFound As Variant

With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\Properties"
.Execute

For Each FileFound In .FoundFiles
If Mid(FileFound, 9) >= PropertyID Then
With
Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")

.Value = .Value + 1
PropertyID = .Value
End With
End If
Next FileFound
End With

Any idea what's wrong with the above code?

--
A. Ch. Eirinberg

The problem is xl2007. It was improved(?) by removing FileSearch.
To be fair, FileSearch wasn't that reliable in the older xl versions.
You will have to use the Dir function or the Scripting.FileSystemObject.
--
Jim Cone
Portland, Oregon USA



"Howard31"
<(E-Mail Removed)>
wrote in message
Hi all,
I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
memory stick and the files I'm searching are in the same USB in a folder
which is in the same folder as the file I'musing this code and I;m getting an
error "Object doesn't support this action". This is the code I'm using:

Dim FileFound As Variant
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\Properties"
.Execute

For Each FileFound In .FoundFiles
If Mid(FileFound, 9) >= PropertyID Then
With
Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")

.Value = .Value + 1
PropertyID = .Value
End With
End If
Next FileFound
End With

Any idea what's wrong with the above code?
--
A. Ch. Eirinberg

The code should look something like the code below. I heard that filesearch
was suppose to be fixed in excel 2007. Make sure you have all the office
2007 updates.


PropertyID = 0
Folder = ThisWorkbook.Path & "\Properties\"
FName = Dir(Folder & "*.*")
Do While FName <> ""

If Mid(FName, 9) >= PropertyID Then
With
Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
.Value = .Value + 1
PropertyID = .Value
End With
FName = Dir()
Loop


"Howard31" wrote:

> Hi all,
>
> I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
> memory stick and the files I'm searching are in the same USB in a folder
> which is in the same folder as the file I'musing this code and I;m getting an
> error "Object doesn't support this action". This is the code I'm using:
>
> Dim FileFound As Variant
>
> With Application.FileSearch
> .NewSearch
> .LookIn = ThisWorkbook.Path & "\Properties"
> .Execute
>
> For Each FileFound In .FoundFiles
> If Mid(FileFound, 9) >= PropertyID Then
> With
> Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
>
> .Value = .Value + 1
> PropertyID = .Value
> End With
> End If
> Next FileFound
> End With
>
> Any idea what's wrong with the above code?
>
> --
> A. Ch. Eirinberg

Thanks Jim, I suspected this.
--
A. Ch. Eirinberg


"Jim Cone" wrote:

> The problem is xl2007. It was improved(?) by removing FileSearch.
> To be fair, FileSearch wasn't that reliable in the older xl versions.
> You will have to use the Dir function or the Scripting.FileSystemObject.
> --
> Jim Cone
> Portland, Oregon USA
>
>
>
> "Howard31"
> <(E-Mail Removed)>
> wrote in message
> Hi all,
> I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
> memory stick and the files I'm searching are in the same USB in a folder
> which is in the same folder as the file I'musing this code and I;m getting an
> error "Object doesn't support this action". This is the code I'm using:
>
> Dim FileFound As Variant
> With Application.FileSearch
> .NewSearch
> .LookIn = ThisWorkbook.Path & "\Properties"
> .Execute
>
> For Each FileFound In .FoundFiles
> If Mid(FileFound, 9) >= PropertyID Then
> With
> Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
>
> .Value = .Value + 1
> PropertyID = .Value
> End With
> End If
> Next FileFound
> End With
>
> Any idea what's wrong with the above code?
> --
> A. Ch. Eirinberg
>

Thanks Simon, I suspected this.
--
A. Ch. Eirinberg


"Simon Lloyd" wrote:

>
> As far as i am aware the FileSearch has been dropped in xl2007.Howard31;230775 Wrote:
> > Hi all,
> >
> > I'm using Application.FileSearch in Excel 2007 in a file which is in a
> > USB
> > memory stick and the files I'm searching are in the same USB in a
> > folder
> > which is in the same folder as the file I'musing this code and I;m
> > getting an
> > error "Object doesn't support this action". This is the code I'm using:
> >
> > Dim FileFound As Variant
> >
> > With Application.FileSearch
> > .NewSearch
> > .LookIn = ThisWorkbook.Path & "\Properties"
> > .Execute
> >
> > For Each FileFound In .FoundFiles
> > If Mid(FileFound, 9) >= PropertyID Then
> > With
> > Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
> >
> > .Value = .Value + 1
> > PropertyID = .Value
> > End With
> > End If
> > Next FileFound
> > End With
> >
> > Any idea what's wrong with the above code?
> >
> > --
> > A. Ch. Eirinberg
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'The Code Cage' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64490
>
>

Hi Joel,

I read from other responses that FileSearch was droped from Excel 2007, I
will have to use the Dir statement as you described, I didn't have time yet
to study it properly but I will soon.

Thanks for your help!
--
A. Ch. Eirinberg


"Joel" wrote:

> The code should look something like the code below. I heard that filesearch
> was suppose to be fixed in excel 2007. Make sure you have all the office
> 2007 updates.
>
>
> PropertyID = 0
> Folder = ThisWorkbook.Path & "\Properties\"
> FName = Dir(Folder & "*.*")
> Do While FName <> ""
>
> If Mid(FName, 9) >= PropertyID Then
> With
> Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
> .Value = .Value + 1
> PropertyID = .Value
> End With
> FName = Dir()
> Loop
>
>
> "Howard31" wrote:
>
> > Hi all,
> >
> > I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
> > memory stick and the files I'm searching are in the same USB in a folder
> > which is in the same folder as the file I'musing this code and I;m getting an
> > error "Object doesn't support this action". This is the code I'm using:
> >
> > Dim FileFound As Variant
> >
> > With Application.FileSearch
> > .NewSearch
> > .LookIn = ThisWorkbook.Path & "\Properties"
> > .Execute
> >
> > For Each FileFound In .FoundFiles
> > If Mid(FileFound, 9) >= PropertyID Then
> > With
> > Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
> >
> > .Value = .Value + 1
> > PropertyID = .Value
> > End With
> > End If
> > Next FileFound
> > End With
> >
> > Any idea what's wrong with the above code?
> >
> > --
> > A. Ch. Eirinberg

Ron de Bruin shares a couple of replacements:
http://www.rondebruin.nl/copy3.htm (for Dir())
and
http://www.rondebruin.nl/fso.htm (for FSO)



Howard31 wrote:
>
> Hi all,
>
> I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
> memory stick and the files I'm searching are in the same USB in a folder
> which is in the same folder as the file I'musing this code and I;m getting an
> error "Object doesn't support this action". This is the code I'm using:
>
> Dim FileFound As Variant
>
> With Application.FileSearch
> .NewSearch
> .LookIn = ThisWorkbook.Path & "\Properties"
> .Execute
>
> For Each FileFound In .FoundFiles
> If Mid(FileFound, 9) >= PropertyID Then
> With
> Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
>
> .Value = .Value + 1
> PropertyID = .Value
> End With
> End If
> Next FileFound
> End With
>
> Any idea what's wrong with the above code?
>
> --
> A. Ch. Eirinberg

--

Dave Peterson

If you like the method of Application.FileSearch, try the class method that
is similar at this link. http://www.mrexcel.com/forum/showthread.php?t=369982

That class uses FSO to iterate the subfolders.

I used it in an example xls.
http://www.4shared.com/file/87591234...nd_Excel4.html

Thanks Kenneth
--
A. Ch. Eirinberg


"Kenneth Hobson" wrote:

> If you like the method of Application.FileSearch, try the class method that
> is similar at this link. http://www.mrexcel.com/forum/showthread.php?t=369982
>
> That class uses FSO to iterate the subfolders.
>
> I used it in an example xls.
> http://www.4shared.com/file/87591234...nd_Excel4.html

Thanks Dave
--
A. Ch. Eirinberg


"Dave Peterson" wrote:

> Ron de Bruin shares a couple of replacements:
> http://www.rondebruin.nl/copy3.htm (for Dir())
> and
> http://www.rondebruin.nl/fso.htm (for FSO)
>
>
>
> Howard31 wrote:
> >
> > Hi all,
> >
> > I'm using Application.FileSearch in Excel 2007 in a file which is in a USB
> > memory stick and the files I'm searching are in the same USB in a folder
> > which is in the same folder as the file I'musing this code and I;m getting an
> > error "Object doesn't support this action". This is the code I'm using:
> >
> > Dim FileFound As Variant
> >
> > With Application.FileSearch
> > .NewSearch
> > .LookIn = ThisWorkbook.Path & "\Properties"
> > .Execute
> >
> > For Each FileFound In .FoundFiles
> > If Mid(FileFound, 9) >= PropertyID Then
> > With
> > Workbooks("Statement_Invoice_Retrieval_Info.xla").Worksheets("Sheet_ID").Range("PropertyID")
> >
> > .Value = .Value + 1
> > PropertyID = .Value
> > End With
> > End If
> > Next FileFound
> > End With
> >
> > Any idea what's wrong with the above code?
> >
> > --
> > A. Ch. Eirinberg
>
> --
>
> Dave Peterson
>