How to Interact With SharePoint From VBA

By Jim Campbell

The Visual Basic for Applications service lets you interface with SharePoint from Microsoft Office applications. SharePoint includes a database that you can use to retrieve data or import documents from the SharePoint service. Use VBA to open the SharePoint URL and query for the data you want to display in the Office application, such as Access, Word or Excel.

Step 1

Open the Office application you want to use to connect to the SharePoint server. Click the "Developer" tab or Ribbon, then click the "VBA" button to open the VBA editor.

Step 2

Double-click the control or form you want to use to query the SharePoint server. For instance, if you want to view a list of SharePoint users when the user clicks a button, double-click the button to scroll directly to the "OnClick" event.

Step 3

Type your code that connects to the SharePoint server. After you connect, you can use any service and interact. For instance, the following code connects to the SharePoint service and retrieves a list of customers:

DoCmd.TransferDatabase acExport, "Windows SharePoint Services", "http://sharepoint/sites/customerportal", acTable, "Customers", "Customers"

Replace the "http://sharepoint/sites/customerportal" with your SharePoint server and site name.

Step 4

Click "Save" and select "Run" to execute the VBA code and verify that there are no bugs with the connection code.

References

Writer Bio