Link from Access to an Excel spreadsheet located on a SharePoint

Question

_{Thursday, April 11, 2019 11:10 AM}

Hello,

I am relatively new to the world of Access and I am currently trying to build a database to manage electronic components in a product lineup. I am working in a corporate environment so I am having some constraints I need to keep in mind while I work on a solution.

I have a group of colleagues working on an Excel Spreadsheet which is located on a SharePoint and I am using Access from Office365. 

I would like to link to the Excel file on the Sharepoint to use information from one or more worksheets. I successfully imported the requeested information from a local copy but the ultimate goal would be to use the excel sheet from the SharePoint as the source of data.

When I try to import the data using the web adress of the document Acess recognises that I am tryink to link to an online document and I get an error that it is only possible for XML files.

Does anyone know a good solution/workaround for this issue?

Thank you for your support!

All replies (4)

_{Friday, April 12, 2019 5:12 AM | 1 vote}

Hi,

Unfortunately,  it's by design that the Excel file stored in SharePoint library cannot be imported to Access on local PC, only xml file can be imported from http internet. 

If you have any comments or feedback on this, you can visit Access UserVoice to share your idea and vote for the post.

For more information, please read  Import or link to data in an Excel workbook.

Hope the information was helpful.

Best Regards,

Herb

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

_{Friday, April 12, 2019 12:36 PM}

Hello Herb, 

thanks for your swift reply!

Can you maybe recommend a workaround? Here are some things I was thinking as possible workarounds:

1. It should be possible to add a sharepoint as a network drive. Would the link from Access work in this case? (This would somehow affect the portability of the access file to another computer)
2. Is there a way to update a table in Access while keeping the links and references to forms and queries intact? I am thinking of downloading the excel file every now and then and updating the table in Access with the latest information
3. Any other SW solution which would automatically maintain an up to date copy of the excel sheet locally so that this can be linked to Access

I appreciate the support!

BR, ryz

_{Tuesday, April 16, 2019 9:03 AM}

Hi ryz,

From my point of view, you can export tables in an Excel spreadsheet to the lists on a SharePoint site, and import SharePoint lists into Access. The method will help import data from Excel workbook to Access manually . 

In addition, I also suggest you ask questions at Access for Developer forum for help. You may get more helpful replies there.

Best Regards,

Herb

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

_{Friday, May 10, 2019 9:04 AM}

Hi,

This thread has been around for a long time. Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding! If you have any questions, or updates, please feel free to let me know.

Best Regards,

Herb

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.