Setting Excel Workbook in code when writing

Best practices, code snippets for common functionality, examples, and guidelines.
Slinty
Posts: 1
Joined: Thu Dec 07, 2017 10:54 am

Setting Excel Workbook in code when writing

Post by Slinty » Thu Dec 07, 2017 11:02 am

Hi,
I know this topic has been addressed a few times in the past, but I can't find anything along these exact lines.
I'm using code to write a value to Excel - the workbook opens and the input is consistently written into the correct cell reference, the workbook is saved and closed all fine. However, the the worksheet last open/saved is the one that is written to, rather than the one I need/specify.
I'm using Excel 2010 and an xlsb file.
My code, adapted from an example on a blog post from a few years ago, looks like:
Excel.Application ExcelDataconnectorSourceFile = new Excel.ApplicationClass();            
ExcelDataconnectorSourceFile.Visible = true;
Excel.Workbook ExcelWorkbook = ExcelDataconnectorSourceFile.Workbooks.Open("<path to sheet>");
Excel.Sheets ExcelSheets = ExcelWorkbook.Worksheets;
Excel.Worksheet LoggingSheet = (Excel.Worksheet)ExcelSheets.get_Item("Logging");
ExcelDataconnectorSourceFile.Cells[2,8] = _CurrentCount + 1;
ExcelWorkbook.Save();
ExcelWorkbook.Close();
ExcelDataconnectorSourceFile.Application.Quit();
I've tried replacing the string parameter in get_Item with the number of the sheet, but it didn't help.

Vaughan.Douglas
Posts: 250
Joined: Tue Mar 24, 2015 5:05 pm
Location: Des Moines, Iowa, USA

Re: Setting Excel Workbook in code when writing

Post by Vaughan.Douglas » Mon Dec 11, 2017 6:31 pm

I don't know if this is your problem, but I use Excel application over ApplicationClass

Code: Select all

Excel.Application ExcelDataconnectorSourceFile = new Excel.Application;      
I'm not familiar with the "get_Item" method you're using, but then all of my samples are in VB .Net. Not sure if that makes a difference.

Code: Select all

        
Dim myXlApp As New Microsoft.Office.Interop.Excel.Application
Dim myWorkBk As Microsoft.Office.Interop.Excel.Workbook = myXlApp.Workbooks.Open("FilePath")
Dim wsh As Microsoft.Office.Interop.Excel.Worksheet = myWorkBk.Worksheets("WorksheetName")
I tried to run it through a code converter, but it just barfed. I'm pretty sure the interopt is going away.

***Edit***
Found an example and added to it.

Code: Select all

            var exApp = new Microsoft.Office.Interop.Excel.Application();
            var exWbk = exApp.Workbooks.Open("FILEPATH");
            var exWks = (Microsoft.Office.Interop.Excel.Worksheet)exWbk.Sheets["WorksheetName"];
            exWks.Cells[2, 8] = "Some Value";
            exWbk.Save();
            exWbk.Close();
Doug Vaughan