YAQ (Yet another question)

[Ken Loomis]

I found a serious problems with my VBA in a workbook that generates reports.
There is a "Master" workbook, that the users open and then they import
several text files to create the report. They then print that report and
save it. I never thought to remove all the code from workbook before they
saved it. Now I find that if they open one of those reports, all the
information could be wiped out. I have corrected the problem for any new
report they run, but the problem is still stored away in all the old
reports.

I figured out that if I open one of those old reports and simple remove all
the code, then save the file, everything is fine.

So what I have done is build a new report generator, that will build a list
of all the potentially problem files the first time the report generator is
run. I use FileSearch to look for "*.xls" files that contain the specific
sub name. All these files names (with paths) are added to a hidden
worksheet. Then every time the report builder is opened, I plan to correct
several of these problematic files, until they have all been fixed.

I could do this all at once, but the problem is how long it ties up the
user's machine.

Just searching the My Documents folder for problematic files takes over an
hour. And, fixing all of the files at once will take even longer. I tried
just searching for the "*.xls" files and then going back to search those for
the specific sub name but just finding the "*.xls" files takes even longer.

I can not count on the report file names being of a specific format. each
user uses a different naming convention.

Can someone suggest anything faster than this code:

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.TextOrProperty = "BuildStreetsReports"
.MatchTextExactly = False
.filename = "*.xls"
.Execute


TIA,
Ken

[William Benson]

Will the XLS files on their machine list you as the Author (Summary Tab) of
File-Properties, and is this something that you can search for using code?

Just a thought.

HTH

Bill
"Ken Loomis" <[email protected]> wrote in message
news:[email protected]...
>I found a serious problems with my VBA in a workbook that generates
>reports. There is a "Master" workbook, that the users open and then they
>import several text files to create the report. They then print that report
>and save it. I never thought to remove all the code from workbook before
>they saved it. Now I find that if they open one of those reports, all the
>information could be wiped out. I have corrected the problem for any new
>report they run, but the problem is still stored away in all the old
>reports.
>
> I figured out that if I open one of those old reports and simple remove
> all the code, then save the file, everything is fine.
>
> So what I have done is build a new report generator, that will build a
> list of all the potentially problem files the first time the report
> generator is run. I use FileSearch to look for "*.xls" files that contain
> the specific sub name. All these files names (with paths) are added to a
> hidden worksheet. Then every time the report builder is opened, I plan to
> correct several of these problematic files, until they have all been
> fixed.
>
> I could do this all at once, but the problem is how long it ties up the
> user's machine.
>
> Just searching the My Documents folder for problematic files takes over an
> hour. And, fixing all of the files at once will take even longer. I tried
> just searching for the "*.xls" files and then going back to search those
> for the specific sub name but just finding the "*.xls" files takes even
> longer.
>
> I can not count on the report file names being of a specific format. each
> user uses a different naming convention.
>
> Can someone suggest anything faster than this code:
>
> With Application.FileSearch
> .NewSearch
> .LookIn = "C:\My Documents"
> .SearchSubFolders = True
> .TextOrProperty = "BuildStreetsReports"
> .MatchTextExactly = False
> .filename = "*.xls"
> .Execute
>
>
> TIA,
> Ken
>
>
>

[Ken Loomis]

I'll try that.

Can you suggest the code to access the author property of a file?

Ken


