Search code examples
excelvbasharepointsharing

Excel sharepoint cells not syncing when updates are generated from vba code

I am a VBA developer, and have created an Excel UserForm in which people at my company can input certain information - and then from that information, data is generated and written to the next blank row in a table. When I test it as a single user, it works perfectly.

However, this week I have started testing it in a multi-user environment. The document is hosted on SharePoint. Normally, cell changes on Excel SharePoint are visible nearly instantly to other users who have the document open. However, this is the first week I am trialling this new system with colleagues - and despite them doing everything correctly, I am unable to see the updates at my end.

I surmise that the problem stems from the fact that the cell updates are written by my VBA code (Cells.Value = Variable) - rather then actually typed by my colleagues.

Can anyone suggest some reasoning behind this?

In the past, I would have used Excel as a front-end and an Access database as a back-end: however, that requires hosting the file on a network drive, and my company is not keen on continuing to use that feature - instructing that everything should be on SharePoint.

Solution

  • Issues with Excel and SharePoint syncing can be tricky, especially when updates are generated via VBA code. Here are a few things to check and some steps you can take to troubleshoot:

    Check Permissions: Ensure that the account running the VBA code has appropriate permissions to update SharePoint. Sometimes, permission issues can prevent updates from syncing properly.

    Committing Changes: Make sure that your VBA code explicitly commits changes to SharePoint. This typically involves ensuring that after your VBA code updates the Excel worksheet, it also triggers a save or a commit to SharePoint. You may need to use methods like Save, CheckIn, or Publish depending on your SharePoint setup.

    Delay and Timing: Sometimes, syncing issues arise if changes are being made too quickly or if there's a network delay. You can introduce small delays in your VBA code using Application.Wait to ensure that SharePoint has enough time to process updates.