Results 1 to 12 of 12
  1. #1
    dmgg is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    42

    Excel Import Export

    I have posted this before and learned a lot of things. However, my basic problem still exists. I export some data to excel, excel does some calcs, then sends back results. To get back results, excel must be opened and closed after receiving data from Access. My code works fine in Access 2003 and 2007. But, in Access 2016, the excel file doesn't open and close, so results are not imported. (Excel does receive the exported data).



    In order to not complicate things, I am attaching a very simplified version of my file. (My regular file exports 51 values and imports 16 results.) In newer fast computers, leaving the excel file open during the export/import provides good quick response. Other computers take 20 plus seconds to respond. Without going into detail, linking isn't a choice.

    I hope someone can modify the attached simplified file to get the excel file to open-close after receiving the data in Access 2016. Works fine in 2007. Thanks much in advance.
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm not going to modify it for you but suggest you need to put doevents before you import to make sure it is closed properly. I would also look at your transfer spreadsheet types - suspect you need acSpreadsheetTypeExcel12Xml, not 9 for 2016

    Also you are specifying a range in your transferspreadsheet export which according to the documentation the export will fail, but you say it exports OK so perhaps the documentation is wrong

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I believe the reference to prior posting is this https://www.access-programmers.co.uk...d.php?t=303563

  4. #4
    dmgg is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    42
    Thanks for quick reply. I have changed the transfer spreadsheet type and also chugged through the reference library to make sure have the latest items. I do transfer to a range. Unfortunately, I won't be able to check out Access 2016 until Monday. One question: I looked up DoEvents and can't see how it applies to this problem.


    Quote Originally Posted by Ajax View Post
    I'm not going to modify it for you but suggest you need to put doevents before you import to make sure it is closed properly. I would also look at your transfer spreadsheet types - suspect you need acSpreadsheetTypeExcel12Xml, not 9 for 2016

    Also you are specifying a range in your transferspreadsheet export which according to the documentation the export will fail, but you say it exports OK so perhaps the documentation is wrong

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why export to Excel? The calcs are simple enough for Access. Are these the actual calcs you must do?

    Code runs fine with Access 2010.

    I think Ajax is suggesting DoEvents after xl.Quit to make sure Excel is fully closed before TransferSpreadsheet command runs. All you can do is try and see what happens.

    Did you already try setting a breakpoint on TransferSpreadsheet line? Does Excel Quit?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    dmgg is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    42
    The file that is actually being used is much more complicated than the one I attached. There are several reasons we export/import from excel.

    I have looked up DoEvents and not sure how applies here. Will study more. I don't know what "breakpoint" means either and will lookup.

    Thanks much for taking time to help. this thing that works so well in A2007 but not 2016 is very frustrating.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A breakpoint is a debugging tool. https://www.techonthenet.com/access/...10/debug01.php
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    dmgg is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    42
    I did change to acSpreadsheetTypeExcel12Xml, and made sure had latest references. You are right about the export-it doesn't work with a specified range in A2016. I removed the range from the export command and Access exports the data to a newly created excel sheet with same name as the export table. I am still unable to import because the excel file must be opened and closed for the calculation results to be imported or linked. I have been unable to find how to do this in Access2016. Hard to understand why it works so well in A2007 and A2003, but not in 2016. I wold much appreciate it if you could tell me how to open close excel with A2016-this problem has consumed me for a long time now. If there is no way to open close, is there some way to refresh while still closed?.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Have you tested on different machines running Access 2016? So bizarre this works perfect with Access 2010 and not with 2016. Have you tested the simplified version you posted? Still fails same as your full version?

    Seems Excel calcs cannot refresh unless file is open.

    I do wonder why the workbook has code to quit the application. I don't think this should interfere but does seem unnecessary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You are right about the export-it doesn't work with a specified range in A2016
    I'm surprised this works with early versions of Access because it has always been the case. Are you sure the code is identical in earlier versions to the later versions. There is nothing you have said which would have to be treated differently in 2016.

  11. #11
    dmgg is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    42
    I found some open-close excel code that works with Access 2016 in StackOverflow. Why?? It also works with Access 2003 and 2007.

    It is also hard to understand why A2016 can't export to an excel range name while 2003 and 2007 do it without any problem. Sequence should be reverse.

    Dim xlapp As Object '(had to add this. )
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Visible = False
    xlapp.Workbooks.Open exfile, True, False
    xlapp.Quit
    Set xlapp = Nothing

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've always leaned towards automation and can't recall the last time I've actually use transferspreadsheet function. I found this, which you might be interested in trying out. If you do, please let us know if it still applies. Since the behaviour seems to have been undocumented then, perhaps it's still valid today
    https://accessblog.net/2006/07/expor...cel-range.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. excel export-import
    By gg80 in forum Programming
    Replies: 10
    Last Post: 08-28-2018, 01:25 PM
  2. Replies: 2
    Last Post: 03-16-2018, 10:19 AM
  3. Replies: 1
    Last Post: 08-10-2015, 02:10 PM
  4. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  5. Replies: 22
    Last Post: 09-06-2011, 11:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums