CODEWITHSUNDEEP
excelsharepoint-2010import
Thoma Bigueres
Thoma Bigueres

Reputation: 141

how to import data from sharepoint to excel using macro

I wanted to know if there's a way to import data from SharePoint to Excel using Macros. What i want to do is have a library in my sharepoint in which i have an excel document. When i download the document and open it, i would like to automatically import the data from sharepoint and have some graphs.

What i've tried to do is export the data of a list using the SharePoint ribbon. Then i opened the file.dll.iqy in notepad, hoping to find the macros to import datas. What i've found was this : 

WEB
1
http://win-sharepoint:9090/sites/PPMO/_vti_bin/owssvr.dll?XMLDATA=1&List={C51D70F2-4D7B-4F20-AE05-379DA264F685}&View={8399F350-92C7-4942-  B8B0-464A735AFC72}&RowLimit=0&RootFolder=%2fsites%2fPPMO%2fLists%2fStade%20du%20projet

Selection={C51D70F2-4D7B-4F20-AE05-379DA264F685}-{8399F350-92C7-4942-B8B0-464A735AFC72}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://win-sharepoint:9090/sites/PPMO/_vti_bin
SharePointListView={8399F350-92C7-4942-B8B0-464A735AFC72}
SharePointListName={C51D70F2-4D7B-4F20-AE05-379DA264F685}
RootFolder=/sites/PPMO/Lists/Stade du projet

I tried to save it as a macro, but the result i got was not the one expected. Do you have any idea how to do this ?

Thanks a lot

Upvotes: 0

Views: 18505

Answers (1)

sedatiko
sedatiko

Reputation: 619

In Excel 2010, you can go to Data / Get External Data / From Web

Then you can browse to your SharePoint List and select your table using the helpful arrow icons.

Similarly, you can open the iqy file with Excel (enabling security on the prompt) then save the new worksheet. You can see the iqy file details under Data / Connections.

Upvotes: 0

Related Questions