Update Excel data from Access query

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-05-12
Hi all,

I need a quick excel macro that would get data from an access query, and then update the data in excel where there is a match. The excel data has TAG_NO and MP_NO_E among the columns, and the query returns TAG_NO and MP_NO_A. For each TAG_NO that matches, I want to update MP_NO_E to MP_NO_A.
Question by:PopoDi
  • 4
  • 2
LVL 19

Expert Comment

ID: 36918730
This probably doesnt need a macro - use the 'data connections' tab to set up a connection to Access and it will guide you through the extract
LVL 18

Expert Comment

ID: 36918805
You can no longer update a linked Excel file in Access due to some copyright infringement issues from back in version 2000 or 2002.

Author Comment

ID: 36918913
I am not too familiar with programming excel. I can bring in the data from Access either through external data or manual export, but how do I do the updating where the TAG_NO matches?
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 18

Accepted Solution

jmoss111 earned 800 total points
ID: 36919549
If it were me, I would update in Access and then create a new Excel file

Author Comment

ID: 36919614
I thought about that, but I cannot. The data resides in the excel file and there is only the match in the two columns (Tag No and Mp No) with the access database. They want to have sort of a button that once clicked updates the Mp No from Access to Excel.

Assisted Solution

PopoDi earned 0 total points
ID: 36919991
I was able to do it by just linking the excel file and the query in another access file and then just doing the update query. Thanks everyone who responded.

Author Closing Comment

ID: 36941259
I am using my comments as a solution as the expert's comment spurred me in the direction of the ultimate solution.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

862 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