0

I'm working on a program that allows the user to pull up part numbers from a database. The part numbers are then to be pasted into an active Excel Sheet.

I'm trying to do this with Excel interop Excel 16.0. I can copy the data but am having issues getting it to paste into excel.

private void cmdCopyToExcel_Click(object sender, EventArgs e)
    {
        string wb = cmb_BookName.Text.ToString();
        string ws = cmb_SheetName.Text.ToString();

        if (chkContainer.Checked)
        {
            Excel.Application xlApp = new Excel.Application();

            Excel.Workbook xlWorkbook = xlApp.Workbooks[wb];                      
            Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[ws];
            
            xlWorksheet.Cells[48, 4] = cboContainer.Text;              
        }

I'm able to get the open excel workbook and worksheet that I need, but when I try to paste it into excel all I get is a COM Exception. The exception occurs on line 10. I've tried using ("wb name") and ("ws name"), have also tried using index numbers [1] for workbook and [3] for worksheets but nothing works.

Can anyone see what I'm doing wrong or is there an easier way to copy from C# and paste into an excel cell?

Addition:I tried opening the workbook that I wanted to add test to, just to see if I could get it to work.

Here is the code:

private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            //create a instance for the Excel object  
            Excel.Application oExcel = new Excel.Application();

            //specify the file name where its actually exist  
            string filepath = @"K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\Test Matrices\JRD Test Matrix for part numbers.xlsm";

            //pass that to workbook object  
            Excel.Workbook WB = oExcel.Workbooks.Open(filepath);

            // statement get the workbookname  
            string ExcelWorkbookname = WB.Name;

            // statement get the worksheet count  
            int worksheetcount = WB.Worksheets.Count;

            Excel.Worksheet wks = (Excel.Worksheet)WB.Worksheets[3];

            // statement get the firstworksheetname  

            string firstworksheetname = wks.Name;

            //statement get the first cell value  
            var firstcellvalue = ((Excel.Range)wks.Cells[48, 4]).Value;
        }
        catch (Exception ex)
        {
            string error = ex.Message;
        }
    }
}

}

This worked, so I guess my question becomes how to work with an Excel workbook and worksheet that are already open?

jrdnoland
  • 115
  • 10
  • Why do you expect an empty Excel.Application to have 3 worksheets? or even a workbook? – Steve May 16 '22 at 19:41
  • Steve - the workbooks are not empty they each have 8 worksheets in them. My code is selecting the correct sheet and the c# combo boxes contain the text I want to transfer. That's why I also have the check boxes, when they are checked it's a way of letting the receiving workbook know where the c# test will go. – jrdnoland May 16 '22 at 19:49
  • Well, the code above doesn't load any file. So xlApp is empty in that code. – Steve May 16 '22 at 20:09
  • @Steve is correct. You have not loaded a file, all you have is an instance of the excel application running. It does not have any workbook loaded. `workbook = workbooks.Open(pathToWorkbook);` – hijinxbassist May 16 '22 at 20:10
  • I'm trying to get the running Excel file with Excel.Workbook xlWorkbook = xlApp.Workbooks["1"]; and then the active sheet with Excel.Worksheet xlWorksheet = xlWorkbook.Worksheets[1]; Am I not doing that correctly. – jrdnoland May 16 '22 at 21:44

1 Answers1

0

I would start by getting a range of cells: i.e.{Range test = {yourWorksheet}.Cells[RowNumber, ColumnNumber]}

Once you have that, you can set the value of that cell by calling the value, or value2 property like so: {test.Value or test.Value2 = {yourPastedData}}

Steven Wangler
  • 33
  • 2
  • 11
  • Clarification: you can also use the worksheet.cells method as mentioned in your post. so it would be more along the lines of xlWorksheet.Cells[48, 4].Value2 = cboContainer.Text; – Steven Wangler May 16 '22 at 20:35
  • Ok, but I still can't get past the section trying to reference the workbook. – jrdnoland May 16 '22 at 21:45
  • This may help you with that: https://stackoverflow.com/questions/2491100/obtaining-excel-worksheet-reference-by-worksheet-name-via-c-sharp – Steven Wangler May 17 '22 at 12:49