Access / Excel transfer of Data

Posted on 2011-10-29
Medium Priority
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 37050199
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

ID: 37050420
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....

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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