Killing Excel after Creating a Workbook in VB

[Leester]

Hi,

I'm writing in VB6. In my program, I create a workbook, add data to it, and format it. When all of that is done, I save the work book as some file name, and ATTEMPT to kill my Excel.application.

Here's the problem: I cannot fully open my newly created workbook if my program (the one that created it) is running. Excel starts up, and shows me the top menu bar, but doesn't 'fill-in' the rest of the screen. If I choose View|Full Screen, I can view my workbook!

DOES ANYONE KNOW WHAT I'M DOING WRONG? I assume I'm not completely killing off my Excel link to the workbook.

To see what I mean, you can

1. Open an new VB project (standard .exe)

2. Set a reference to 'Microsoft Excel 9.0 (or whatever) object Library'

3. Paste the following code into the Form_Load event:

'get a new excel app
Dim xlApp As New excel.Application

'open a workbook
xlApp.Workbooks.Add

'format a little
Range("A1:F1&quot.Select
Selection.Interior.ColorIndex = 37

'save formatted worksheet
xlApp.ActiveWorkbook.SaveAs _
fileName:="C:\myWorkbook.xls", _
FileFormat:=xlWorkbookNormal

xlApp.Workbooks.Close
xlApp.Quit

4. Run the program and try to open the workbooke while the VB program is still running.

I REALLY appreciate any insight.

 

[dsi]

The following works for me. Note that I have added more object control, including clean-up.

Option Explicit

Private Sub Command1_Click()
    Dim xlApp As New Excel.Application
    Dim xlWb As Workbook
    Dim xlSht As Worksheet
    
    'start a new excel app
    Set xlApp = New Excel.Application
    
    'open a workbook
    Set xlWb = xlApp.Workbooks.Add
    Set xlSht = xlWb.Sheets("Sheet1")
    
    'format a little
    xlSht.Range("A1:F1").Interior.ColorIndex = 37
    
    'save formatted worksheet
    xlWb.SaveAs _
        FileName:="C:\myWorkbook.xls", _
        FileFormat:=xlWorkbookNormal
    
    xlWb.Close
    xlApp.Quit

    Set xlSht = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing

    MsgBox "Done"
    Unload Me
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[DaytonEE]

Thanks, dsi. I plugged your code into an empty project. And, as you promised, it worked. So, I applied the same methodology to my app, but it wouldn't work. I worked throught the code systematically and finally discovered that it was the formatting I was doing, namely, using the .Select method. So I changed all of my

xlSht.Range("A1:J1&quot.Select
With Selection
.HorizontalAlignment = xlLeft
.MergeCells = True
.Font.Size = 20
.Font.Bold = True
End With


to


With xlSht.Range("A1:J1&quot
.HorizontalAlignment = xlLeft
.MergeCells = True
.Font.Size = 20
.Font.Bold = True
End With


And it worked! Any insight into this?

- Thanks For the Help.

 

[dsi]

I am not sure why that would happen. I know that others have had similar trouble in the past, but have not linked it as you have. Anyway, your second procedure is better. There is really no need to use the select method, at least in the majority of cases. To sum it up, I guess that I don't have any real insight into this problem...

Glad to help. DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.