Two Questions: FileSearch & Excel Instance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xRoachx - 27 Sep 2005 18:26 GMT

I have two seperate questions regarding FileSearch & an Excel Instance. I'll
start with the latter. Thanks for the assistance.

I have a module that opens an Excel instance (objExcel) and closes it at the
end of the module by using objExcel.Quit & objExcel = Nothing. However, the
Excel instance is not closed correctly (still shows in the Task Manager) and
causes problems when the module is executed again. Each time I have to go to
Task Manager and manually close the instance.

The second question is regarding the following module. The module works
great the first time. However, if a second file is modified with a newer
date/time, the module is still displaying the previously last modified file
instead of the most recent modified file:

Function fLastModified(strFolder As String) As String
On Error GoTo Err_Handler

Dim strFileName As String

With Application.FileSearch
.NewSearch
.LookIn = strFolder
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
.Execute SortBy:=msoSortBySize
.LookIn = strFolder
.FileType = msoFileTypeExcelWorkbooks

If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
strFileName = .FoundFiles(1)
'Application.Workbooks.Open strFileName
fLastModified = strFileName
End If
End With

Exit_Sub:
Exit Function
Err_Handler:
Resume Exit_Sub

End Function

Reply to this Message

Klatuu - 27 Sep 2005 19:50 GMT

As to your FileSearch object question:
1. You identify the file type twice. This is not necessary.
2. You are sorting by file size. If you want last modified, you should be
using msoSortByLastModified

As to your Excel Instance question:
This can be really frustrating. I can't give you a specific. Even If I
saw the code, it would take some experimenting to find it. I know, I have
been through the same exercise. It has to do with how you are referencing
your objects. If the reference to an object is not correctly qualified, it
will start another instances of Excel without your even knowing it. Now you
have an additional instance, and when you do your quit, it will kill the
instance you established, but not the one (or more) established accidently.
I have made it a habit to always clearly establish my references based on a
previous reference, starting with the applcation level.
so, something like this seems to work well:
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select

> I have two seperate questions regarding FileSearch & an Excel Instance. I'll
> start with the latter. Thanks for the assistance.
[quoted text clipped - 37 lines]
>
> End Function

Reply to this Message

xRoachx - 27 Sep 2005 20:09 GMT

Klatuu,

Good catch on the FileSearch code but I'm still having the same problem. :-(

As for the Excel instance, I'm going to do some more research.

Thanks for the reply!

> As to your FileSearch object question:
> 1. You identify the file type twice. This is not necessary.
[quoted text clipped - 70 lines]
> >
> > End Function

Reply to this Message

Klatuu - 27 Sep 2005 20:16 GMT

hmmmmm. It may be the sort order, still. I don't know if you can sort either
ascending or descending with a filesearch, but try looking at the last file
in the searrch. It would be .FoundFiles.Count. I would look through what is
returned in debug mode and see what I got.

> Klatuu,
>
[quoted text clipped - 78 lines]
> > >
> > > End Function

Reply to this Message

Kevin K. Sullivan - 27 Sep 2005 20:33 GMT

xRoachx,

PMFJI. Watch out for the use of ActiveWorksheet in Excel (or
ActiveDocument in Word). They have stung me in the past. These methods
may create another instance of the program you are controlling. I think
most people end up using these methods because the macro recorder sticks
them in (it doesn't know any better). Klatuu has it right, but I would
change the two lines:

>> xlBook.Worksheets("Actuals_res_export").Activate
>> ActiveSheet.Range("F3").Select

to one:

xlBook.Worksheets("Actuals_res_export").Range("F3").Select

or use extra objects as necessary:

Dim oWS as Object
Dim oRange as Object

Set oWS = xlBook.WorkSheets("Actuals_res_export")
oWS.Activate
Set oRange = oWS.Range("F3")
oRange.Select

...
'Close it all down
Set oRange = Nothing
Set oWS = Nothing
xlBook.Save
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

--------
Using *specific* objects should also protect you from concurrent uses of
Excel, Word, etc.

HTH,

Kevin

> Klatuu,
>
[quoted text clipped - 78 lines]
>>>
>>>End Function

Reply to this Message

Klatuu - 27 Sep 2005 20:39 GMT

That was my original approach. I don't remember exactly why, but I changed
it because I was having problems with it.

> xRoachx,
>
[quoted text clipped - 122 lines]
> >>>
> >>>End Function

Reply to this Message