How to link a excel sheet to a table

[Sonya810]

I want to link an excel sheet to a table, so when the table gets updated the excel sheet is automatically updated.

 

[GPGeorge]

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.

 

[Sonya810]

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.

 

[Sonya810]

I just decided to create a user form in excel. This data feeds to a power BI report.

 

[Sonya810]

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?

Yes, that is correct

 

[Sonya810]

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.

 

[theDBguy]

Power BI wouldn't even allow me to import access, I got some errors.

Perhaps that's a problem we could help you fix. What errors were you getting?

 

[Sonya810]

Perhaps that's a problem we could help you fix. What errors were you getting?

 

[theDBguy]

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?

 

[Sonya810]

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?

Yes I am

 

[theDBguy]

Yes I am

Hi. No offense, but since we can't see what you're looking at, would you mind posting a screenshot showing the bitness for Power BI and Access? Thanks!

In case it helps, here's a link you could check out.

Troubleshoot Access and XLS import issues in Power BI - Power BI

Troubleshoot and resolve Access database and Excel XLS spreadsheet import issues in Power BI Desktop and Power Query Editor.

docs.microsoft.com