Hello, I have a bit of a problem. In C# I am doing an Windows Form Excel Addin using Visual Studio 2010. In my form I have a DatagridView that is connected to an Access databe and by pressing a button the user can send data to an Excel spreadsheet template. The area/range where the data comes into is limited to cells C10-M35, the area/range from A1 to M9 is occupied with images and headers and such. Sometimes the data that the user sends to the spreadsheet is only one line and sometimes it takes up the whole range. In my form there is another button that reads the data from the Excel sheet into a Word template. Here is where I have some problems.
How can I read from Excel only the cells that have values in them into the Word Document? I have tried to use UsedRange but that reads in everything from A1 to M35 and that is not what I want because the first cells are headers and other text that I dont want to read in. I can´t hardcode the range because it changes all the time, depending on the data that comes in. Any help is highly appricated. I am using the COM Interop. Here is what I got so far.
string FileName = @"C:\MyFile.xlsx";
Excel.Application xlApp = xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
xlWorkBook = xlApp.Workbooks.Open(FileName);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//Here are some different versions that I tried. I also tried to use the SpecialCell method but it doesn´t seem to work. Maybe I don´t know how to?
Excel.Range myRange = (Excel.Range)xlWorkSheet.get_Range("C10", "M35"); //Doesn´t work because it copies the blank cells as well
Excel.Range myRange1= (Excel.Range)xlWorkSheet.UsedRange; // Could I limit UsedRange to "C10", "M35"?
Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range myRange2 = sheet.get_Range("C10", last); // Doesn´t seem to work either
//then to copy over to Word I would use
myRange.Copy();
//and then...some code missing for opening a Word template.
Word.Range wordRange;
wordRange.Paste();
Thank you all.