Solved: Stop that process

[Dave]

I've just started learning VB6 so this may seem simple but I don't understand it. The following code is supposed to open an XL file and make a simple change to C1. What I have found is that the task manager shows that the XL process continues after the subs' completion. This only happens when the line of code making the change is included... without it everything is fine. Any assistance would be welcome. Dave
[VBA]
Sub Fxchart()
Dim objExcel As Object, objWorkBook As Object, Rng As Range
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\chartf(x).xls")
'problem below
[sheet1!C1] = Application.InputBox("Input iteration")
Application.DisplayAlerts = False
objExcel.Quit
Set objExcel = Nothing
Set objWorkBook = Nothing
Application.DisplayAlerts = True
End Sub
[/VBA]

[XL-Dennis]

Hi Dave,

You're code include some errors that propably cause the unexpected behaviour.

Check out the following revised code and let me know if it work better:

[vba]
Option Explicit
Sub Fxchart()
Dim objExcel As Object, objWorkBook As Object, objWorksheet As Object

Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\t.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")

objWorksheet.Cells(1, 3).Value = InputBox("Iteration")

objWorkBook.Close SaveChanges:=False
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

End Sub
[/vba]

Kind regards,
Dennis

[Bob Phillips]

The important thing to remember when you are referencing Excel via automation is that you cannot assume any implicit references as you can when working directly in Excel.

For instance, in Excel, you can use (but shouldn't IMO ) this code

[Sheet1!C1]

as it refers to cell C1 on Sheet1 within the ACTIVEWORKBOOK. In automation, the code doesn't assume the activeworkbook. As Dennis showed, you need to fully reference it, as he does with

[VBA]
Set objWorksheet = objWorkBook.Worksheets("Sheet1")

objWorksheet.Cells(1, 3).Value = InputBox("Iteration")
[/VBA]

or directly with

[VBA]
objWorkBook.Worksheets("Sheet1").Range("A1").Value = InputBox("Iteration")
[/VBA]

You cannot reference Application as you do in

[VBA]
Application.DisplayAlerts = False
[/VBA]

as that refers to the default, Excel, application in Excel, it refers to nothing in automation, so you have to use your application object

[VBA]
objExcel.DisplayAlerts = False
[/VBA]

Dennis also showed you two other important techniques.

First, he closed the workbook with

[VBA]
objWorkBook.Close SaveChanges:=False
[/VBA]

He did this so that no objects would tied up when you quit.

Secondly, he released all object variables in the opposite order to which they were assigned.

[VBA]
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
[/VBA]

This is a defensive measure to ensure that nothing is left in an incomplete state.

When using automation, it is a good idea to use rigorous syntax, don't use shortcut syntax or default properties, as it is likely to give you more debugging problems that the time saved in not spelling out fully.

[Dave]

Thank you both very much. Dennis your code works perfect of course. xld...your additional input is also greatly appreciated. The following code is the outcome for this little project. It passes the the XL wb the iteration then runs the wb sub "chartfunction" to produce a .gif file which is displayed in the VB6 image control on form1. Rather a clumsy route I'm sure. If someone could point me in the right direction for charting using VB6 without XL (if this is possible?), I'd be that much closer to attaining my real objective. Again, thanks. Dave
[VBA]
Option Explicit
Sub Fxchart()
Dim objExcel As Object, objWorkBook As Object, objWorksheet As Object

Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\chartf(x).xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")

objWorksheet.Cells(1, 3).Value = InputBox("Iteration")
objExcel.Run "ChartFunction"
objWorkBook.Close SaveChanges:=False
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Form1.Image1.Picture = LoadPicture("C:\" & "ChartF(x).gif")
End Sub
[/VBA]

[XL-Dennis]

Dave,

Since You're a beginner I believe that the MS Chart component may be a good start for You. Open Your VB-project and select the command Project | Components... On the first tab scroll down to the component name Microsoft Chart Control 6.0 (SP-4) (OLEDB) and check it.

You may also consider to use the Chart component in the Office Web Component package. Check out my articles here at VBAX for an introduction of them. On it's face they may appear to be easy to work with but they put demand on You.

Finally, there exist some great third part ActiveX controls You can buy and use but they are quite expensive. Personally I use the Component One Enterprise package.

Bob - Thanks for Your clarifications as I missed the fact that Dave is a VB-beginner.

Kind regards,
Dennis

[Dave]

Those articles are a great read. Thank you for them and your assistance. I'll mark this thread as solved as I've gone a little off topic. I'll repost when I get lost again. Have a nice day. Dave