How to link a excel sheet to a table (1 Viewer)
- Thread starter Sonya810
- Start date
GPGeorge
George Hepworth
- Local time
- Today, 14:51
- Joined
- Nov 25, 2004
- Messages
- 2,074
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.
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.
- Local time
- Today, 17:51
- Joined
- Feb 19, 2002
- Messages
- 43,686
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 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.
- Local time
- Today, 17:51
- Joined
- Feb 19, 2002
- Messages
- 43,686
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?
I'm assuming that if you create a user form, the Access table/query is updated live. Is that correct?
Yes, that is correct
- Local time
- Today, 17:51
- Joined
- Feb 19, 2002
- Messages
- 43,686
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.
- Local time
- Today, 14:51
- Joined
- Oct 29, 2018
- Messages
- 21,602
Perhaps that's a problem we could help you fix. What errors were you getting?
Attachments
-
Capture.PNG
Yes I am
- Local time
- Today, 14:51
- Joined
- Oct 29, 2018
- Messages
- 21,602
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 Desktop - Power BI
Troubleshoot and resolve Access database and Excel XLS spreadsheet import issues in Power BI Desktop and Power Query Editor.
docs.microsoft.com
- Local time
- Today, 17:51
- Joined
- Feb 19, 2002
- Messages
- 43,686
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.
