Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TransferSpreadsheet (ACC2000)

    I've read all the topics that appear relevant to Access and exports ... nayda ! So here's my issue :

    Dim FileName as string

    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "tblNachaTransactions", FileName

    The TransferSpreadsheet function exports to an Excel file just fine ... BUT ... I cannot delete the resultant '*.xls' filename once I've finished and closed the MS Access application; the Excel file is not open and neither is Excel so it's logical to want to delete a test file.

    I've tried moving to Excel, opening the file, and saving it or 'closing' it ... no help.

    I've check for 'shared' users from within Excel, none are indicated.

    I, admittedly, left off the last two 'column names' and 'range' parameters ... column names at this point were irrelevant (just use default), so my thought was let the function derive a range as the data write progresses ... all the data is written.

    The documentaion is unclear, so I really need some expert help ... btw ... if you creat an Excel object, write the data, and use the close method of the Excel object, this is not a problem .. delete the file anytime you want.

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

    Re: TransferSpreadsheet (ACC2000)

    TransferSpreadsheet seems to be buggy. There was a recent thread about it in the Excel forum: see <post:=628,902>post 628,902</post:>.

    I cannot reproduce the error report in Office XP (2002), by the way. I can delete a spreadsheet created with DoCmd.TransferSpreadsheet without problems. Did you create the workbook on a local drive or on a network drive?

  3. #3
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (ACC2000)

    Hans. it's a local drive on a machine with XP sp-2 ... so I'm stumped. There must be something still 'clinging' to the created file from MS Access, I suspect the MS Access DLL (whichever one) has not recognized a completion of the worksheet, and it's waiting on a 'return code' ... and the cleanup routine in Access does not 'close' the Excel file when Access is closed. MS knows of the problem, but I get zero interest solving the problem.

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

    Re: TransferSpreadsheet (ACC2000)

    I guess Microsoft isn't prepared to spend time on repairing a bug in a version of Access that is 3 versions behind the current one.

    Have you tried whether it works better if you export to a .csv file? (Excel can open .csv files directly.)

  5. #5
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (ACC2000)

    Hey Hans, I looked at this post ... see post 628,902. Looking at his code, he's evoked an Access object, then used that object's, internal, object methods to 'close' the database .... I'm no expert on that, but he still has an 'open' instance of Access on his hands, so that will screw up any later methods ... That's not exactly the same as my problem. Regards Wayne

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

    Re: TransferSpreadsheet (ACC2000)

    Yes, I'm aware that it's a different problem - it's about importing into Access instead of exporting from Access. I only wanted to point out that there are problems with TransferSpreadsheet.

  7. #7
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (ACC2000)

    Yeah ... I know it's an issue ... but ever since ACC2000, the product has been pretty stable. I don't make CSV files (as a work around) because the clients want Excel; and it should WORK ! Not yelling, you understand, but this is pretty simple stuff [img]/forums/images/smilies/smile.gif[/img] Is there a way to "force" the O/S to delete the file outside of the explorer window ??? Kind Regards Wayne

  8. #8
    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: TransferSpreadsheet (ACC2000)

    Wayne

    I have just come across your post (I posted the Excel thread Hans referred to) & can understand your frustrations over this.

    I can only say that writing the data out to CSV files worked perfectly & without any noticeable delay, although there was not a huge amount of data. I closed the Access object after the files had been imported into it. When it came to closing down Excel it left no processes running in Task Manager - whereas when I used TransferSpreadsheet it did.

    Hope this helps.

    Nick

  9. #9
    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: TransferSpreadsheet (ACC2000)

    Wayne,
    Have you tried automating Excel directly instead?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (ACC2000)

    Yeah, thanks Nick ... I have a client database, they want to generate "what if" spreadsheets, so it's a natural to 'push' a dataset out of MS Access. I'm not lazy, but when all is needed is a one time Excel WB with one tab, I see no sense in creating an Excel Object and all the code to make it. TransferSpreadsheet 'ought' to work [img]/forums/images/smilies/sad.gif[/img] Inasmuch as Excel will recognize a CSV formatted document and open it normally, that's probably the way to go. I'm like you, I think the function call is not releasing .. I may play with the closing and release of the form object, just to see if that works. Regards Wayne

  11. #11
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (ACC2000)

    Hi Rory ... yeah, MS Access can create the Excel object and send the data ... works great ... but I have not had an interest in coding Excel to 'pull' the data ... defeats the purpose of the exercise "the client is using access", he doesn't want to close access and open Excel just to get the data into a workbook ... Nice thought, but it's just now workable. Thanks again .. Wayne

  12. #12
    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: TransferSpreadsheet (ACC2000)

    The client doesn't have to know there's any difference in the way it's done though? Whether you use TransferSpreadsheet or Automation should not be apparent to the client.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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