Excel Process Close

  • Thread starter Thread starter Powerguy
  • Start date Start date
P

Powerguy

Hi all,

I've spent countless hours trying to get this silly EXCEL process to
close once I have used it. If anyone can help it would really REALLy
be appreciated! Below is my code-

Dim t As Integer
Dim testCount As Boolean
testCount = False
For t = 0 To UserListView.Items.Count - 1
If UserListView.Items(t).Checked = True Then
testCount = True
End If
Next

If testCount = True Then

Dim j As Integer
Dim k As Integer
Dim strSelectedStartDate As String
Dim strSelectedEndDate As String
strSelectedStartDate = CStr(Format(DateTimePickerStart.Value,
"dd-MM-yyyy"))
strSelectedEndDate = CStr(Format(DateTimePickerEnd.Value,
"dd-MM-yyyy"))
Dim fiPath As IO.FileInfo
Dim strPath As String

fiPath = New IO.FileInfo
System.Reflection.Assembly.GetExecutingAssembly.Location)
strPath = fiPath.Directory.FullName

Dim PathStr As String
PathStr = "" & strPath & "\ClientTemplate.xls"

For j = 0 To UserListView.Items.Count - 1

If UserListView.Items(j).Checked = True Then
ConnectToDatabase()
DoSummary(UserListView.Items(j).Text,
DateTimePickerStart.Value, _

DateTimePickerEnd.Value)
Dim EXL As Excel.Application = New
Excel.Application
Dim oWorkbooks As Excel.Workbooks = EXL.Workbooks
Dim oWorkbook As Excel.Workbook =
oWorkbooks.Open(PathStr)

Dim SummaryWorkSheet As Excel.Worksheet

Dim DetailWorksheet As Excel.Worksheet

EXL.Visible = False
SummaryWorkSheet = EXL.Workbooks(1).Worksheets(1)
SummaryWorkSheet.Cells(1, 2) =
UserListView.Items(j).Text
SummaryWorkSheet.Cells(2, 2) =
strSelectedStartDate
SummaryWorkSheet.Cells(3, 2) = strSelectedEndDate

If SummaryTable.Rows.Count > 0 Then
Dim i As Integer

For i = 0 To SummaryTable.Rows.Count - 1
Dim EXLAppName As String

SummaryWorkSheet.Activate()
SummaryWorkSheet.Cells(6 + i, 1) =
SummaryTable.Rows(i).Item(0)
SummaryWorkSheet.Cells(6 + i, 2) =
SummaryTable.Rows(i).Item(1)
SummaryWorkSheet.Cells(6 + i, 3) =
SummaryTable.Rows(i).Item(2)

DetailWorksheet =
EXL.Workbooks(1).Worksheets.Add(after:=EXL.Workbooks(1).Worksheets(1 +
i))
DetailWorksheet =
EXL.Workbooks(1).Worksheets(i + 2)
DetailWorksheet.Name =
SummaryTable.Rows(i).Item(0)

DetailWorksheet.Cells(6, 1) =
SummaryTable.Rows(i).Item(0)
DetailWorksheet.Cells(6, 2) =
SummaryTable.Rows(i).Item(1)
DetailWorksheet.Cells(6, 3) =
SummaryTable.Rows(i).Item(2)

DetailWorksheet.Cells(1, 2) =
UserListView.Items(j).Text
DetailWorksheet.Cells(2, 2) =
strSelectedStartDate
DetailWorksheet.Cells(3, 2) =
strSelectedEndDate


DetailWorksheet.Columns("A").ColumnWidth =
16.71
DetailWorksheet.Columns("B").ColumnWidth =
25
DetailWorksheet.Columns("C").ColumnWidth =
50
DetailWorksheet.Columns("D").ColumnWidth =
18

DetailWorksheet.Columns("A:A").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Columns("B:B").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Columns("C:C").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Columns("D:D").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Cells(1, 1).select()

DetailWorksheet.Range("A1:A3").Font.Bold =
True
DetailWorksheet.Range("A5:C5").Font.Bold =
True
DetailWorksheet.Range("A8:D8").Font.Bold =
True
DetailWorksheet.Cells(1, 1) = "UserName:"
DetailWorksheet.Cells(2, 1) = "Start
Date:"
DetailWorksheet.Cells(3, 1) = "End Date:"

DetailWorksheet.Cells(5, 1) = "Application
Name"
DetailWorksheet.Cells(5, 2) = "Total
Hours"
DetailWorksheet.Cells(5, 3) = "Condition"

DetailWorksheet.Cells(8, 1) = "Log
Details"
DetailWorksheet.Cells(8, 2) = "Start Time"
DetailWorksheet.Cells(8, 3) = "End Time"
DetailWorksheet.Cells(8, 4) = "Chargeable
Hours"


