Excel Merge Macro

The Issue:

The client used SharePoint to distribute data entry across a number of different locations.  Each location would update the data in an Excel workbook, and the customer needed that data integrated into a centralized Excel workbook to manage their inventory database, from which would be uploaded and synchronized with a Magento-based eCommerce platform.  The solution required that the program be able to check to see if the document was open in Sharepoint (and hence locked for editing) before attempting to update it, as it would cause errors otherwise.

After the program was initially written, Microsoft updated their SharePoint platform, which caused the code which initially worked to fail.

The Solution:

Initially, the complete application was written in VBA, and worked just fine.  However, once Microsoft updated SharePoint, the project needed to be upgraded to ensure it worked.  The solution entailed determining how to connect to SharePoint, log in, prompting the user for their ID and password where needed, and then proceeding to check to see if the appropriate workbook was available or locked for editing for the time being.

The eventual solution solved the problem and provided the customer with a quality product that accomplished their goals.

About Steven Britton

Steve is a freelance programmer, partial billionaire, dad, Recovering Atheist, Conservative, and occasionally prolific blogger.
  • Yue Chu

    Hi Steven,

    I am writing a macro that would:
    check out a document from sharepoint
    open a document on C drive
    save (stack) the open document on the sharepoint document
    close the file
    check in the document.

    my code is as follows:
    Workbooks.CheckOut “https://site.com/J/TestingFile.xlsx”
    Set wbTarget = Workbooks.Open(“c:theTestFile.xlsx”)
    wbTarget.SaveAs FileName:=”https://site.com/J/TestingFile.xlsx”
    wbTarget.CheckIn SaveChanges:=False, Comments:=”updating file”
    wbTarget.Close False

    I keep getting an error when I check get to the checkin line:”we can’t do that for you because the file is no longer checked out or has been deleted.”…..but file is not deleted and I’m checking it in after it was checked out….any ideas?