Help: VB Excel Problem
 

Dear All,

I developed one module by using VB 6.0. There is one button that is
used to create excel spreadsheet and display the information in excel.
My problem is after the excel is closed then I press the button again,
then it can't open the excel anymore and error.
I saw there is an excel application still running in the list of
process(windows task manager) after the excel is closed.

Does someone can help me how to fix this problem?

Thanks

Robert Lie

VB Excel Problem
 

Hard to say what the problem is without seeing your code. Does your
reporting code open Excel before creating the report?
If you have a left-over Excel process still running then maybe you
have an un-released reference which you need to eliminate before excel
will quit.

Tim

"Robert Lie" wrote in message
oups.com...
Dear All,

I developed one module by using VB 6.0. There is one button that is
used to create excel spreadsheet and display the information in
excel.
My problem is after the excel is closed then I press the button
again,
then it can't open the excel anymore and error.
I saw there is an excel application still running in the list of
process(windows task manager) after the excel is closed.

Does someone can help me how to fix this problem?

Thanks

Robert Lie

VB Excel Problem
 

Here's the code:

With DEReceiving.rsRecevingGradeList
If .RecordCount 0 Then
trow = (.RecordCount / 2)
If (.RecordCount Mod 2) 0 Then trow = trow + 1
ecol = 1
xls.Cells(1, 1).Select
xls.Cells.Activate
xls.Cells(1, 1) = "Grade for Station : " & StationSelect &
" - " & ShipmentNoSelect
xls.Cells(1, 1).Font.Bold = True
xls.Cells(1, 1).Font.Size = 16
Set xlr = Range(xls.Cells(1, 1), xls.Cells(1, 3))
xlr.Merge
xlr.HorizontalAlignment = xlCenter
For rec = 1 To .RecordCount
If rec <= trow Then
ecol = 1
erow = (rec * 4) - 1
Else
ecol = 3
erow = ((rec - trow) * 4) - 1
End If
Temp = IIf(.Fields!grade = "-", Format(.Fields!spareno,
"0000#"), .Fields!grade)
xls.Cells(erow, ecol) = UCCEAN128("=$Gr" & Temp)
xls.Cells(erow, ecol).Font.Name = "MW6 Code128S"
xls.Cells(erow, ecol).Font.Size = 18
xls.Cells(erow + 1, ecol).Name = "Arial"
xls.Cells(erow + 1, ecol).Font.Size = 14
xls.Cells(erow + 1, ecol).HorizontalAlignment =
xlCenter
xls.Cells(erow + 1, ecol).NumberFormat = "@"
xls.Cells(erow + 1, ecol) = Temp
.MoveNext
Next rec
Columns("A:A").EntireColumn.AutoFit
Cells(1, 2).ColumnWidth = 25
Columns("C:C").EntireColumn.AutoFit
ActiveSheet.PageSetup.PrintArea = "$A$1:$C$" & ((trow * 4)
+ 3)
End If
.Close
End With
xla.Visible = True
xla.UserControl = True
' xlb.Close False
' xla.Quit

' ActiveWindow.SelectedSheets.PrintOut
' xlb.Close False
' xla.Quit

VB Excel Problem
 

This is run from VB ?

I see unqualified references to what look like Excel objects:

eg Columns("A:A").EntireColumn.AutoFit

I don't see how that would work in VB.

Where does xls come from, and what are xla and xlb?

Tim.


"Robert Lie" wrote in message
ups.com...
Here's the code:

With DEReceiving.rsRecevingGradeList
If .RecordCount 0 Then
trow = (.RecordCount / 2)
If (.RecordCount Mod 2) 0 Then trow = trow + 1
ecol = 1
xls.Cells(1, 1).Select
xls.Cells.Activate
xls.Cells(1, 1) = "Grade for Station : " & StationSelect
&
" - " & ShipmentNoSelect
xls.Cells(1, 1).Font.Bold = True
xls.Cells(1, 1).Font.Size = 16
Set xlr = Range(xls.Cells(1, 1), xls.Cells(1, 3))
xlr.Merge
xlr.HorizontalAlignment = xlCenter
For rec = 1 To .RecordCount
If rec <= trow Then
ecol = 1
erow = (rec * 4) - 1
Else
ecol = 3
erow = ((rec - trow) * 4) - 1
End If
Temp = IIf(.Fields!grade = "-",
Format(.Fields!spareno,
"0000#"), .Fields!grade)
xls.Cells(erow, ecol) = UCCEAN128("=$Gr" & Temp)
xls.Cells(erow, ecol).Font.Name = "MW6 Code128S"
xls.Cells(erow, ecol).Font.Size = 18
xls.Cells(erow + 1, ecol).Name = "Arial"
xls.Cells(erow + 1, ecol).Font.Size = 14
xls.Cells(erow + 1, ecol).HorizontalAlignment =
xlCenter
xls.Cells(erow + 1, ecol).NumberFormat = "@"
xls.Cells(erow + 1, ecol) = Temp
.MoveNext
Next rec
Columns("A:A").EntireColumn.AutoFit
Cells(1, 2).ColumnWidth = 25
Columns("C:C").EntireColumn.AutoFit
ActiveSheet.PageSetup.PrintArea = "$A$1:$C$" & ((trow *
4)
+ 3)
End If
.Close
End With
xla.Visible = True
xla.UserControl = True
' xlb.Close False
' xla.Quit

' ActiveWindow.SelectedSheets.PrintOut
' xlb.Close False
' xla.Quit

VB Excel Problem
 

I run it from VB Form.

Dim xla As Excel.Application
Dim xlb As Excel.Workbook
Dim xls As Excel.Worksheet
Dim xlr As Excel.Range


Actualy, the main problem is how to remove the excel application from
Processes (Windows task manager) after the excel is closed?

VB Excel Problem
 

"Robert Lie" wrote in message
oups.com...
I run it from VB Form.

Dim xla As Excel.Application
Dim xlb As Excel.Workbook
Dim xls As Excel.Worksheet
Dim xlr As Excel.Range


Actualy, the main problem is how to remove the excel application from
Processes (Windows task manager) after the excel is closed?

Hi Robert,

As Tim has pointed out, your code is filled with unqualified references
to Excel properties and methods. Whenever you run something like this from
VB:

Set xlr = Range(xls.Cells(1, 1), xls.Cells(1, 3))

You haven't told VB what object the Range method applies to so VBA
creates a hidden instance of the default object for it. Then, when you try
to close Excel you can't, because your VB application still has all of these
hidden references to Excel that you have no way to get rid of.

In the case above, all you need to do to correct the problem is this:

Set xlr = xls.Range(xls.Cells(1, 1), xls.Cells(1, 3))

This makes every Excel method in that line of code fully qualified and
therefore doesn't create any additional references to Excel. You need to do
the same thing for all the rest of your code.

Every reference to every Excel object, property and method in your
program must derive in an unbroken sequence from your original Excel
Application object variable. Once you do this you will be able to destroy
all the Excel references your program creates and shut Excel down cleanly.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm