I don't think the appl is really savving memory. try again without the appl

Sub ReadNewContract(fileNo, arrMaster)

Dim MasterFile
Dim f, c, r
Dim lngCount As Long

Dim arrTemp() As Variant

Dim Master As Workbook
Dim masterSht As Worksheet
Dim rowsMaster, colsMaster, lastCellMaster
Dim rowMax, rightCol
Dim FoundIndent

Dim matCount, matTotal
Dim ctCellMaster
Dim testRowCountMat
Dim alertStat
Dim tempXLFile

Dim xlApp As New Excel.Application 'ADDED FOR MEMORY

Dim FileString As String 'ADDED FOR MEMORY
xlApp.Application.Visible = True 'ADDED FOR MEMORY

'################################################# ###########################################
'########### READ IN MASTER FILE
'################################################# ###########################################

If fileNo = 1 Or IsEmpty(fileLocExcel) Then
fileLocExcel = Get_File_Info(arrFiles(fileNo, colFileName), "Directory")
End If

FileString = arrFiles(fileNo, colFileName) 'ADDED FOR MEMORY

xlApp.Workbooks.Open (FileString) 'Focus is now on the workbook 'ADDED FOR
MEMORY

Set Master = ActiveWorkbook
Set masterSht = .ActiveSheet
MasterFile = Master.Name

lastCellMaster = LastCellIn(masterSht)
rowsMaster = LastRowIn(masterSht)
arrFiles(fileNo, colFileRows) = rowsMaster
colsMaster = LastColIn(masterSht)

colsMaster = Mid(Cells(1, colsMaster).Address, 2, 1)

If rowsMaster = Empty Or colsMaster = Empty Then
'File was not converted to Excel propertly
arrFiles(fileNo, colFileStat) = "Empty File"
Master.Close SaveChanges:=False
errCount = errCount + 1
' 1 'KeyErr Anchor: Name
arrErrors(errCount, colKeyErrFile) = _
Get_File_Info(arrFiles(fileNo, colFileName), "FileName")
' 2 'KeyErrAnchor: Anchor or Code
arrErrors(errCount, colKeyErrCode) = "Empty Excel File"
' 3 'KeyErr Anchor: Name
arrErrors(errCount, colKeyErrDesc) = "Empty Excel File"
' KeyErr Anchor:Special Function to perform (such as count repeats)
arrErrors(errCount, colKeyErrStat) = "Empty Excel File"
Exit Sub
End If

ctCellMaster = 0

arrTemp = masterSht.Range("A1:" & colsMaster & rowsMaster).Value

arrMaster = arrTemp

Master.Close SaveChanges:=False
'Application.DisplayAlerts = alertStat

End Sub


"MikeZz" wrote:

Hi Joel,
Below is the code I am currently testing:
I've tried to use the Application.ScreenUpdating = False in the procedure
that calls this one but I still get the blinking (Focusing and Refocusing on
Excel).

I also seem to have another dilema. On my computer at Home, Task Manager
only had shown the single Excel Task when I was all done. At work, it shows
a new task for each file I open and both the qty of Processes and Memory
Usage continues to grow. Is there an options setting somewhere that is
impacting this?

Thanks again for all your help,

Sub ReadNewContract(fileNo, arrMaster)

Dim MasterFile
Dim f, c, r
Dim lngCount As Long

Dim arrTemp() As Variant

Dim Master As Workbook
Dim masterSht As Worksheet
Dim rowsMaster, colsMaster, lastCellMaster
Dim rowMax, rightCol
Dim FoundIndent

Dim matCount, matTotal
Dim ctCellMaster
Dim testRowCountMat
Dim alertStat
Dim tempXLFile

Dim xlApp As New Excel.Application 'ADDED FOR MEMORY

Dim FileString As String 'ADDED FOR MEMORY
xlApp.Application.Visible = True 'ADDED FOR MEMORY

'################################################# ###########################################
'########### READ IN MASTER FILE
'################################################# ###########################################

If fileNo = 1 Or IsEmpty(fileLocExcel) Then
fileLocExcel = Get_File_Info(arrFiles(fileNo, colFileName), "Directory")
End If

FileString = arrFiles(fileNo, colFileName) 'ADDED FOR MEMORY

xlApp.Workbooks.Open (FileString) 'Focus is now on the workbook 'ADDED FOR
MEMORY

Set Master = xlApp.ActiveWorkbook
Set masterSht = xlApp.ActiveSheet
MasterFile = Master.Name

lastCellMaster = LastCellIn(masterSht)
rowsMaster = LastRowIn(masterSht)
arrFiles(fileNo, colFileRows) = rowsMaster
colsMaster = LastColIn(masterSht)

colsMaster = Mid(Cells(1, colsMaster).Address, 2, 1)

If rowsMaster = Empty Or colsMaster = Empty Then
'File was not converted to Excel propertly
arrFiles(fileNo, colFileStat) = "Empty File"
Master.Close SaveChanges:=False
errCount = errCount + 1
arrErrors(errCount, colKeyErrFile) = Get_File_Info(arrFiles(fileNo,
colFileName), "FileName") ' 1 'KeyErr Anchor: Name
arrErrors(errCount, colKeyErrCode) = "Empty Excel File" ' 2 'KeyErr
Anchor: Anchor or Code
arrErrors(errCount, colKeyErrDesc) = "Empty Excel File" ' 3 'KeyErr
Anchor: Name
arrErrors(errCount, colKeyErrStat) = "Empty Excel File" 'KeyErr Anchor:
Special Function to perform (such as count repeats)
Exit Sub
End If

ctCellMaster = 0

arrTemp = masterSht.Range("A1:" & colsMaster & rowsMaster).Value

arrMaster = arrTemp

Master.Close SaveChanges:=False
'Application.DisplayAlerts = alertStat

Set masterSht = Nothing
Set Master = Nothing
xlApp.Quit
Set xlApp = Nothing 'ADDED FOR MEMORY

End Sub

"Joel" wrote:

1) To speed up execution of your code and to stop blinking turn off screen
updting at the beginning of the macro and then turn it back on at the end

Application.ScreenUpdating = False

Application.ScreenUpdating = True

2) Excel is sometime bad at comnpacting workbooks to reduce memory size. It
may be best to create a new workbook with the your condensed table and savig
the final results as a new file. This will probably cure youor memory
problem. I suspect that maybe when you are creating the table you maybe
copying formulas rather than just the values which may be the reason for the
memory error.


"MikeZz" wrote:

Hi JP,
I think I posted back to Joel when it should have gone to you.
In case you are not notified of replies to him, please look at my 9/14 posts
further down in the thread to answer all your previous questions.

Thanks again, this thread is a lifesaver.