I modified my code and removed the portion of saving (Save As) to another excel file. Now the problem is gone, the application quits properly now.
I simplified my code and opted to just copy the records from one source file to a destination file. The excel macro file is just the middle man. Now, got another problem though, I can't save the destination file after copying the records.
Here's my code:
Quote:
Dim xlsAppSource As Excel.Application
Dim xlsAppDest As Excel.Application
Dim fs
Dim rowDestSheet1 As Integer
Dim rowDestSheet2 As Integer
Dim cntFiles As Integer
Sub Consolidate()
cntFiles = 1
rowDestSheet1 = 5
rowDestSheet2 = 5
Set xlsAppDest = New Excel.Application
xlsAppDest.Workbooks.Open ActiveWorkbook.Path & "\" & "Report-CONSOLIDATED.xls"
While cntFiles < 8
CopyRecords ("Report" & cntFiles & ".xls")
Wend
'xlsAppDest.Workbooks(1).Saved = True
xlsAppDest.Workbooks("Report-CONSOLIDATED").Saved = True
xlsAppDest.Workbooks.Close
End Sub
Private Sub CopyRecords(strSourceFile As String)
'Copy records from source to destination
Dim rowSource As Integer
Dim colSource As Integer
Set xlsAppSource = New Excel.Application
xlsAppSource.Workbooks.Open ActiveWorkbook.Path & "\" & strSourceFile
'--Sheet1
rowSource = 2
col = 1
While xlsAppSource.Sheets("Sheet2").Cells(rowSource, 1).Value <> ""
For col = 1 To 8
xlsAppDest.Sheets("Sheet1").Cells(rowDestSheet1, col).Value = xlsAppSource.Sheets("Sheet2").Cells(rowSource, col + 1).Value
Next
rowSource = rowSource + 1
rowDestSheet1 = rowDestSheet1 + 1
Wend
'--Sheet2
rowSource = 2
col = 1
While xlsAppSource.Sheets("Sheet3").Cells(rowSource, 1).Value <> ""
For col = 1 To 8
xlsAppDest.Sheets("Sheet2").Cells(rowDestSheet2, col).Value = xlsAppSource.Sheets("Sheet3").Cells(rowSource, col + 1).Value
Next
rowSource = rowSource + 1
rowDestSheet2 = rowDestSheet2 + 1
Wend
xlsAppSource.Workbooks.Close
End Sub
|
dBforums
>
Excel VBA macro - how to close the excel application
04-08-09, 05:36
Linear Mode
