Visual Basic, Vb generated Excel object not destroyed

Lank
#1 / 5

Vb generated Excel object not destroyed

Hi All,

I am having a problem with an excel 9.0 object I create using VB6 w/ SP3
on NT4 w/ SP6. I am putting data into an excel spreadsheet object and
then generating a chart from that data. This is all cool. I am
terminating all my objects properly, but an excel.exe process is still
{*filter*} there. Dunno why it won't die. Anyone done this before and
experienced this problem. Please help.

Here some code:

Dim strChartType As String

Dim lngRow As Long
Dim lngCol As Long
Dim lngRowTotal As Long
Dim lngColTotal As Long
Dim intFreeFile As Integer
Dim strRow As String

Dim dblData As Double
Dim dbltest As Double
Dim intNull As Integer

lngRowTotal = objChart.RowCount
lngColTotal = objChart.ColumnCount

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objExcelRange As Excel.Range
Dim objWorksheet As Excel.Worksheet

Set objExcel = New Excel.Application

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets.Add

' Write Column Headers (space first)
objChart.Row = 1
strRow = ""
For lngCol = 1 To lngColTotal
objChart.Column = lngCol

objWorksheet.Cells(1, lngCol + 1).Value =
objChart.ColumnLabel
Next lngCol

' Write Data
For lngRow = 1 To lngRowTotal
objChart.Row = lngRow
strRow = ""
'Y axis data
objWorksheet.Cells(lngRow + 1, 1).Value = objChart.RowLabel
For lngCol = 1 To lngColTotal
objChart.Column = lngCol

GetData lngRow, lngCol, dblData, intNull
If intNull = 0 Then
'X Axis data
objWorksheet.Cells(lngRow + 1, lngCol + 1).Value =
dblData
End If
Next lngCol
Next lngRow

' Auto dynamically gets range of data
Set objExcelRange =
objWorkbook.Worksheets(1).Range("a1").CurrentRegion

Select Case objChart.ChartType
Case 0 ' 3D Bar
strChartType = xl3DColumnClustered
Case 1 ' 2D Bar
strChartType = xlBarClustered
Case 2 ' 3D Line
strChartType = xl3DLine
Case 3 ' 2D Line
strChartType = xlLine
Case 4 ' 3D Area
strChartType = xl3DArea
Case 5 '2D Area
strChartType = xlArea
Case 16 '2D XY
strChartType = xlXYScatterLines

End Select

With objExcel
Charts.Add
ActiveChart.ChartType = strChartType

ActiveChart.SetSourceData Source:=objExcelRange,
PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = objChart.TitleText
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
objChart.Plot.Axis(xlValue, xlPrimary).AxisTitle.Text
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
objChart.Plot.Axis(xlCategory, xlPrimary).AxisTitle.Text

End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End With

objWorkbook.Close False

objExcel.Quit

Set objExcelRange = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

-- Lanky
********************
It's All Good!

Sent via Deja.com http://www.*-*-*.com/
Before you buy.

Fri, 06 Sep 2002 03:00:00 GMT

Nightingal
#2 / 5

Vb generated Excel object not destroyed

Quote:

> Hi All,

> I am having a problem with an excel 9.0 object I create using VB6 w/ SP3
> on NT4 w/ SP6. I am putting data into an excel spreadsheet object and
> then generating a chart from that data. This is all cool. I am
> terminating all my objects properly, but an excel.exe process is still
>{*filter*} there. Dunno why it won't die. Anyone done this before and
> experienced this problem. Please help.

<snip>

Something you're doing is leaving an object open. To find out what,
comment out whole sections of your code, and then uncomment it a
section at a time until you figure out exactly what's doing it.

I had a lot of trouble with this, especially when using code created
using Macro/Record in Excel itself. Excel frequently creates objects
behind the scenes, and then doesn't uninstantiate them. The thing
that really got me was that SELECTION.something. I was doing this:

.range(.cells(a,b),.cells(c,d)).select
with selection
.font = arial
.property = whatever
end selection

Excel creates an object behind the scene here. I changed it to this:

Dim myRange as Range
set myrange= .range(.cells(a,b),.cells(c,d))
with myrange
.font=arial
.property = whatever
end with
set myrange=nothing

