I have a 2002 access database that verifies a file is resident (using FileSearch) before trying to import it (see MY CODE). This works fine on the PC's that have Access 2002 but aborts on those that have 2007.
When I click on the command to run the module I get:
Run-time error '2455": You entered an expression that has an invalid reference to the property FileSearch.
I tried looking at Micosoft help for this issue, but according to it, my code is fine (See Microsoft Help). When I step through the code, the error pops up as soon as I try to "Set fs = Application.FileSearch". Can anyone see what I may be overlooking?
MY CODE:
Function PORT_USAGE_IMPORT_WED()
Dim Inputport, loadedport, Mystr, Mydate, fs, Sortby
Set fs = Application.FileSearch
On Error GoTo PORT_USAGE_IMPORT_WED_Err
Mydate = Date - 1
Mystr = Format(Mydate, "mmddyy")
Inputport = "U:\portusage\Wed\port_111_0000.CSV"
loadedport = "U:\portusage\Imported\Wed\" & Mystr & "_port_111_0000.CSV"
With fs
.LookIn = "U:\portusage\Wed\"
.FileName = "port_111_0000.CSV"
' THERE ARE SEVERAL FILES UNDER "U:\portusage\WED\" that I look for
If .Execute > 0 Then
DoCmd.Echo True, "IMPORTING PH111 Midnight PORT USAGE DATA."
DoCmd.TransferText acImportDelim, "IMPORT_PORT_USAGE", "WED_PORT_USAGE_111", "U:\portusage\Wed\PORT_111_0000.CSV", False, ""
Name Inputport As loadedport
Else
MsgBox "portusage\Wed\port_111_0000.CSV file not found."
End If
MICROSOFT HELP:
Office Developer ReferenceFileSearch.LookIn Property
Gets or sets the folder to be searched during the specified file search. Read/write
Syntax
expression.LookIn
expression A variable that represents a FileSearch object.
Example
This example searches the My Documents folders for all files that begin with "Cmd" and displays the name and location of each file that’s found. Visual Basic for ApplicationsSet fs = Application.FileSearchWith fs .LookIn = "C:\My Documents" .FileName = "cmd*.*" If .Execute > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i Else MsgBox "There were no files found." End IfEnd With
When I click on the command to run the module I get:
Run-time error '2455": You entered an expression that has an invalid reference to the property FileSearch.
I tried looking at Micosoft help for this issue, but according to it, my code is fine (See Microsoft Help). When I step through the code, the error pops up as soon as I try to "Set fs = Application.FileSearch". Can anyone see what I may be overlooking?
MY CODE:
Function PORT_USAGE_IMPORT_WED()
Dim Inputport, loadedport, Mystr, Mydate, fs, Sortby
Set fs = Application.FileSearch
On Error GoTo PORT_USAGE_IMPORT_WED_Err
Mydate = Date - 1
Mystr = Format(Mydate, "mmddyy")
Inputport = "U:\portusage\Wed\port_111_0000.CSV"
loadedport = "U:\portusage\Imported\Wed\" & Mystr & "_port_111_0000.CSV"
With fs
.LookIn = "U:\portusage\Wed\"
.FileName = "port_111_0000.CSV"
' THERE ARE SEVERAL FILES UNDER "U:\portusage\WED\" that I look for
If .Execute > 0 Then
DoCmd.Echo True, "IMPORTING PH111 Midnight PORT USAGE DATA."
DoCmd.TransferText acImportDelim, "IMPORT_PORT_USAGE", "WED_PORT_USAGE_111", "U:\portusage\Wed\PORT_111_0000.CSV", False, ""
Name Inputport As loadedport
Else
MsgBox "portusage\Wed\port_111_0000.CSV file not found."
End If
MICROSOFT HELP:
Office Developer ReferenceFileSearch.LookIn Property
Gets or sets the folder to be searched during the specified file search. Read/write
Syntax
expression.LookIn
expression A variable that represents a FileSearch object.
Example
This example searches the My Documents folders for all files that begin with "Cmd" and displays the name and location of each file that’s found. Visual Basic for ApplicationsSet fs = Application.FileSearchWith fs .LookIn = "C:\My Documents" .FileName = "cmd*.*" If .Execute > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i Else MsgBox "There were no files found." End IfEnd With