none
Import data and export results back to Access 2007 RRS feed

  • Question

  • Using Office 2007.  I asked this in Access forum, but got no reply.  Hope to get it here.   I export data to an Excel file with Access vba.  Excel calculates results based on received data, then next line in Access vba code imports results from Excel back to Access. 

    It works the first time I use it.  If I change the data in Access, it exports the new data to  the Excel fine.  However, when Import code runs, it returns the old results even though the new results are in the Excel file ( I manually opened the excel file and checked-new data is there)   Any help to get around this much appreciated.

    I am using simple canned code in Access: TransferApreadheet etc.

    Sunday, August 9, 2015 12:31 AM

Answers

  • It sounds like the slowness is due to your network connection.  Could that be right?  You are working on your local machine, but the DB is linking to a bunch of tables on the server, and all the data needs to be pulled down to your PC (refreshed) before you can do anything with it.  Honestly, if it's not too slow, it's probably just to run your process, go get some coffee, and when you come back, hopefully, it's almost done.

    An alternative, would be to copy everything from the server to your local machine, run all your processes, and then load the final results back to the server.  Is that an option for you?  I did something similar a few years ago for a client.  When we ran a huge Access Db on their network drive, it just crawled along.  When we downloaded it to a local machine (desktop), ran the process, and uploaded it back to the server, it wan about 100x faster.  When you are close to the metal, everything will run much, much, much faster then it will work over any kind of network.

    HTH.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 10, 2015 7:00 PM
  • Try code like this:

    Sub ExportAndImport()
        Dim objXL As Object
        Dim objWb As Object
        Dim Loc As String
        Loc = CurrentProject.Path & "\" & "Adders95.xls"
     
        ' Export
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
            "QUERY1", Loc, -1, "zJib"
    
        ' Clear target table
        DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM [T_Excel_Import_Local]"
        DoCmd.SetWarnings False
     
        ' Open and close workbook
        Set objXL = CreateObject("Excel.Application")
        Set objWb = objXL.Workbooks.Open(Filename:=Loc)
        objWb.Close SaveChanges:=True
        objXL.Quit
        ' Give other events a chance
        DoEvents
     
        ' Import
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
            "T_Excel_Import_Local", Loc, True, "JibAdder!h1:o50"
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 9, 2015 6:56 PM