And it was fixed. I had a *lot* of these. After fixing all of them,
my problem was resolved.

Good luck.
Robin
nightingale at home don't spamme dammit dot com

Sat, 07 Sep 2002 03:00:00 GMT

muk_ikenn..
#3 / 5

Vb generated Excel object not destroyed

Quote:

> Hi All,

> I am having a problem with an excel 9.0 object I create using VB6 w/
SP3
> on NT4 w/ SP6. I am putting data into an excel spreadsheet object and
> then generating a chart from that data. This is all cool. I am
> terminating all my objects properly, but an excel.exe process is still
>{*filter*} there. Dunno why it won't die. Anyone done this before and
> experienced this problem. Please help.

> Here some code:

> Dim strChartType As String

> Dim lngRow As Long
> Dim lngCol As Long
> Dim lngRowTotal As Long
> Dim lngColTotal As Long
> Dim intFreeFile As Integer
> Dim strRow As String

> Dim dblData As Double
> Dim dbltest As Double
> Dim intNull As Integer

> lngRowTotal = objChart.RowCount
> lngColTotal = objChart.ColumnCount

> Dim objExcel As Excel.Application
> Dim objWorkbook As Excel.Workbook
> Dim objExcelRange As Excel.Range
> Dim objWorksheet As Excel.Worksheet

> Set objExcel = New Excel.Application

> objExcel.Visible = True

> Set objWorkbook = objExcel.Workbooks.Add

> Set objWorksheet = objWorkbook.Worksheets.Add

> ' Write Column Headers (space first)
> objChart.Row = 1
> strRow = ""
> For lngCol = 1 To lngColTotal
> objChart.Column = lngCol

> objWorksheet.Cells(1, lngCol + 1).Value =
> objChart.ColumnLabel
> Next lngCol

> ' Write Data
> For lngRow = 1 To lngRowTotal
> objChart.Row = lngRow
> strRow = ""
> 'Y axis data
> objWorksheet.Cells(lngRow + 1, 1).Value = objChart.RowLabel
> For lngCol = 1 To lngColTotal
> objChart.Column = lngCol

> GetData lngRow, lngCol, dblData, intNull
> If intNull = 0 Then
> 'X Axis data
> objWorksheet.Cells(lngRow + 1, lngCol + 1).Value =
> dblData
> End If
> Next lngCol
> Next lngRow

> ' Auto dynamically gets range of data
> Set objExcelRange =
> objWorkbook.Worksheets(1).Range("a1").CurrentRegion

> Select Case objChart.ChartType
> Case 0 ' 3D Bar
> strChartType = xl3DColumnClustered
> Case 1 ' 2D Bar
> strChartType = xlBarClustered
> Case 2 ' 3D Line
> strChartType = xl3DLine
> Case 3 ' 2D Line
> strChartType = xlLine
> Case 4 ' 3D Area
> strChartType = xl3DArea
> Case 5 '2D Area
> strChartType = xlArea
> Case 16 '2D XY
> strChartType = xlXYScatterLines

> End Select

> With objExcel
> Charts.Add
> ActiveChart.ChartType = strChartType

> ActiveChart.SetSourceData Source:=objExcelRange,
> PlotBy:=xlColumns

> ActiveChart.Location Where:=xlLocationAsNewSheet
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = objChart.TitleText
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> objChart.Plot.Axis(xlValue, xlPrimary).AxisTitle.Text
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
> objChart.Plot.Axis(xlCategory, xlPrimary).AxisTitle.Text

> End With
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlBottom
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> End With

> objWorkbook.Close False

> objExcel.Quit

> Set objExcelRange = Nothing
> Set objWorksheet = Nothing
> Set objWorkbook = Nothing
> Set objExcel = Nothing

> -- Lanky
> ********************
> It's All Good!

> Sent via Deja.com http://www.*-*-*.com/
> Before you buy.

If that is literally the whole code, no more and no less, then you're
going wrong straight away with your

Quote:

> lngRowTotal = objChart.RowCount

because that will become an implicit object ie one not created by you.
You have got to explicitly create and properly prefix everything. It is
very easy to do as soon as you have an excel reference in your project
and it can take ages to track them all down.

