Extract data from Sharepoint into Excel using VB Macro

[a99bjomi]

Hi, I am currently investigating if it is possible to have an Excel sheet synchronizing it's data using a macro to connect to Sharepoint.
Basically I have the same Excel Spreadsheet format in both my Excel file and stored on my Sharepoint space. Reading around the topic to my understanding all data uploaded in Sharepoint is Ghosted, ie stored in a Sharepoint database. Is there any way to reach this data from an Excel sheet? If so how do I need to configure my connection string?

Thanks for your help!

 

[bdog2020]

hi There,
It's definitely good to understand Ghosted vs. Unghosted pages in SharePoint, but it's more of a developer thing. excellent writeup here:

http://www.bluedoglimited.com/SharePointThoughts/ViewPost.aspx?ID=4

You can have a spreadsheet that syncs with SharePoint data, no VB or macros required.

Here's how:
1. Create a document library
2. Create a custom view showing the fields you would like to see on the spreadsheet (if using custom property fields for example)
3. Go to the view and notice the Actions choices on the left menu, choose 'Export to Spreadsheet'
4. This will prompt you about creating an Excel Web Query File (.iqy), just click open
5. Note that the External Data toolbar should pop up, if it doesn't, open it. You should also be prompted that you can modify rows, but not delete or add rows of data. We use this to quickly update custom properties on big document libraries that have complex folder structures.
6. Save the file to SharePoint or outside of SharePoint.
7. Once you make a change to the data, be sure to click the Synchronize List button on the External Data toolbar.

If you're curious about the .iqy file, you can go back to the browser interface, click Export to Spreadsheet, and simply save the .iqy file. You'll see reference to the URL and view GUIDs and the URL's.

You can also link SharePoints lists to tables in Access. Just go to Datasheet view in the web browser, then tasks, and you'll see that functionality.

Hope that's helpful.
Ben

 

[a99bjomi]

Hi Ben,

Yes this is exactly what I was looking for. Thank you very much for pointing this out I really appreciate it!!

Regards,
Bjoern