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?
Another thought...
What you are calling in your function is a file that is in your Git repository... I'm not 100% familiar with Git but I know that some source control applications like TFS and the old VisualSourceSafe have a possible setup that files are marked "Read only" if they are not actually checked out in the source control. I wonder if what you're having problems with is if the file is, for some reason, "read only", and therefore cannot be opened by excel.Workbooks.
I'm not 100% familiar with using the Excel.Application via COM like you are but, according to https://msdn.microsoft.com/en-us/library/office/ff194819.aspx there is a special parameter to add to your open call to indicate to be opened in read-only... which means that I can probably safely assume that without that parameter, it's attempting to be opened in read/write mode... and if the file IS marked as read-only, the open call is probably failing...