Ian Kennedy

Sent via Deja.com http://www.*-*-*.com/
Before you buy.

Sat, 07 Sep 2002 03:00:00 GMT

v_..
#4 / 5

Vb generated Excel object not destroyed

Quote:

> Set objExcel = New Excel.Application

When you open the Excel object, add the below line below the above line
in your code.
Excel.Application.DisplayAlerts = False
This should take care of those annoying 'saveas', 'already exists..'
problems.

The only way you can kill the process is to try and emulate the end
process of the task mgr. I usually save the file with a saveas
junkfilename(in a sure path, like C:\prog files). Then I use either a
Kill or DestroyFile API call. This is a sureshot method.

Hope this helps...

Quote:

> Hi All,

> I am having a problem with an excel 9.0 object I create using VB6 w/
SP3
> on NT4 w/ SP6. I am putting data into an excel spreadsheet object and
> then generating a chart from that data. This is all cool. I am
> terminating all my objects properly, but an excel.exe process is still
>{*filter*} there. Dunno why it won't die. Anyone done this before and
> experienced this problem. Please help.

> Here some code:

> Dim strChartType As String

> Dim lngRow As Long
> Dim lngCol As Long
> Dim lngRowTotal As Long
> Dim lngColTotal As Long
> Dim intFreeFile As Integer
> Dim strRow As String

> Dim dblData As Double
> Dim dbltest As Double
> Dim intNull As Integer

> lngRowTotal = objChart.RowCount
> lngColTotal = objChart.ColumnCount

> Dim objExcel As Excel.Application
> Dim objWorkbook As Excel.Workbook
> Dim objExcelRange As Excel.Range
> Dim objWorksheet As Excel.Worksheet

> Set objExcel = New Excel.Application

> objExcel.Visible = True

> Set objWorkbook = objExcel.Workbooks.Add

> Set objWorksheet = objWorkbook.Worksheets.Add

> ' Write Column Headers (space first)
> objChart.Row = 1
> strRow = ""
> For lngCol = 1 To lngColTotal
> objChart.Column = lngCol

> objWorksheet.Cells(1, lngCol + 1).Value =
> objChart.ColumnLabel
> Next lngCol

> ' Write Data
> For lngRow = 1 To lngRowTotal
> objChart.Row = lngRow
> strRow = ""
> 'Y axis data
> objWorksheet.Cells(lngRow + 1, 1).Value = objChart.RowLabel
> For lngCol = 1 To lngColTotal
> objChart.Column = lngCol

> GetData lngRow, lngCol, dblData, intNull
> If intNull = 0 Then
> 'X Axis data
> objWorksheet.Cells(lngRow + 1, lngCol + 1).Value =
> dblData
> End If
> Next lngCol
> Next lngRow

> ' Auto dynamically gets range of data
> Set objExcelRange =
> objWorkbook.Worksheets(1).Range("a1").CurrentRegion

> Select Case objChart.ChartType
> Case 0 ' 3D Bar
> strChartType = xl3DColumnClustered
> Case 1 ' 2D Bar
> strChartType = xlBarClustered
> Case 2 ' 3D Line
> strChartType = xl3DLine
> Case 3 ' 2D Line
> strChartType = xlLine
> Case 4 ' 3D Area
> strChartType = xl3DArea
> Case 5 '2D Area
> strChartType = xlArea
> Case 16 '2D XY
> strChartType = xlXYScatterLines

> End Select

> With objExcel
> Charts.Add
> ActiveChart.ChartType = strChartType

> ActiveChart.SetSourceData Source:=objExcelRange,
> PlotBy:=xlColumns

> ActiveChart.Location Where:=xlLocationAsNewSheet
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = objChart.TitleText
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> objChart.Plot.Axis(xlValue, xlPrimary).AxisTitle.Text
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
> objChart.Plot.Axis(xlCategory, xlPrimary).AxisTitle.Text

> End With
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlBottom
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> End With

> objWorkbook.Close False

> objExcel.Quit

> Set objExcelRange = Nothing
> Set objWorksheet = Nothing
> Set objWorkbook = Nothing
> Set objExcel = Nothing

