Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Automation - Cant Quit !? (Access97 sp2a / XP OS)

    Hi,
    I have a small section of code (below) that opens Excel and saves a csv file as an xls file ready for import. It runs first time but when the process is used to pick up on a second file it falls over with an automation error.
    Looking in Task Manager the instance of Excel created in the first process is still running.

    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Open(strFileName) ' open csv file
    With objWB
    With Worksheets(1)
    .SaveAs Left(strFileName, Len(strFileName) - 3) & "xls", xlExcel5 ' save as Excel 5 workbook
    End With
    .Close ' close workbook
    End With
    objXL.Quit ' quit excel

    Set objWB = Nothing
    Set objXL = Nothing


    Is there something more appropriate than objXL.Quit to get rid of this ?

    thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Automation - Cant Quit !? (Access97 sp2a / XP OS)

    Hi,
    I'm not 100% sure but I suspect it has something to do with the fact that you're saving one of the worksheets as a new file, not the whole workbook. This I think means that when you close objWB you still effectively have the newly saved workbook open (because it is a separate entity from objWB). I don't know why closing the application doesn't clear that but it doesn't!
    Try this instead:
    Set objWB = objXL.Workbooks.Open(strFileName) ' open csv file
    With objWB
    .SaveAs Left(strFileName, Len(strFileName) - 3) & "xls", xlExcel5 ' save as Excel 5 workbook
    .Close ' close workbook
    End With
    objXL.Quit ' quit excel
    Set objWB = Nothing
    Set objXL = Nothing

    since you can't have multiple sheet csv files anyway, you might as well save the whole thing as the new file.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation - Cant Quit !? (Access97 sp2a / XP OS)

    Genius. Thanks a lot. <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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