Good day my mentors. I was able to export my Ms Access table to Excel. But whenever I update the table it doesn't update in the Excel table until I go back to the VBA to click on run before it update it in the Excel table. Please what will be added to the code attached below to automatically refresh the VBA code without going to the VBA always to update. Thank you.
Auto Refresh Code
- Thread starter aym
- Start date
You're exporting the table to Excel, not creating any link. The data exports as is and won't ever change automatically.
Excel should be fetching the data in one of the many ways there are. Simplest of which is:
Data tab > From Access (Get External Data group) > choose table > choose where you want the data
After that, Excel creates a link to that table and you will be able to go to the Table Design tab and click on Refresh from the External Table Data group, effectively updating the data to match its existing state in Access.
There are other ways to do this, you could even have Access push the update in an open Workbook, but that's a more advanced procedure that you would have to really need.
Excel should be fetching the data in one of the many ways there are. Simplest of which is:
Data tab > From Access (Get External Data group) > choose table > choose where you want the data
After that, Excel creates a link to that table and you will be able to go to the Table Design tab and click on Refresh from the External Table Data group, effectively updating the data to match its existing state in Access.
There are other ways to do this, you could even have Access push the update in an open Workbook, but that's a more advanced procedure that you would have to really need.
- Local time
- Today, 13:26
- Joined
- Feb 28, 2001
- Messages
- 31,029
You have gotten EXCELLENT advice from Edgar_ and I won't attempt to do better. Instead, I would like to explain WHY it was good advice.
Excel and Access have totally different internal concepts. Excel treats EVERY CELL as an independent entity. Access does higher-level grouping of things, like fields as records in tables. Oh, sure, an Excel cell can have a formula that references other cells. But basically, each cell is normally treated as an isolated datum. When you store data in an Excel worksheet, unless you did something such as Edgar_ suggested, each cell has NO MEMORY of its origins. It's on its own. And a simple export to Excel creates those independent entities.
Access has the ability to create data-centric macro events that might get triggered when someone updates a table. Usually, the trick is that if the person doing the update was running Access, Access events can be fired. I.e. there is an active environment implicit in table updates.
Excel mostly remains passive. You have to do something IN EXCEL in order to trigger its events, particularly if the data uploaded to it was not through one of the data table linkages that Edgar_ mentioned.
Both Excel AND Access have the same limitations. They have no interest and no action related to things to which they have no direct links. If Excel or Access are not active at the time, they have NO events or links to exploit. Which is why Edgar_'s advice included that you had to open Excel to get to controls and options that COULD trigger events to have an effect on the worksheet's contents. The updates you wanted are not automatic.
Excel and Access have totally different internal concepts. Excel treats EVERY CELL as an independent entity. Access does higher-level grouping of things, like fields as records in tables. Oh, sure, an Excel cell can have a formula that references other cells. But basically, each cell is normally treated as an isolated datum. When you store data in an Excel worksheet, unless you did something such as Edgar_ suggested, each cell has NO MEMORY of its origins. It's on its own. And a simple export to Excel creates those independent entities.
Access has the ability to create data-centric macro events that might get triggered when someone updates a table. Usually, the trick is that if the person doing the update was running Access, Access events can be fired. I.e. there is an active environment implicit in table updates.
Excel mostly remains passive. You have to do something IN EXCEL in order to trigger its events, particularly if the data uploaded to it was not through one of the data table linkages that Edgar_ mentioned.
Both Excel AND Access have the same limitations. They have no interest and no action related to things to which they have no direct links. If Excel or Access are not active at the time, they have NO events or links to exploit. Which is why Edgar_'s advice included that you had to open Excel to get to controls and options that COULD trigger events to have an effect on the worksheet's contents. The updates you wanted are not automatic.
Good day, sir. Thank you for your time and solution. I really appreciate. Thanks
Similar threads
