Access / Excel transfer of Data

Posted on 2011-10-29
Last Modified: 2012-05-12
I have an Access Database that is  getting data from excel.  The excel app uses a addin called LIM to collect hourly data and access is the storage medium. while excel will be the reporting tool.  I successfully have the data being imported into excel.  I have both the access db and excel workbook open at the same time,  from MS access VBA is there a way to have the excel refresh (I need to access the LIM menu in Excel and select refreshall) before access grabs the data and then after all my data is imported I need for excel to do a recalculate (and again this must be done from access VBA)

Thank you in advance
Question by:INSCNOC
    LVL 84

    Accepted Solution

    Are you automating Excel from within Access, or do you simply open Excel and then open Access?

    If you're not automating Excel, then you can try to grab that instance of Excel and automate it through code like this:

    Dim obj As Object
    Set obj = GetObject(, "Excel.Application")

    You can then use the "obj" variable to manipulate that instance of Excel. The trouble is if you have multiple instances of Excel open - GetObject will grab the first one, which may or may not be the one you want. I suppose you could examine some property of that obj variable to see if you have the right one. For example, if the name of the worksheet is "LIM Data", perhaps you could do something like this:

    If obj.ActiveSheet.Name = "LIM Data" Then
      '/ you have the right sheet
    End If

    I believe you could use the Calculate method to force the worksheet to refresh it's data:


    However, I'm not sure if that will refresh your linked data - you'd have to try it and see.

    And to be clear: The link between Access and Excel will always show the most recent data, so you don't need to refresh that link.


    Author Comment

    All my code is in Access, I need the excel sheet open only to have the LIM app refresh to update the data that gets downloaded from the web into excel, and then everything is done in Access and finally I export some data files which my excel sheet has data connectionc to, but they do not update unless you doa recalculation inexcel at the end....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    #Citrix #Citrix Netscaler #HTTP Compression #Load Balance
    #CITRIX #XENDESKTOP #POC #Citrix Studio
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now