How to link a excel sheet to a table

Sonya810

Registered User.
Local time
Today, 11:34
Joined
Mar 8, 2018
Messages
22
I want to link an excel sheet to a table, so when the table gets updated the excel sheet is automatically updated.
 

Attachments

  • Capture.PNG
    Capture.PNG
    14 KB · Views: 134
Sorry to say, that's not going to be possible as stated, for a couple of different reasons.

First, you can't actually "link" tables and Excel worksheets in any case.

What you CAN do, though, is link from Access to the Excel worksheet (set up in tabular form, of course) to DISPLAY the data in Access as a linked table.

Second, even if you do create such a link, it is read-only in Access.

So, an option to make it possible is to create a query, using a technique Colin (Isladogs) has developed. Search here for information on that method.

Another option would be to write VBA to update the Excel Worksheet using ADO. If that seems too much to tackle, the query method might be enough.
 
Sorry to say, that's not going to be possible as stated, for a couple of different reasons.

First, you can't actually "link" tables and Excel worksheets in any case.

What you CAN do, though, is link from Access to the Excel worksheet (set up in tabular form, of course) to DISPLAY the data in Access as a linked table.

Second, even if you do create such a link, it is read-only in Access.

So, an option to make it possible is to create a query, using a technique Colin (Isladogs) has developed. Search here for information on that method.

Another option would be to write VBA to update the Excel Worksheet using ADO. If that seems too much to tackle, the query method might be enough.
Ok. Thank you. I think I found a solution.
 
You didn't say what solution you found. It would be helpful for those who find this post in the future to know what solved your problem.

I would suggest a macro in the Excel workbook that refreshed the link from the spreadsheet to the table when the spreadsheet is opened if this doesn't happen automatically. This presumes of course that the users close the spreadsheet and reopen it if they need to lookup something else later. I don't use Excel this way but there may be an option for a live link so that Excel "sees" updates to the table as they happen.
 
I just decided to create a user form in excel. This data feeds to a power BI report.
 
Not sure how power BI got added to the problem.

I'm assuming that if you create a user form, the Access table/query is updated live. Is that correct?
 
The user form can't update the Access table can it?
 
No, not that I can see. My manager wanted an access database to update the power BI. I currentlyhave an excel table. I just created the user form he wanted to feed my excel table. Power BI wouldn't even allow me to import access, I got some errors.
 
Hi. Thanks for posting the screenshot. First thing to check is make sure you're using the same bitness of Power BI as your version of Access. Are you?
 
It would help us if you described the actual goal. Is the goal now to connect Access to Power BI? That seems to be what the question has morphed into.
 

Users who are viewing this thread

Back
Top Bottom