Click here to Skip to main content
14,661,016 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi,

I'm trying to edit some of the details in an excel file, but, getting Runtime binder exception "Cannot perform runtime binding on a null reference".

Some of the manipulations I'm trying to do is:

i. renaming the sheet name,
ii. replacing the word 'yesterday' with yesterday's date 01/03/2018,
iii. insert few rows if the header is not in the expected row.


I've added excel dll reference and imported the namespace using Microsoft.Office.Interop.Excel.

Below is my code:


Could someone suggest what am I doing wrong here..?

What I have tried:

string folder = Path.GetDirectoryName(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
string fileName = Path.GetFileNameWithoutExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
string extension = Path.GetExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");

FileInfo info = new FileInfo(fileName + extension);
{
    // Rename the file as FileName_YYYYMMDD date format.
    string newName = folder + "\\" + fileName + "_" + ISO_Date() + extension;

    if (!File.Exists(newName))
    {
        // info.MoveTo(newName);
    }
}

MyApp = new Excel.Application();
MyApp.Visible = false;
xlBook = MyApp.Workbooks.Open(@"D:\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1); // Explicit cast is not required here
// Modify the sheet name to 'Sheet1'
xlSheet.Name = "Sheet1";
xlBook.Save();

int totalRows = xlSheet.Rows.Count;
int totalCols = xlSheet.Columns.Count;

// replace word 'yesterday' in second row with date
if(xlSheet.Cells[2, 1].Value.Contain("yesterday"))
{
    xlSheet.Cells[2, 1].Value.Replace("yesterday", DateTime.Today.AddDays(-1));
}
// if it is date range, remove the first date
else if (xlSheet.Cells[2, 1].Value.Contain("-"))
{
    int indexOf = xlSheet.Cells[2, 1].Value.IndexOf("-");
    xlSheet.Cells[2, 1].Value.Remove(indexOf, -14);
}

Excel.Range range = (Excel.Range)xlSheet.Columns["A", Type.Missing];
Excel.Range currentFind = null;
Excel.Range firstFind = null;

// Name column header should start from 21st row.
if(xlSheet.Cells[21, 1].Value != "Name")
{
    currentFind = range.Find("Name", xlSheet.Cells[1, 1],
                              Excel.XlFindLookIn.xlValues,
                              Excel.XlLookAt.xlPart,
                              Excel.XlSearchOrder.xlByRows,
                              Excel.XlSearchDirection.xlNext,
                              false,
                              false,
                              false);
}
Posted
Updated 25-Nov-19 22:15pm
Comments
ZurdoDev 4-Jan-18 7:23am
   
Step one. What line of code is causing the error? Step 2. Figure out what is null and why.
Samuel Corpuz 4-Jan-18 21:18pm
   
try to break and continue the program, it may help to see what is the null value and error.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

A lot of errors you've been made...

Please, read my comments:

Why to use methods of Path class, if you can initiate variables directly?
A drive letter has been missed!
//string folder = Path.GetDirectoryName(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
//string fileName = Path.GetFileNameWithoutExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
//string extension = Path.GetExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");

Rather than above, use this:
string fullFileName = @"C:\OpsVsAdp\Files\Daily\TempHours.xlsx";


Below part of code won't be necessary, because you can open file in Excel and "move" it into different location by using SaveAs method.
FileInfo info = new FileInfo(fileName + extension);
{
    // Rename the file as FileName_YYYYMMDD date format.
    string newName = folder + "\\" + fileName + "_" + ISO_Date() + extension;

    if (!File.Exists(newName))
    {
        // info.MoveTo(newName);
    }
}


You should use fullFileName variable instead of string containing a path to file. Doing it this way is called "good programming practice" and let you avoid of several mistakes later.
MyApp = new Excel.Application();
MyApp.Visible = false;
//proper way:
xlBook = MyApp.Workbooks.Open(fullFileName);


To be able to modify sheet name, you should check its name firstly.
xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1); // Explicit cast is not required here
// Check and change sheet name to 'Sheet1'
if(xlSheet.Name != "Sheet1")
    xlSheet.Name = "Sheet1";
//you don't need to save a workbook now
//as i mentioned above, you can do it later by using SaveAs method
//xlBook.Save();


totalRows and totalCols variables have been initiated, but never used!
If you know the exact address of cell containing date (or date range or any other data), you can change its content directly!
//int totalRows = xlSheet.Rows.Count;
//int totalCols = xlSheet.Columns.Count;

// in second row 
// replace word 'yesterday' with date 
// in case of date range, remove the first date
string stmp = (string)xlSheet.Cells[2, 1].Value;
if(stmp == "yesterday")
    xlSheet.Cells[2, 1].Value = DateTime.Today.AddDays(-1);

if (stmp.Contains("-"))
    xlSheet.Cells[2, 1].Value = stmp.Remove(ind, ind);


Sorry, but the other part of code is not clear to me...

To save workbook in different location, call this:
string sNewFileName = Path.Combine(Path.GetDirectoryName(fullFileName), 
	string.Concat(Path.GetFileNameWithoutExtension(fullFileName), "_", DateTime.Today.ToString("yyyyMMdd"), ".xslx"));
xlBook.SaveAs(sNewFileName);
MyApp.Quit();
//read a Final note!


Final note: do not forget release resources! For further details, please see: How to Release COM Interop Objects so the Called Program Can Exit[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100