Forum Discussion

Diceman's avatar
Diceman
Occasional Contributor
8 years ago

Make an Excel file hosted on SharePoint editable

Hello - I have an excel file hosted on a SharePoint server that I am using to store some data stored while my test was running.   When I open the file, it is being opened in Read-only mode, which prevents me from saving my chages to the document.   

 

 

 

Here is the function that I'm using to write the data to Excel:

function writeToExcel(fileName, sheetName, data, col, row){ //assume data is a value (int, string), and not an object
Log.Message("STARTFUNCTION STARTFUNCTION WriteToExcel(" + fileName + ", " + sheetName + ", " + data + ", " + col + ", " + row + ") STARTFUNCTION STARTFUNCTION");
//Open Book, Sheet. Ready Excel for use.
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fileName);
var sheet = book.Sheets(sheetName);
app.DisplayAlerts = false;

//Write the ID in the current cell
sheet.Cells(row, col) = data;
//Save the book.
book.Save();
app.Quit();

Log.Message("ENDFUNCTION ENDFUNCTION WriteToExcel() ENDFUNCTION ENDFUNCTION");
}

 

I think what I need to do is to perform an additional action on the workbook, before I try to save data to the file.   Any ideas?

1 Reply

Sort By
  • shankar_r's avatar
    shankar_r
    Community Hero
    8 years ago

    Hi,

     

    You want to edit your Read-Only file, there are no in-build function to change the File Access from Read-Only to write mode.(reference)

     

    However, you can save this file as new workbook and edit all over there and replace it.

     

    Steps to do it:

     

    1) Open you excel workbook as you are having code var book = app.Workbooks.Open(fileName);

    2) Save As current workbook to new workbook as like below

                      excelWorkbookObj.SaveAs(strexcelFileName);
                  excelWorkbookObj.Close();
                  excelWorkbookObj = null;

    3) Open a new workbook which you saved then edit your changes and Save it.

    4) Replace with the existing file.