EXLAppName = SummaryGrid.Item(i, 0)
DoDetails(UserListView.Items(j).Text,
EXLAppName, DateTimePickerStart.Value, _

DateTimePickerEnd.Value)
For k = 0 To DetailLogTable.Rows.Count - 1
DetailWorksheet.Cells(k + 9, 1) =
DetailLogTable.Rows(k).Item(0)
DetailWorksheet.Cells(k + 9, 2) =
DetailLogTable.Rows(k).Item(1)
DetailWorksheet.Cells(k + 9, 3) =
DetailLogTable.Rows(k).Item(2)
DetailWorksheet.Cells(k + 9, 4) =
DetailLogTable.Rows(k).Item(3)
Next

Next

SummaryWorkSheet.Activate()
End If



oWorkbook.SaveAs("" & strPath & "\" &
UserListView.Items(j).Text & " from " & strSelectedStartDate & " to "
& strSelectedEndDate & ".xls")
oWorkbooks.Close()
SummaryWorkSheet = Nothing
DetailWorksheet = Nothing
oWorkbooks = Nothing
oWorkbook = Nothing

EXL.Quit()
EXL = Nothing

DisconDatabase()
GC.Collect()



End If

Next

I know its alot of code to look at but I'm not quite sure what to
leave out incase that is what is creating the problem!
I'm really running out of ideas..

Thanks
PowaGuy
 
Hi,

Replace EXL = Nothing with Marshal.ReleaseComObject(EXL)
http://msdn.microsoft.com/library/d...servicesmarshalclassreleasecomobjecttopic.asp

Ken
--------------------

Hi all,

I've spent countless hours trying to get this silly EXCEL process to
close once I have used it. If anyone can help it would really REALLy
be appreciated! Below is my code-

Dim t As Integer
Dim testCount As Boolean
testCount = False
For t = 0 To UserListView.Items.Count - 1
If UserListView.Items(t).Checked = True Then
testCount = True
End If
Next

If testCount = True Then

Dim j As Integer
Dim k As Integer
Dim strSelectedStartDate As String
Dim strSelectedEndDate As String
strSelectedStartDate = CStr(Format(DateTimePickerStart.Value,
"dd-MM-yyyy"))
strSelectedEndDate = CStr(Format(DateTimePickerEnd.Value,
"dd-MM-yyyy"))
Dim fiPath As IO.FileInfo
Dim strPath As String

fiPath = New IO.FileInfo
System.Reflection.Assembly.GetExecutingAssembly.Location)
strPath = fiPath.Directory.FullName

Dim PathStr As String
PathStr = "" & strPath & "\ClientTemplate.xls"

For j = 0 To UserListView.Items.Count - 1

If UserListView.Items(j).Checked = True Then
ConnectToDatabase()
DoSummary(UserListView.Items(j).Text,
DateTimePickerStart.Value, _

DateTimePickerEnd.Value)
Dim EXL As Excel.Application = New
Excel.Application
Dim oWorkbooks As Excel.Workbooks = EXL.Workbooks
Dim oWorkbook As Excel.Workbook =
oWorkbooks.Open(PathStr)

Dim SummaryWorkSheet As Excel.Worksheet

Dim DetailWorksheet As Excel.Worksheet

EXL.Visible = False
SummaryWorkSheet = EXL.Workbooks(1).Worksheets(1)
SummaryWorkSheet.Cells(1, 2) =
UserListView.Items(j).Text
SummaryWorkSheet.Cells(2, 2) =
strSelectedStartDate
SummaryWorkSheet.Cells(3, 2) = strSelectedEndDate

If SummaryTable.Rows.Count > 0 Then
Dim i As Integer

For i = 0 To SummaryTable.Rows.Count - 1
Dim EXLAppName As String

SummaryWorkSheet.Activate()
SummaryWorkSheet.Cells(6 + i, 1) =
SummaryTable.Rows(i).Item(0)
SummaryWorkSheet.Cells(6 + i, 2) =
SummaryTable.Rows(i).Item(1)
SummaryWorkSheet.Cells(6 + i, 3) =
SummaryTable.Rows(i).Item(2)

DetailWorksheet =
EXL.Workbooks(1).Worksheets.Add(after:=EXL.Workbooks(1).Worksheets(1 +
i))
DetailWorksheet =
EXL.Workbooks(1).Worksheets(i + 2)
DetailWorksheet.Name =
SummaryTable.Rows(i).Item(0)

DetailWorksheet.Cells(6, 1) =
SummaryTable.Rows(i).Item(0)
DetailWorksheet.Cells(6, 2) =
SummaryTable.Rows(i).Item(1)
DetailWorksheet.Cells(6, 3) =
SummaryTable.Rows(i).Item(2)

