Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel won't close (2K)

    I'm running this code from Access to format some cells in an exported spreadsheet.
    Dim oXL As New Excel.Application
    Dim oXB As New Excel.Workbook

    oXL.Workbooks.Open (strFILE)
    Set oXB = oXL.Workbooks.Open(strFILE)

    With oXL.Application
    .Columns("J:J").Select
    .Selection.NumberFormat = "@" 'formats as text
    .Range("A2").Select
    .Range(Selection, Selection.End(xlDown)).Select
    .Selection.NumberFormat = "0" 'formats as number
    .Cells.Select
    .Selection.Columns.AutoFit
    .Range("A1").Select

    End With
    oXB.Save
    Set oXB = Nothing
    oXL.Quit
    Set oXL = Nothing
    If I Ctrl-Alt-Del and go to the task list I still see Excel running in the background.
    Am I not closing it properly in the code?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel won't close (2K)

    Declare oXB without the New keyword:

    Dim oXB As Excel.Workbook

    I suspect that using New causes a second instance of Excel to be created.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Other remarks:

    Omit the line

    oXL.Workbooks.Open (strFILE)

    It is superfluous, since you open the file again in the next line.

    You don't need to select ranges in order to format them:

    With oXL.Application
    .Columns("J:J").NumberFormat = "@" 'formats as text
    .Range(.Range("A2"), .Range("A2").End(xlDown)).NumberFormat = "0" 'formats as number
    .Cells.Columns.AutoFit
    End With

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel won't close (2K)

    Hans,
    What can I say? You da Man.
    Many thanks again,
    Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •