C# Excel Interop, CopyPicture method of Range class failed

Question

I've looked at this for some time now and I can't find any obvious issue. I have a refreshable workbook that is linked to SQL. I need to open the workbook, refresh the data connection, save the workbook, copy a range of cells as an image and paste it into an email. It works about 50% of the time and the other 50% I get a CopyPicture method of Range class failed error.

Any help is appreciated.

here is a code snippet:

namespace Scorecard
{
    class SpreadsheetRefresh
    {
        public void Execute() 
        {
          public Excel.Application xlApp;
          public Excel.Workbook xlWorkbook;
          public Excel.Worksheet xlWorksheet;
          DateTime reportDate = DateTime.Now.AddDays(-1).Date;
          string stringDate = reportDate.ToString("d");
          try
          {
            xlApp = new Excel.Application
            {
                Visible = true,
                DisplayAlerts = true
            };
            xlWorkbook = xlApp.Workbooks.Open("c:\workbook.xlsx");

            xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item("Report");
            xlWorksheet.Cells[3, 3] = stringDate;
            xlWorksheet.Cells[4, 3] = stringDate;

            var task = Task.Run(() => refreshWorkbook());
            if (!task.Wait(TimeSpan.FromSeconds(60)))
            {
                throw new Exception("The data refresh failed due to a timeout - please try again or refresh manually.");
            }

             Excel.Range StartCell = xlWorksheet.Cells[12, 2] as Excel.Range;
             Excel.Range EndCell = xlWorksheet.Cells[17, 21] as Excel.Range;
             Excel.Range ImageRange = xlWorksheet.Range[StartCell, EndCell] as Excel.Range;
             ImageRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);

             using (FileStream filestream = new FileStream(baseImageOneName, FileMode.Create))
             {
                    BitmapSource bitmapSource = Clipboard.GetData(DataFormats.Bitmap) as BitmapSource;
                    JpegBitmapEncoder encoder = new JpegBitmapEncoder();
                    encoder.Frames.Add(BitmapFrame.Create(bitmapSource));
                    encoder.QualityLevel = 100;
                    encoder.Save(filestream);
              }
              xlWorkbook.Save();
              xlWorkbook.Close(false);
              xlApp.Quit();

          } 
          catch (Exception ex)
          {
               throw new Exception(ex.Message);
          }
       }
    }
 }

Answer 1

This is why I have the words "Refuse to e-mail data" in my how-to on instrumentation. Users expect to be convenienced with notifications, a "push system", but push systems are a central point of failure. When your process fails for any reason other than the immediate problem you've found, e.g. network outage or file locking, users will then expect you to diagnose it and possibly accept fault. Don't allow them to dump their responsibility to check their data onto you.

Instrumentation already solves this problem, because its polling is a pull process. The consumer knows he or she is responsible for checking the data, and other consumers can share that responsibility by having a copy of the monitor.

Basic definition of instrumentation

https://en.wikipedia.org/wiki/Instrumentation

Instrumentation How-to

http://www.powersemantics.com/e.html

• Not integrated (in the production process)
• Primary data only -- not images of data
• Pull not push
• Organize by process
• Immediate alerts
• Never offline

It's important to separate the responsibility of producing the spreadsheet from instrumentation needs. How users check that data and what the data means could change, and users may want to define what the result value ranges mean. They may need separate alerts, such as an emergency, depending on the values.

Users already use custom reporting solutions like SAP AO to manually monitor data themselves, without IT. You don't need to convenience them with photos. It's not the API answer you asked for, but I have written an essay on this subject.