All replies

  • It might help if you posted your code.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 9, 2015 10:21 AM
  • Thanks for quick reply.   I was trying to not take up too much space.  Here is code.

    Dim Loc As String
    Loc = CurrentProject.Path & "\" & "Adders95.xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QUERY1", Loc, -1, "zJib"

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [T_Excel_Import_Local]" 
    DoCmd.SetWarnings False

    DoCmd.TransferSpreadsheet acImport, 8, "T_Excel_Import_Local", Loc, True, "JibAdder!h1:o50"

    Sunday, August 9, 2015 3:19 PM
  • I notice that you export to a .xls workbook using acSpreadsheetTypeExcel12Xml (i.e. .xlsx); I think you should use acSpreadsheetTypeExcel8 instead, although it may not really make a difference.

    Apart from that, it might be a timing problem. Try inserting one or two lines

        DoEvents

    below the line that exports the data.

    Another option would be to open the workbook to give Excel a chance to update the calculations, then save and close it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 9, 2015 3:35 PM
  • I am not familiar with the DoEvents command, but will try to learn about it later today. To check if was a simple  timing problem, I added separate buttons for import and export so that import didn't immediately follow export.  Same problem. 

     I actually have used code that I found on the web that opens and closes the workbook and exports to specific cells. The problem with it is that it took an annoying long to respond.  That is why I am trying this simpler, much faster method. 

      I found this on the web to open/close the workbook and inserted it.

      Dim ObjXL As Excel.Workbook
      Set ObjXL = GetObject(Loc) -  Loc is file path and name

    ---Next- Export and import commands plus some other vba code related to the form---

          Then:
      ObjXL.Save             (I don't need save and can take out later.)
       ObjXL.close

    This does make the update correct.  But, the wait for results is unbelievably long..

    Thanks again for the response.  It is beginning to look like an annoying long wait for response is something that we may have to live with.

    Sunday, August 9, 2015 6:20 PM
  • Try code like this:

    Sub ExportAndImport()
        Dim objXL As Object
        Dim objWb As Object
        Dim Loc As String
        Loc = CurrentProject.Path & "\" & "Adders95.xls"
     
        ' Export
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
            "QUERY1", Loc, -1, "zJib"
    
        ' Clear target table
        DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM [T_Excel_Import_Local]"
        DoCmd.SetWarnings False
     
        ' Open and close workbook
        Set objXL = CreateObject("Excel.Application")
        Set objWb = objXL.Workbooks.Open(Filename:=Loc)
        objWb.Close SaveChanges:=True
        objXL.Quit
        ' Give other events a chance
        DoEvents
     
        ' Import
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
            "T_Excel_Import_Local", Loc, True, "JibAdder!h1:o50"
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 9, 2015 6:56 PM
  • something that you aid doesn't make sense......  Please see these links for importing/exporting between Excel & Access.

    http://www.erlandsendata.no/english/index.php?t=envbadac

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 10, 2015 3:26 AM
  • I tried the code and it works.  But, again, takes a long time to return results. Not real long, just annoyingly long.

    I have left out some details which I don't think matter, but--.  The Access program is operating in a place with several computers, all with Access 2007, and linked to Access tables in a server.  The paired excel files are in each computer rather than the server because the Share feature of excel gave us problems when two users were working at the same time..

    So, it appears that I can't do an export/import excel from Access without including code to close excel after exporting data to it.  If not, results for only the first export are always returned to Access.  i.e. newly exported data from Access is ignored.  Correct?  

    I will take a look at sites you gave me.

    I can't thank you enough for time you have spent on this.  I am obviously weak in Excel-need to study more.

    Monday, August 10, 2015 6:31 PM
  • When you export data to a closed workbook, formulas that refer to the exported data are NOT updated because it is not the workbook itself that updates formulas, but the Excel application. So when you import the values of the formulas immediately after exporting, you'll retrieve the stored, non-updated values.

    To update the formula results, you need to open the workbook in Excel.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 10, 2015 6:43 PM
  • It sounds like the slowness is due to your network connection.  Could that be right?  You are working on your local machine, but the DB is linking to a bunch of tables on the server, and all the data needs to be pulled down to your PC (refreshed) before you can do anything with it.  Honestly, if it's not too slow, it's probably just to run your process, go get some coffee, and when you come back, hopefully, it's almost done.

    An alternative, would be to copy everything from the server to your local machine, run all your processes, and then load the final results back to the server.  Is that an option for you?  I did something similar a few years ago for a client.  When we ran a huge Access Db on their network drive, it just crawled along.  When we downloaded it to a local machine (desktop), ran the process, and uploaded it back to the server, it wan about 100x faster.  When you are close to the metal, everything will run much, much, much faster then it will work over any kind of network.

    HTH.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 10, 2015 7:00 PM
  • Ryan (ryguy72) is correct - Access usually works much faster with files on a local drive than on a network drive.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 10, 2015 7:26 PM
  • You guys are right-the user's system very slow.  When I simulate things at home, making my other computer act as the server, delay times are barely noticeable.  I will play with the idea of transporting tables, but that may be slow also. Love this site-thanks again.  I will copy this to Ryan also as I don't know if you both get this reply.
    Monday, August 10, 2015 8:19 PM
  • You guys are right-the user's system very slow.  When I simulate things at home, making my other computer act as the server, delay times are barely noticeable.  I will play with the idea of transporting tables, but that may be slow also. Love this site-thanks again.  I will copy this to Ryan also as I don't know if you both get this reply.
    Monday, August 10, 2015 8:20 PM