Good day:
FUNCTIONAL BACKGROUND: [programmatically] application needs to search a given [i.e. predefined] directory for a list of "submitted" Excel files [Project Plans from PMs] to be read into an Access Db. After successful read, (a)file is copied to a different directory ["approved folder"], (b) "deleted" from "submitted" directory (c) timestamp written into "arbitrary but known" cell for verification/validation purposes. When PM desires to "check-out" plan, application "reverses" the process by (a) searching "approved" directory, (b) validating file [details here not relevant], (c) copying excel file to user-defined directory. This user may modify the plan and "re-submit" file into the defined submittal directory where the process of reading in the plan can occur again. Fairly straight-forward process [minus the complex/necessary business rule implementations placed upon the system]
ISSUE: FileSearch Object being used to retrieve list of excel files in pre-defined directory is not working as expected. It is as though it remembers search criteria even when I try like "hell" to force it to forget.
NOTE: [FS is dimmed as an Application.FileSearch object, I even tried FS dimmed as Excel.FileSearch object]. It returns its file list [.FoundFiles] to a list box on a GUI form for the user to select. Running on XP Prof with latest SP, Access 2000 file format. Ran locally on full-priveliged machine.
'[not original code- but is current spaghetti patch work]
'******************
With FS
.NewSearch - [claims to clear previous search knowledge]
.LookIn = "" - [try to force it to forget]
.LookIn = SubmittedDir - [this variable has the valid pre-defined dir.]
.SearchSubFolders = False - [false since I am heading directly to the folder]
.RefreshScopes - [claims to reset the default search folders which normally are C:\ root, MyDocuments, MyComputer and DeskTop]
.MatchTextExactly = False - [default but set anyway]
.MatchAllWordForms = False- [default but set anyway]
.FileName = "*.xls"- [obvious from above background]
.FileType = msoFileTypeAllFiles [asking for Workbooks constant here fails the execute method, don't know why either? Fails mean execute returns 0 even if there are 999 excel files in the folder!!!]
.LastModified = msoLastModifiedAnyTime - [default but set anyway]
NumSubmittedProjects = .Execute(msoSortByFileName, msoSortOrderAscending, True)
'******************
eg. TEST:
intial state: 5 files (A,B,C,D,E) in submitted folder [SF], 0 files in Approved Folder [AF]
(1) Run app [Access]
(2) FS finds 5 files in SF, user selects A, "app processes" [details not relevant], A copied to AF, A deleted from SF - ALL IS GOOD!
Intermediate state: 4 files (-,B,C,D,E) in submitted folder [SF], 1 file (A) in Approved Folder [AF]
(3) Close app, re-open [in fact compile, compact/repair - do what ever you want to this Acces app file]
(4) run app, FS finds (A) in AF, (B,C,D,E) in SF, user selects A, A copied to user defined location - ALL IS GOOD STILL!
(5) User modifies A, places A back into SF.
(6) optional [close app, compile code, compact, copy to new dir, whatever - I tried them ALL!]
(7) run app again, FS finds 4 files in SF [cannot "see" A sitting there]!! Why?
IN FACT: I can completely delete SF altogether off my machine, run the APP and FS still returns (B,C,D,E) to the listbox on the GUI form. What the heck am I missing here? Thank you so much for your assistance.
Teddy
FUNCTIONAL BACKGROUND: [programmatically] application needs to search a given [i.e. predefined] directory for a list of "submitted" Excel files [Project Plans from PMs] to be read into an Access Db. After successful read, (a)file is copied to a different directory ["approved folder"], (b) "deleted" from "submitted" directory (c) timestamp written into "arbitrary but known" cell for verification/validation purposes. When PM desires to "check-out" plan, application "reverses" the process by (a) searching "approved" directory, (b) validating file [details here not relevant], (c) copying excel file to user-defined directory. This user may modify the plan and "re-submit" file into the defined submittal directory where the process of reading in the plan can occur again. Fairly straight-forward process [minus the complex/necessary business rule implementations placed upon the system]
ISSUE: FileSearch Object being used to retrieve list of excel files in pre-defined directory is not working as expected. It is as though it remembers search criteria even when I try like "hell" to force it to forget.
NOTE: [FS is dimmed as an Application.FileSearch object, I even tried FS dimmed as Excel.FileSearch object]. It returns its file list [.FoundFiles] to a list box on a GUI form for the user to select. Running on XP Prof with latest SP, Access 2000 file format. Ran locally on full-priveliged machine.
'[not original code- but is current spaghetti patch work]
'******************
With FS
.NewSearch - [claims to clear previous search knowledge]
.LookIn = "" - [try to force it to forget]
.LookIn = SubmittedDir - [this variable has the valid pre-defined dir.]
.SearchSubFolders = False - [false since I am heading directly to the folder]
.RefreshScopes - [claims to reset the default search folders which normally are C:\ root, MyDocuments, MyComputer and DeskTop]
.MatchTextExactly = False - [default but set anyway]
.MatchAllWordForms = False- [default but set anyway]
.FileName = "*.xls"- [obvious from above background]
.FileType = msoFileTypeAllFiles [asking for Workbooks constant here fails the execute method, don't know why either? Fails mean execute returns 0 even if there are 999 excel files in the folder!!!]
.LastModified = msoLastModifiedAnyTime - [default but set anyway]
NumSubmittedProjects = .Execute(msoSortByFileName, msoSortOrderAscending, True)
'******************
eg. TEST:
intial state: 5 files (A,B,C,D,E) in submitted folder [SF], 0 files in Approved Folder [AF]
(1) Run app [Access]
(2) FS finds 5 files in SF, user selects A, "app processes" [details not relevant], A copied to AF, A deleted from SF - ALL IS GOOD!
Intermediate state: 4 files (-,B,C,D,E) in submitted folder [SF], 1 file (A) in Approved Folder [AF]
(3) Close app, re-open [in fact compile, compact/repair - do what ever you want to this Acces app file]
(4) run app, FS finds (A) in AF, (B,C,D,E) in SF, user selects A, A copied to user defined location - ALL IS GOOD STILL!
(5) User modifies A, places A back into SF.
(6) optional [close app, compile code, compact, copy to new dir, whatever - I tried them ALL!]
(7) run app again, FS finds 4 files in SF [cannot "see" A sitting there]!! Why?
IN FACT: I can completely delete SF altogether off my machine, run the APP and FS still returns (B,C,D,E) to the listbox on the GUI form. What the heck am I missing here? Thank you so much for your assistance.
Teddy
] ensured file placed back into SF had the correct file type [ensure file extension as well as ensured folder options did *NOT* hide extensions under its properties]
