Empty same cell from many excel spreadsheets

[German_John]

Hey all, 

first of all, I'm not sure whether this is the right place to ask, so I'm sorry if it isn't.

I couldn't find the answer by googling, so here goes:

 

I have a folder of excel spreadsheets from which I need to empty the same cell for all of them (say, D17). It was only needed for a sum, but needs to be empty for further processing in a different program.

It's a real PITA to open each one up and remove the cell, so I thought there must be a way to automate this. Is there?

 

Best wishes,

John

[Ecchi]

You could loop through the directory and open every excel file and clear that specific cell?

 

I'm pretty sure if you spent some time searching, you'd find something.

 

Aftter 1 search:

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Cells/How-to-remove-the-value-and-format-from-Excel-Cell-range.html

https://www.codeproject.com/Questions/785053/Remove-the-content-in-cell-using-csharp

 

Two very useful posts after  a single search. When trying to google how to do a specific task, like what your trying to do, break it down into smaller ones and search how to do that. For instance, how to loop through a directory and find all files with the .xls file extension, then I'd search how to remove a specific cell value in excel in X programming language.

 

 

[Franck]

You could write a small 10 lines in C# to do this, as long as you have microsoft office installed.

 

i'm not 100% sure of the syntax as i don't use the core office library to edit excel files but it must be something along these lines

foreach (var file in Directory.GetFiles("C:\\folder1\\folder2\\"))
{
    // create excel app
    var excelApp = new Microsoft.Office.Interop.Excel.Application();

    // open the file
    var workbook = excelApp.Workbooks.Open(file);

    // load a specific worksheet
    var worksheet = workbook.Sheets[1] as Microsoft.Office.Interop.Excel._Worksheet;

    // get the cells range
    var range = worksheet.UsedRange;

    // empty cell value row 3, column 6
    range.Cells[3, 6].Value2 = "";

    // close workbook and app
    workbook.Close();
    excelApp.Quit();
}

 

[German_John]

5 hours ago, Franck said:

You could write a small 10 lines in C# to do this, as long as you have microsoft office installed.

 

i'm not 100% sure of the syntax as i don't use the core office library to edit excel files but it must be something along these lines

foreach (var file in Directory.GetFiles("C:\\folder1\\folder2\\"))
{
    // create excel app
    var excelApp = new Microsoft.Office.Interop.Excel.Application();

    // open the file
    var workbook = excelApp.Workbooks.Open(file);

    // load a specific worksheet
    var worksheet = workbook.Sheets[1] as Microsoft.Office.Interop.Excel._Worksheet;

    // get the cells range
    var range = worksheet.UsedRange;

    // empty cell value row 3, column 6
    range.Cells[3, 6].Value2 = "";

    // close workbook and app
    workbook.Close();
    excelApp.Quit();
}

 

Thanks, I'll try that!