SOLVED

Working in Excel with an exported SahrePoint List into Excel.

Occasional Contributor

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. 

9 Replies

@trainingthefuture 

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.

hi I am quete new for excel I want to use it for future

@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?

@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. 

@Nikolino 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. 

@trainingthefuture 

 

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)

best response confirmed by trainingthefuture (Occasional Contributor)
Solution

@trainingthefuture 

I 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)

 

@Sergei Baklan Thank you so much for this response, this has resolved the issues I was experiencing.