Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel/Access automation (Excel 2003 SP2)

    I have an Excel spreadsheet which reads data into a worksheet from a SQL Server database when the sheet is opened. It then creates (using VBA) a temporary Access database to store some of the data & to build some queries which are used elsewhere in the spreadsheet.

    My problem is that after the sheet (and Excel) is closed there is still an instance of Excel running in Task Manager - which it should not be.

    I have checked that all the references to the Access database have been closed & then set to Nothing, likewise with SQL Server.

    Does anybody have any suggestions as to how I can get rid of the rogue Excel process ?

    Usual thanks for your expert help

    Nick

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

    Re: Excel/Access automation (Excel 2003 SP2)

    Do you create an Excel.Application, Excel.Workbook or similar object anywhere in the code?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/Access automation (Excel 2003 SP2)

    Hans

    No. The only automation code refers to Access.

    There is a Userform in the workbook and I use some of the Access queries to populate the combo boxes on the form. However, even if I close the workbook before opening the form it still leaves Excel running.

    Nick

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

    Re: Excel/Access automation (Excel 2003 SP2)

    This might be a tough one to crack - if something goes wrong with the automation code, I'd expect Access to remain in memory, not Excel.

    Without seeing the code it's hard to say what might be causing the problem.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/Access automation (Excel 2003 SP2)

    Hans

    I have been doing some testing on the code trying to narrow down exactly what causes the problem. Here is part of the code :

    MakeTempDatabase "MarineOperations.MDB"

    'Set objAccess = CreateObject("Access.Application")
    Set objAccess = New Access.Application

    With objAccess
    .OpenCurrentDatabase LocalTempPath & "MarineOperations.MDB"
    If NameRange("deal_list_nd", Worksheets("Deals").Cells(1, 1)) Then
    .DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblDeals", ThisWorkbook.FullName, _
    True, "deal_list_nd", True
    End If
    ' If NameRange("movements_nd", Worksheets("Movements").Cells(1, 1)) Then
    ' .DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMovements", ThisWorkbook.FullName, _
    ' True, "movements_nd"
    ' End If
    .CloseCurrentDatabase
    .Quit
    End With

    There is no problem with just using .OpenCurrentDatabase and .CloseCurrentDatabase - the Excel task disappears when
    Excel is closed after just using these. However, as soon as the .DoCmd.TransferSpreadsheet code is uncommented the
    problem recurs and Excel will not close.

    Wondered if you had any more thoughts

    Thanks

    Nick

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

    Re: Excel/Access automation (Excel 2003 SP2)

    I'll have a closer look at it this afternoon.

  7. #7
    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/Access automation (Excel 2003 SP2)

    I think this is a bug in the TransferSpreadsheet method. I believe that if you run the TransferSpreadsheet code from Access while the Excel file is open, you will get the same behaviour. You can, I believe, get round this by sharing the Workbook, but I'm not convinced that's a great idea (though I admit I have no experience of Shared Workbooks that don't actually get used by multiple users, so they may be fine). You might try either writing the data out to CSV and importing from there, or simply copying the worksheet in question to a new workbook, saving that and then importing from that workbook.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Excel/Access automation (Excel 2003 SP2)

    I created a test workbook with a small table and your code (slightly modified to work on my PC), and experienced the same problem.As far as I can tell, the cause of the problem is that you import into Access from the same workbook that is running the code. If I move the code to another workbook, and replace ThisWorkbook.FullName with the path and name of the workbook with the table, Excel doesn't remain in memory afterwards.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/Access automation (Excel 2003 SP2)

    Rory

    Thanks for that. Your suggestions make sense & I will be trying them out this afternoon. I have trawled the net & more than a few other people seem to have hit this problem. One might wonder why Microsoft have not identified it as a bug before & issued a fix.

    Nick

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/Access automation (Excel 2003 SP2)

    Hans

    After several more problems I did manage to get it working. I ended up writing out CSV files & using TransferText to import those. That caused another problem which I finally worked out was caused by duplicate column names in Excel. If you import the CSV manually into Access it is happy, but to do it via VBA causes an error. Once I had made all column headings unique, the file was imported, & the Excel task disappeared when Excel closed. Finally...

    Thanks again for your help on this

    Nick

Posting Permissions

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