DetailWorksheet.Cells(1, 2) =
UserListView.Items(j).Text
DetailWorksheet.Cells(2, 2) =
strSelectedStartDate
DetailWorksheet.Cells(3, 2) =
strSelectedEndDate


DetailWorksheet.Columns("A").ColumnWidth =
16.71
DetailWorksheet.Columns("B").ColumnWidth =
25
DetailWorksheet.Columns("C").ColumnWidth =
50
DetailWorksheet.Columns("D").ColumnWidth =
18

DetailWorksheet.Columns("A:A").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Columns("B:B").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Columns("C:C").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Columns("D:D").select()
EXL.Selection.HorizontalAlignment =
Excel.XlHAlign.xlHAlignLeft
DetailWorksheet.Cells(1, 1).select()

DetailWorksheet.Range("A1:A3").Font.Bold =
True
DetailWorksheet.Range("A5:C5").Font.Bold =
True
DetailWorksheet.Range("A8:D8").Font.Bold =
True
DetailWorksheet.Cells(1, 1) = "UserName:"
DetailWorksheet.Cells(2, 1) = "Start
Date:"
DetailWorksheet.Cells(3, 1) = "End Date:"

DetailWorksheet.Cells(5, 1) = "Application
Name"
DetailWorksheet.Cells(5, 2) = "Total
Hours"
DetailWorksheet.Cells(5, 3) = "Condition"

DetailWorksheet.Cells(8, 1) = "Log
Details"
DetailWorksheet.Cells(8, 2) = "Start Time"
DetailWorksheet.Cells(8, 3) = "End Time"
DetailWorksheet.Cells(8, 4) = "Chargeable
Hours"


EXLAppName = SummaryGrid.Item(i, 0)
DoDetails(UserListView.Items(j).Text,
EXLAppName, DateTimePickerStart.Value, _

DateTimePickerEnd.Value)
For k = 0 To DetailLogTable.Rows.Count - 1
DetailWorksheet.Cells(k + 9, 1) =
DetailLogTable.Rows(k).Item(0)
DetailWorksheet.Cells(k + 9, 2) =
DetailLogTable.Rows(k).Item(1)
DetailWorksheet.Cells(k + 9, 3) =
DetailLogTable.Rows(k).Item(2)
DetailWorksheet.Cells(k + 9, 4) =
DetailLogTable.Rows(k).Item(3)
Next

Next

SummaryWorkSheet.Activate()
End If



oWorkbook.SaveAs("" & strPath & "\" &
UserListView.Items(j).Text & " from " & strSelectedStartDate & " to "
& strSelectedEndDate & ".xls")
oWorkbooks.Close()
SummaryWorkSheet = Nothing
DetailWorksheet = Nothing
oWorkbooks = Nothing
oWorkbook = Nothing

EXL.Quit()
EXL = Nothing

DisconDatabase()
GC.Collect()



End If

Next

I know its alot of code to look at but I'm not quite sure what to
leave out incase that is what is creating the problem!
I'm really running out of ideas..

Thanks
PowaGuy
 
Hi guys,

Yah believe me I've tried that marshal thing. That EXCEL.exe process
just refuses to die. Is there a way that I can find out the PID of
the process of the EXCEL.exe process that has been opened by the
automation (left open!!)somehow through code.

I'm hoping if I can get that instance of the process I can use some
sort of API function to kill it on exit of the subroutine.

Any ideas how this can be done?
 
Hi All,

Thanks for all your help. I have managed to get the process id's of
all the EXCEL.exe processes at the instance that I want to terminate
my EXCEL.exe process.

The problem I have now is - is there any way to determine which
instance of EXCEL.exe is the one that Visual Basic has created. I
need some way to get the PID of the EXCEL.exe process as Visual Basic
creates it??

Something like

Dim EXL as new Excel.Application
'' SOME CODE NOW TO GET THE PROCESS ID OF PROCESS CREATED??!

I need to get this information because I get an answer of all the
instances of excel running. I need some way to put into my code to
terminate just the instance that it created. I don't want to terminate
another users session of excel!! (The program is to run on a server) I
know i can get the owner of the process but what if I myself have afew
instances of excel running?? I'm sure there must be away.

Any help would be greatly appreciated.

Come on Gurus!

PowaGuy
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA group the number in the Column and Copy to the new worksheet. 0
PowerPoint Shuffle (swap) objects on PPT slides with the same name only 0
Excel Excel: Sort a column with #.#.# number and keep other column 0
Excel VBA 1
2 different numbers..... 13
vb.net early binding coding format 4
Outlook help regarding outlook vba 0
dynamic main menu 2

Back
Top