Feb 20 2021 12:25 AM
Hi I am quite new to world of 365.
I have exported a number of SharePoints lists to an Excel workbook - all in the same book just on different sheets.
But I can't link (Vlookup) to this Query table data, it doesn't return any values.
I want to use the query table data to populate other excel sheets, that can be updated when the SharePoint Lists are added to.
Feb 20 2021 08:23 AM
I am definitely not one of the SharePoint specialists. But that doesn't mean that I can't provide any information to help. Here is some information - possibilities that I could memorize on the Internet.
Connecting to SharePoint-Based Excel File
There is also this way:
Linking excel to-> Access to-> Sharepoint
Create a sharepoint list. Link this to with Access.
Then you can export the list under "Actions" into a "Spreadsheet" (Excel) in Sharepoint. This works with any library or list. In the case of document libraries, the documents are even displayed as hyperlinks in the Excel lists.
This is now "linked" to Excel as an Excel list (the data area is outlined in blue). To "synchronize" the data you go to the Excel toolbar. A "List" symbol group should now appear there (list / switch result line / synchronize list / discard and update changes).
Synchronize list synchronizes the data with the SharePoint list. If you have write access to the Sharepoint list, you can also update the data via Excel and synchronize it with the Sharepoint list. This data then appears again immediately in your access database.
Import, link, or move data to SharePoint
for Access 2019 SharePoint in Microsoft 365 Access 2016 Access 2013 Access 2010 Access 2007
Here is another VBA approach:
Public Sub Sharepoint_Verknüpfen()
On Error Resume Next
DoCmd.DeleteObject acTable, "TabellenName"
On Error GoTo 0
DoCmd.TransferDatabase acLink, "WSS" _
, "WSS;HDR=NO;IMEX=2;" _
& "DATABASE=https://adresse.ch/sharepointtabelle;" _
& "LIST={DKSDJFJsd10-3432-4DSD-8085-3ADB46C5E138};" _
& "VIEW=;RetrieveIds=Yes;TABLE=Parts", acTable, _
, "TabellenName"
End Sub
If that's not what you're looking for, just ignore it.
At the same time, I would be happy to find out if it helped you.
Wish you a nice day.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Feb 20 2021 09:57 AM
Feb 20 2021 10:10 AM
@trainingthefuture You mention that you want to "link" VLOOKUP to "this query table". Are you, by any chance, using Power Query to connect to a Sharepoint folder?
Feb 20 2021 10:25 AM
@Riny_van_Eekelen Hi I am exporting the Sharepoint List via the export button on SharePoint, then opening them and adding them to the excel sheet.
I would use a Vlookup like: =VLOOKUP(M2, 'Staff Matrix'!$A:$B, 2), but this doesn't return any value when the file Staff Matrix has been imported from SharePoint.
Feb 20 2021 10:28 AM
@NikolinoDE Thank you for your response, is Access included within within 365?.
I think the issue is that a Vlookup doesn't connect to the imported SharePoint List data.
Is the imported SharePoint List file a data file or a query file?
I will try your suggestions and report back.
Feb 20 2021 10:55 AM
is Access included within within 365?.
Access included as part of Microsoft 365 and Office 365 subscriptions
As I said when it comes to Sharepoint, I'm a bloody beginner.
Thank you for your time and patience
Nikolino
I know I don't know anything (Socrates)
Feb 20 2021 11:35 AM
SolutionI could only make I guess that you lookup numbers. Numbers imported from SharePoint list in Excel will be presented as texts, thus in lookup column you have texts. If so you may try
=VLOOKUP(M2 & "", 'Staff Matrix'!$A:$B, 2)
Feb 21 2021 12:22 AM
@Sergei Baklan Thank you so much for this response, this has resolved the issues I was experiencing.
Feb 21 2021 09:18 AM
@trainingthefuture , glad it helped
Feb 20 2021 11:35 AM
SolutionI could only make I guess that you lookup numbers. Numbers imported from SharePoint list in Excel will be presented as texts, thus in lookup column you have texts. If so you may try
=VLOOKUP(M2 & "", 'Staff Matrix'!$A:$B, 2)