Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Location
    Newport Beach, CA
    Posts
    4

    Unanswered: Help, Instance of excel running in background when using TransferSpreadsheet command

    I'm using access 2003 and win XP pro sp2. The code works seemlessly until I try to use the DoCmd.TransferSpreadsheet command, then when I try and close and quit the excel object an instance is running in task manager and I have to end the process manually. If you comment out the DoCmd.TransferSpreadsheet line there is no problem. Any help would be greatly appreciated. I've been trying to figure this out for a couple of days and am becoming ansy because it is for work.

    The DoCmd.TransferSpreadsheet needs to be called or used while the object is still open, hopefully this is not the reason why I am having the problem.

    Here's the minimized version of the whole code focusing on the offending part:



    Sub testexcelrun()
    Dim dealn As String

    Dim XL As Object
    Set XL = CreateObject("Excel.Application")

    With XL.Application
    .Visible = False
    .Workbooks.Open "t:\import2.xls"
    End With

    Call transfer

    XL.Application.DisplayAlerts = False
    XL.Application.ActiveWorkbook.Close
    XL.Application.DisplayAlerts = True
    XL.Application.Quit

    Set XL = Nothing

    End Sub


    Sub transfer()

    DoCmd.TransferSpreadsheet acImport, , "transfer", "t:\import2.xls", True

    End Sub


    THANK YOU,

    Brad

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The TransferSpreadsheet method is a standalone command, and does not require an open application. Essentially, this means that you can delete the entire routine except for that one line, in order to import the spreadsheet. In fact, I used your one line of code to do exactly that in my own db and it worked perfectly - as a standalone statement.

    Sam

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    Post all the code.

    Are you manipulating Excel from it?

    If you are then there could be a reason why the instance remains.

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    As far as I know, you have accurately described both the challenge and the solution.

    Opening the same file through both an instance of Excel (through the automation) and through one of the transferthingies, might give such result.

    So - ensure you don't open the file through more than one of the methods at a time.
    Last edited by RoyVidar; 11-26-06 at 09:13. Reason: Typo
    Roy-Vidar

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Roy

    The OP has indicated that there is more code.

    The code they have posted, as far as I can see anyway, will not cause the problem.

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Did you try running it?

    If you can, without getting an extra instance of Excel in memory after you've finished, then perhaps you are lucky - but just keep in mind on the setup of the rest of us, opening the same Excel file through two different methods, often has the disadvantage of creating an extra instance of Excel in memory (and other amusing stuff)
    Roy-Vidar

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    possible test would be
    XL.Application.DisplayAlerts = False
    XL.Application.Visible = True
    XL.Application.ActiveWorkbook.Close
    XL.Application.DisplayAlerts = True
    ...and see if it hangs around on-screen

    izy
    currently using SS 2008R2

Posting Permissions

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