×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Killing Excel after Creating a Workbook in VB

Killing Excel after Creating a Workbook in VB

Killing Excel after Creating a Workbook in VB

(OP)
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").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.

RE: Killing Excel after Creating a Workbook in VB

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.

RE: Killing Excel after Creating a Workbook in VB

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").Select
   With Selection
        .HorizontalAlignment = xlLeft
        .MergeCells = True
        .Font.Size = 20
        .Font.Bold = True
   End With


to


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


And it worked!  Any insight into this?

- Thanks For the Help.

RE: Killing Excel after Creating a Workbook in VB

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources