Missing VBA Code in Excel 2007
 

Hi,

I have been trying Excel 2007 and one of my models that works fine in Excel
2000, doesn't run the VBA code below, if I retype this the option from the
prompt , suggests that FileSearch is no long an option. Surely this can't
be true?

With Application.FileSearch
.NewSearch

Thanks, Rob

Missing VBA Code in Excel 2007
 

I am afraid that it is true, FileSearch has been dropped from 2007 (it was
buggy, and MS decided to drop it rather than invest in fixing it).

Look at Dir in help, it will achieve the same results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Hi,

I have been trying Excel 2007 and one of my models that works fine in
Excel
2000, doesn't run the VBA code below, if I retype this the option from the
prompt , suggests that FileSearch is no long an option. Surely this can't
be true?

With Application.FileSearch
.NewSearch

Thanks, Rob

Missing VBA Code in Excel 2007
 

Bob,

Shame about the change in 2007, have looked at help but unsure where to go
with this change in code as had removed Excel 2000 and can't search help to
find out what FileSearch was doing. Is it a case of changing just part of
the code or all of it? I have placed part of the code below that seems to
be falling over. Thanks, Rob

With Application.FileSearch
.NewSearch
.LookIn = "N:\UniSE\" & Worksheets("Sheet1").Range("Year") &
"\SALES\"
.SearchSubFolders = False
.Filename = "SALE" & Worksheets("Sheet1").Range("WK") & ".xls"
.MatchTextExactly = True
If .Execute() Then



"Bob Phillips" wrote in message
...
I am afraid that it is true, FileSearch has been dropped from 2007 (it was
buggy, and MS decided to drop it rather than invest in fixing it).

Look at Dir in help, it will achieve the same results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Hi,

I have been trying Excel 2007 and one of my models that works fine in
Excel
2000, doesn't run the VBA code below, if I retype this the option from
the
prompt , suggests that FileSearch is no long an option. Surely this
can't
be true?

With Application.FileSearch
.NewSearch

Thanks, Rob

Missing VBA Code in Excel 2007
 

Try this instead

If Dir("N:\UniSE\" & Worksheets("Sheet1").Range("Year") & "\SALES\" & _
"SALE" & Worksheets("Sheet1").Range("WK") & ".xls") < "" Then


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Bob,

Shame about the change in 2007, have looked at help but unsure where to go
with this change in code as had removed Excel 2000 and can't search help
to
find out what FileSearch was doing. Is it a case of changing just part of
the code or all of it? I have placed part of the code below that seems to
be falling over. Thanks, Rob

With Application.FileSearch
.NewSearch
.LookIn = "N:\UniSE\" & Worksheets("Sheet1").Range("Year") &
"\SALES\"
.SearchSubFolders = False
.Filename = "SALE" & Worksheets("Sheet1").Range("WK") & ".xls"
.MatchTextExactly = True
If .Execute() Then



"Bob Phillips" wrote in message
...
I am afraid that it is true, FileSearch has been dropped from 2007 (it
was
buggy, and MS decided to drop it rather than invest in fixing it).

Look at Dir in help, it will achieve the same results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Hi,

I have been trying Excel 2007 and one of my models that works fine in
Excel
2000, doesn't run the VBA code below, if I retype this the option from
the
prompt , suggests that FileSearch is no long an option. Surely this
can't
be true?

With Application.FileSearch
.NewSearch

Thanks, Rob

Missing VBA Code in Excel 2007
 

Thanks for you help. The revised code worked insomuch that the run error
happened after the DIR code on

Workbooks.Open Filename:=.FoundFiles(1)

If the code could work, I'd then somehow need to save the file in DBF format
which Excel 2007 doesn't appear to support. Hopefully, 2008 will see a
version which is more compatible.

Thanks, Rob

"Bob Phillips" wrote in message
...
Try this instead

If Dir("N:\UniSE\" & Worksheets("Sheet1").Range("Year") & "\SALES\" & _
"SALE" & Worksheets("Sheet1").Range("WK") & ".xls") < "" Then


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Bob,

Shame about the change in 2007, have looked at help but unsure where to
go
with this change in code as had removed Excel 2000 and can't search help
to
find out what FileSearch was doing. Is it a case of changing just part
of
the code or all of it? I have placed part of the code below that seems
to
be falling over. Thanks, Rob

With Application.FileSearch
.NewSearch
.LookIn = "N:\UniSE\" & Worksheets("Sheet1").Range("Year") &
"\SALES\"
.SearchSubFolders = False
.Filename = "SALE" & Worksheets("Sheet1").Range("WK") & ".xls"
.MatchTextExactly = True
If .Execute() Then



"Bob Phillips" wrote in message
...
I am afraid that it is true, FileSearch has been dropped from 2007 (it
was
buggy, and MS decided to drop it rather than invest in fixing it).

Look at Dir in help, it will achieve the same results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Hi,

I have been trying Excel 2007 and one of my models that works fine in
Excel
2000, doesn't run the VBA code below, if I retype this the option from
the
prompt , suggests that FileSearch is no long an option. Surely this
can't
be true?

With Application.FileSearch
.NewSearch

Thanks, Rob