Closing Completly an Excel Application

[FlipOver]

After posting a topic, i got some cool code to close an Excel application, but somehow, after i tryed to close it, it still doens't close the application (in the task manager it is shown "Excel"), so it seems to me that it only goes away if i close the main application (vb). But this is something i really can't do. The user should be able to export as many data to an Excel application as he needs (without for each time he does that, another Excel application is oppened). Can someone help me in this one, since Monday i'm trying to find it out.

Special thanks to Iouri

FlipOver

PS - Could it be the fact i made the excel appliction.visible = False one of the reasons the application doensn't close completly?

[Cimperiali]

To have excel visible = false is not the matter.
Matter seems you do not release all references.
If you post your code people here will give a look...

Have a nice day

Cesare Imperiali

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[FlipOver]

Dim sSave As SelectedFile
Dim Count As Integer
Dim FileList As String

FileDialog.sDlgTitle = "Export to Excel"
FileDialog.sFilter = "Microsoft Excel (*.xls)" & Chr$(0) & "*.xls"
' Utilização das flags para todas as opções
FileDialog.flags = OFN_HIDEREADONLY

'minimize the main form
FrmGestaoPedidos.WindowState = 1

'to show the commondialog centered
sSave = ShowSave(Me.hWnd)

If Err.Number <> 32755 And sSave.bCanceled = False Then
FileList = sSave.sLastDirectory
For Count = 1 To sSave.nFilesSelected
If Len(Trim(sSave.sFiles(Count))) <> 0 Then
FileList = FileList & sSave.sFiles(Count)
End If
Next Count
Else
FrmGestaoPedidos.WindowState = 0
Exit Sub
End If


Dim NomeFicheiro As String

NomeFicheiro = ""
NomeFicheiro = Mid(FileDialog.sFile, FileDialog.nFileOffset + 1, InStr(1, FileDialog.sFile, Chr$(0), vbTextCompare) - FileDialog.nFileOffset - 1)
nome_file = NomeFicheiro

'///////////////////////////////////////

' ------------------ TEST -------------

Dim aplicacao_excel As Excel.Application
Dim xlWorkbooks As Excel.Workbooks
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim intI As Integer

Set aplicacao_excel = Excel.Application
aplicacao_excel.Visible = True

Set xlWorkbooks = aplicacao_excel.Workbooks
xlWorkbooks.Add

For Each xlWorkbook In xlWorkbooks
intI = 1
Set xlSheet = xlWorkbook.Worksheets(intI)
xlSheet.Name = "Exported data"
Next


Dim contador As Long
Dim arr 'array de títulos

contador = 0

FrmGestaoPedidos.rdoPedidos.Resultset.MoveFirst
Do While Not FrmGestaoPedidos.rdoPedidos.Resultset.EOF

If contador = 0 Then

arr = Array("Ano", "Nº Pedido", "Dt. Pedido", "Dt. Últ. Psit.", "Últ. Psit.", _
"Cód. Área Utiliz.", "Utilizador", "Descrição Ocorrência", "Dt. Prev. Resol.", _
"Dt. Prev. Resol. Fixa", "Unidade Org.", "Cód. Tipo Pedido", _
"Cód. Prioridade Pedido", "Cód. Via Pedido", "Desc. Área Cliente", _
"Cód. Utilizador Cliente", "Tipo Pedido", "Prioridade Pedido", "Via", _
"Cód. Int. Ráp.", "Cód Área PSit.", "Cód. Utiliz. PSit.", "Interveniente", _
"Cód. Psit.")

For i = LBound(arr) To UBound(arr)

'write data in cells
xlSheet.Cells(1, i + 1).Value = arr(i)

'Format to Bold
xlSheet.Cells(1, i + 1).Font.Bold = True

'Center data
xlSheet.Cells(1, i + 1).HorizontalAlignment = xlCenter

Next

End If

extrai_user = ""


contador = contador + 1

For i = 0 To 22

escreve = FrmGestaoPedidos.rdoPedidos.Resultset.rdoColumns(i).Value

xlSheet.Cells(contador + 1, i + 1).Value = escreve
xlSheet.Cells(contador + 1, i + 1).Font.Size = 8
xlSheet.Cells(contador + 1, i + 1).HorizontalAlignment = xlLeft

Next
FrmGestaoPedidos.rdoPedidos.Resultset.MoveNext
Loop

With xlSheet.Range(Cells(contador + 1, i + 1), "A1")


.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic


.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = xlAutomatic


.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic


.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = xlAutomatic


.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = xlAutomatic


.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic

End With

xlSheet.Columns("A:W").EntireColumn.AutoFit

aplicacao_excel.Visible = True

If Right(nome_file, 4) <> ".xls" Then
nome_file = nome_file & ".xls"
End If

aplicacao_excel.ActiveWorkbook.SaveAs sSave.sLastDirectory & nome_file


aplicacao_excel.Quit

Set aplicacao_excel = Nothing
xlWorkbooks.Close
Set xlWorkbooks = Nothing

Set xlWorkbook = Nothing
Set xlSheet = Nothing

'---------- END TEST ----------------

I belive this is it, thers no problem in the top part of the code, i think thers something wrong in the TEST area...
If thers anyone who can help me, i'd really apreciate it !!!

FlipOver

[Cimperiali]

'try first this way:
'this is the correct order for closing
'(you reversed it in some sentences!)
'first the sheet
Set xlSheet = Nothing
'then the workbook
xlWorkbook.Close
Set xlWorkbook = Nothing
'then the workbooks
xlWorkbooks.Close
Set xlWorkbooks = Nothing
'last the application
aplicacao_excel.Quit
Set aplicacao_excel = Nothing

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[FlipOver]

Thanks, this time this is working (i finaly ended my quest!! hehe).

FilpOver