> -- Lanky
> ********************
> It's All Good!

> Sent via Deja.com http://www.*-*-*.com/
> Before you buy.

Sent via Deja.com http://www.*-*-*.com/
Before you buy.

Sat, 07 Sep 2002 03:00:00 GMT

Lank
#5 / 5

Vb generated Excel object not destroyed

Quote:

> Hi All,

> I am having a problem with an excel 9.0 object I create using VB6 w/
SP3
> on NT4 w/ SP6. I am putting data into an excel spreadsheet object and
> then generating a chart from that data. This is all cool. I am
> terminating all my objects properly, but an excel.exe process is still
>{*filter*} there. Dunno why it won't die. Anyone done this before and
> experienced this problem. Please help.

> Here some code:

> Dim strChartType As String

> Dim lngRow As Long
> Dim lngCol As Long
> Dim lngRowTotal As Long
> Dim lngColTotal As Long
> Dim intFreeFile As Integer
> Dim strRow As String

> Dim dblData As Double
> Dim dbltest As Double
> Dim intNull As Integer

> lngRowTotal = objChart.RowCount
> lngColTotal = objChart.ColumnCount

> Dim objExcel As Excel.Application
> Dim objWorkbook As Excel.Workbook
> Dim objExcelRange As Excel.Range
> Dim objWorksheet As Excel.Worksheet

> Set objExcel = New Excel.Application

> objExcel.Visible = True

> Set objWorkbook = objExcel.Workbooks.Add

> Set objWorksheet = objWorkbook.Worksheets.Add

> ' Write Column Headers (space first)
> objChart.Row = 1
> strRow = ""
> For lngCol = 1 To lngColTotal
> objChart.Column = lngCol

> objWorksheet.Cells(1, lngCol + 1).Value =
> objChart.ColumnLabel
> Next lngCol

> ' Write Data
> For lngRow = 1 To lngRowTotal
> objChart.Row = lngRow
> strRow = ""
> 'Y axis data
> objWorksheet.Cells(lngRow + 1, 1).Value = objChart.RowLabel
> For lngCol = 1 To lngColTotal
> objChart.Column = lngCol

> GetData lngRow, lngCol, dblData, intNull
> If intNull = 0 Then
> 'X Axis data
> objWorksheet.Cells(lngRow + 1, lngCol + 1).Value =
> dblData
> End If
> Next lngCol
> Next lngRow

> ' Auto dynamically gets range of data
> Set objExcelRange =
> objWorkbook.Worksheets(1).Range("a1").CurrentRegion

> Select Case objChart.ChartType
> Case 0 ' 3D Bar
> strChartType = xl3DColumnClustered
> Case 1 ' 2D Bar
> strChartType = xlBarClustered
> Case 2 ' 3D Line
> strChartType = xl3DLine
> Case 3 ' 2D Line
> strChartType = xlLine
> Case 4 ' 3D Area
> strChartType = xl3DArea
> Case 5 '2D Area
> strChartType = xlArea
> Case 16 '2D XY
> strChartType = xlXYScatterLines

> End Select

> With objExcel
> Charts.Add
> ActiveChart.ChartType = strChartType

> ActiveChart.SetSourceData Source:=objExcelRange,
> PlotBy:=xlColumns

> ActiveChart.Location Where:=xlLocationAsNewSheet
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = objChart.TitleText
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> objChart.Plot.Axis(xlValue, xlPrimary).AxisTitle.Text
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
> objChart.Plot.Axis(xlCategory, xlPrimary).AxisTitle.Text

> End With
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlBottom
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> End With

> objWorkbook.Close False

> objExcel.Quit

> Set objExcelRange = Nothing
> Set objWorksheet = Nothing
> Set objWorkbook = Nothing
> Set objExcel = Nothing

> -- Lanky
> ********************
> It's All Good!

> Sent via Deja.com http://www.*-*-*.com/
> Before you buy.

Found it. It was a chart object that was being created.

set objexcelchart = objexcel.charts.add

-- Lanky
********************
It's All Good!
http://www.*-*-*.com/ ~mdang

Sent via Deja.com http://www.*-*-*.com/
Before you buy.

Sat, 07 Sep 2002 03:00:00 GMT