Try altering your path name... my guess is that the Workbook is not actually getting opened so excel.ActiveWorkbook might be a null object. Right now, your path is using the wrong slash... Try
var filepath = "C:\\Repositories\\Git\\Repos\\Automation\\Automation\\ExcelSheets\\" + filename;
This should format your string properly for the call to open the workbook.
Also... make sure that you don't already have the workbook open... since you are using the Excel application directly, you might have a problem with exclusive access...
Finally... what is the reason for using this function rather than implementing DDT.ExcelDriver? It seems you want to iterate through rows in a sheet and use the data in each row... the DDT.ExcelDriver and similar DDT objects were designed to do exactly that.
Still got the same error after changing the file path to what you suggested. I also checked that Excel.exe is not running when I go to run the test.
This is mainly confusing to me as pretty much the same code in another file works fine.
var Excel = Sys.OleObject("Excel.Application");
Excel.Workbooks.Open("C:/Users/UserName/Documents/Test Cases.xlsx");
var xlsheet = Excel.ActiveWorkBook.WorkSheets("CategoryGroup");
I am also using this function because it was the easiest to understand when I started doing it.
You're example of where it is working someplace else has another significant difference... the name of the spreadsheet file is actually hardcoded in the code... the function you posted initially passes that filename in as a parameter. It's ENTIRELY possible that, when you're getting this error, that the filename may be incorrect... double check that by dropping a breakpoint in your function at excel.Workbooks.Open(filepath) and check the value of filepath.
You can also, step to the next line and then do a check to see if excel.ActiveWorkbook is actually a non-null value... again, just to double check... but I think the reason for your error is that ActiveWorkbook probably is null and so, therefore, Worksheets cannot be called.
Good idea to check the Active workbook.
Turns out it is null, but I can't imagine why.
The file path I create is valid: C:\Repositories\Git\Repos\Automation\Automation\ExcelSheets\Fees Test Cases.xlsx
Is there something more to opening files with variable names that I am missing?
