Two Questions: FileSearch & Excel Instance

[xRoachx]

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

 

[Klatuu]

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

 

[xRoachx]

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!

 

[Klatuu]

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.

 

[Kevin K. Sullivan]

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:


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]

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