none
Write to an excel file on SharePoint 2010 document library using Excel 2016 VBA RRS feed

  • Question

  • Hi experts,

    I have a piece of VBA code to update another Excel file (file A). The VBA code runs on my local desktop, the file A is in a document library on a SharePoint 2010 server. The code runs fine in Excel 2010 on Win 7. However, when I run the code in Excel 2016 on win 10, the file A never got updated. 

    This is the code to write the file:

    dim wbArc as Workbook
    dim wsArc as Worksheet

    dim fileAddr as String
    fileAddr = "http://sp.server.com/doc lib 1/Archive.xlsx"
    Set wbArc = Workbooks.Open(fileAddr)
    Set wsArc = wbArc.Sheets("Sheet1")

    'write some values to wsArc cells

    wbArc.Save
    wbArc.Close True

    in the fileAddr variable, I also tried using shared folder path like \\root\shared\folder\doc lib 1\Archive.xlsx, but get same issue.

    So, what's reason for this issue, and how to solve it? Any guidance or hints are appreciated!

    Thanks,

    Michael


    Sunday, May 12, 2019 7:16 PM

Answers

  • For whoever has similar issue, by adding the Application.LockServerFile line solved the problem.

    dim wbArc as Workbookdim wsArc as Worksheet
    dim fileAddr as String
    fileAddr = "http://sp.server.com/doc lib 1/Archive.xlsx"
    Set wbArc = Workbooks.Open(fileAddr)
    Application.LockServerFile
    Set wsArc = wbArc.Sheets("Sheet1")
    'write some values to wsArc cells
    wbArc.Save
    wbArc.Close True
    

    • Marked as answer by Michael_bdd Thursday, May 16, 2019 1:11 AM
    Wednesday, May 15, 2019 6:43 PM