"William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> Will the XLS files on their machine list you as the Author (Summary Tab)
> of File-Properties, and is this something that you can search for using
> code?
>
> Just a thought.
>
> HTH
>
> Bill
> "Ken Loomis" <[email protected]> wrote in message
> news:[email protected]...
>>I found a serious problems with my VBA in a workbook that generates
>>reports. There is a "Master" workbook, that the users open and then they
>>import several text files to create the report. They then print that
>>report and save it. I never thought to remove all the code from workbook
>>before they saved it. Now I find that if they open one of those reports,
>>all the information could be wiped out. I have corrected the problem for
>>any new report they run, but the problem is still stored away in all the
>>old reports.
>>
>> I figured out that if I open one of those old reports and simple remove
>> all the code, then save the file, everything is fine.
>>
>> So what I have done is build a new report generator, that will build a
>> list of all the potentially problem files the first time the report
>> generator is run. I use FileSearch to look for "*.xls" files that contain
>> the specific sub name. All these files names (with paths) are added to a
>> hidden worksheet. Then every time the report builder is opened, I plan to
>> correct several of these problematic files, until they have all been
>> fixed.
>>
>> I could do this all at once, but the problem is how long it ties up the
>> user's machine.
>>
>> Just searching the My Documents folder for problematic files takes over
>> an hour. And, fixing all of the files at once will take even longer. I
>> tried just searching for the "*.xls" files and then going back to search
>> those for the specific sub name but just finding the "*.xls" files takes
>> even longer.
>>
>> I can not count on the report file names being of a specific format. each
>> user uses a different naming convention.
>>
>> Can someone suggest anything faster than this code:
>>
>> With Application.FileSearch
>> .NewSearch
>> .LookIn = "C:\My Documents"
>> .SearchSubFolders = True
>> .TextOrProperty = "BuildStreetsReports"
>> .MatchTextExactly = False
>> .filename = "*.xls"
>> .Execute
>>
>>
>> TIA,
>> Ken
>>
>>
>>
>
>

[Doug Glancy]

Ken,

I've never used the FileSearch object, so thanks for opportunity for
education! This seems to work. There doesn't seem to be a unique Author
property (I thought Creator might work but it refers to the creator program,
not person) so TextOrProperty seems to be the way to go. I switched the
exact match field to True. This is not especially quick, it took almost a
minute.

Sub test()
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Doug\My Documents\"
.SearchSubFolders = True
.MatchTextExactly = True
.Filename = "*.xls"
.TextOrProperty = "Doug Glancy"
.Execute
Debug.Print .FoundFiles.Count
For i = 1 To .FoundFiles.Count
Debug.Print .FoundFiles(i)
Next i
End With
End Sub


hth,

Doug

"Ken Loomis" <[email protected]> wrote in message
news:[email protected]...
> I'll try that.
>
> Can you suggest the code to access the author property of a file?
>
> Ken
>
>
> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> Will the XLS files on their machine list you as the Author (Summary Tab)
>> of File-Properties, and is this something that you can search for using
>> code?
>>
>> Just a thought.
>>
>> HTH
>>
>> Bill
>> "Ken Loomis" <[email protected]> wrote in message
>> news:[email protected]...
>>>I found a serious problems with my VBA in a workbook that generates
>>>reports. There is a "Master" workbook, that the users open and then they
>>>import several text files to create the report. They then print that
>>>report and save it. I never thought to remove all the code from workbook
>>>before they saved it. Now I find that if they open one of those reports,
>>>all the information could be wiped out. I have corrected the problem for
>>>any new report they run, but the problem is still stored away in all the
>>>old reports.
>>>
>>> I figured out that if I open one of those old reports and simple remove
>>> all the code, then save the file, everything is fine.
>>>
>>> So what I have done is build a new report generator, that will build a
>>> list of all the potentially problem files the first time the report
>>> generator is run. I use FileSearch to look for "*.xls" files that
>>> contain the specific sub name. All these files names (with paths) are
>>> added to a hidden worksheet. Then every time the report builder is
>>> opened, I plan to correct several of these problematic files, until they
>>> have all been fixed.
>>>
>>> I could do this all at once, but the problem is how long it ties up the
>>> user's machine.
>>>
>>> Just searching the My Documents folder for problematic files takes over
>>> an hour. And, fixing all of the files at once will take even longer. I
>>> tried just searching for the "*.xls" files and then going back to search
>>> those for the specific sub name but just finding the "*.xls" files takes
>>> even longer.
>>>
>>> I can not count on the report file names being of a specific format.
>>> each user uses a different naming convention.
>>>
>>> Can someone suggest anything faster than this code:
>>>
>>> With Application.FileSearch
>>> .NewSearch
>>> .LookIn = "C:\My Documents"
>>> .SearchSubFolders = True
>>> .TextOrProperty = "BuildStreetsReports"
>>> .MatchTextExactly = False
>>> .filename = "*.xls"
>>> .Execute
>>>
>>>
>>> TIA,
>>> Ken
>>>
>>>
>>>
>>
>>
>
>