0

A Visual Management Board at my workplace uses an excel spreadsheet, with many sheets in it, to populate a VMB on a TV. These sheets have many charts in them. I don't work a whole lot in VB so please bear with me.

I'm convinced my problem is because the chart the program is trying to access, literally isn't visible in the excel sheet that pops up. I may just not know what I'm talking about but this seems like a terrible way to get and display data. But I'm supposed to fix it. Here's the sheet that pops up on the screen (I've erased some information for privacy reasons): enter image description here

Here is an example of the bmp being created when it tries to export a chart that is not visible in the excel window: enter image description here From my research, I've found many have resolved this issue by Activating the chart object before exporting it. I tried to do that here, but an exception gets thrown. Here's the entire section of code dealing with the charts and exports to make BMPs that are supposed to reside in the Documents folder:

    Dim xlApp As Excel.Application
    Dim xlWorkBooks As Excel.Workbooks
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheets As Excel.Sheets
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.Application
    xlApp.Visible = True
    xlWorkBooks = xlApp.Workbooks
    xlWorkBook = xlWorkBooks.Open(ScoreCard)
    xlWorkSheets = xlWorkBook.Sheets




    For x As Integer = 1 To xlWorkSheets.Count
        xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)


        If xlWorkSheet.Name = My.Settings.Org Then


            xlWorkSheet.ChartObjects(2).chart.Export(Filename:=path + "\Documents\OnTimeDelivery.bmp", FilterName:="BMP")
            picOnTimeDelivery.Image = New System.Drawing.Bitmap(path + "\Documents\OnTimeDelivery.bmp")
            xlWorkSheet.ChartObjects(3).chart.Export(Filename:=path + "\Documents\Quality.bmp", FilterName:="BMP")
            picQuality.Image = New System.Drawing.Bitmap(path + "\Documents\Quality.bmp")
            xlWorkSheet.ChartObjects(1).chart.Export(Filename:=path + "\Documents\NoDemandInventory.bmp", FilterName:="BMP")
            picNoDemandInventory.Image = New System.Drawing.Bitmap(path + "\Documents\NoDemandInventory.bmp")
            xlWorkSheet.ChartObjects(7).chart.Export(Filename:=path + "\Documents\ExcessInventory.bmp", FilterName:="BMP")
            picExcessInventory.Image = New System.Drawing.Bitmap(path + "\Documents\ExcessInventory.bmp")
            xlWorkSheet.ChartObjects(4).chart.Export(Filename:=path + "\Documents\Freight.bmp", FilterName:="BMP")
            picFreight.Image = New System.Drawing.Bitmap(path + "\Documents\Freight.bmp")
            xlWorkSheet.ChartObjects(5).chart.Export(Filename:=path + "\Documents\ShortagesByStart.bmp", FilterName:="BMP")
            picShortagesByStart.Image = New System.Drawing.Bitmap(path + "\Documents\ShortagesByStart.bmp")
            xlWorkSheet.ChartObjects(6).chart.Export(Filename:=path + "\Documents\ShortagesRootCause.bmp", FilterName:="BMP")
            picShortagesRootCause.Image = New System.Drawing.Bitmap(path + "\Documents\ShortagesRootCause.bmp")


        End If

        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)

    Next
    xlWorkBook.Close()
    xlApp.UserControl = True
    xlApp.Quit()

    'Close connection to excel sheet
    MyConnection.Close()

The program crashes, throwing an Invalid Parameter exception when trying to set

picFreight.Image = New System.Drawing.Bitmap(path + "\Documents\Freight.bmp")

because the Freight.bmp in my documents folder is a 0kb file. If I change what image it's loading next (comment that line out and let it try to load ShortagesByStart.bmp) it crashes for the same reason. All of the charts past this point have one thing in common, they aren't visible on screen. Still this seems like a stupid reason to me; surely something like that wouldn't cause an issue!

First I tried to Activate the xlWorkSheet

xlWorkSheet.Activate()

but this changed nothing.

So I tried to activate the individual ChartObjects by adding 

            xlWorkSheet.ChartObjects(2).chart.Activate()
            xlWorkSheet.ChartObjects(3).chart.Activate()
            xlWorkSheet.ChartObjects(1).chart.Activate()
            xlWorkSheet.ChartObjects(7).chart.Activate()
            xlWorkSheet.ChartObjects(4).chart.Activate()

before the export statements. This actually threw an exception: enter image description here

So, at this point I'm stuck. How can I activate the chart objects in the worksheet properly? Perhaps there's another problem that's causing this.

Caleb W.
  • 129
  • 2
  • 11
  • 1
    Seems the worksheet might not be active when you try to activate its chartobjects? That would throw an error in VBA as well - in managed code that's a `COMException` just like what you're getting. – Mathieu Guindon Aug 15 '17 at 20:20
  • I made the thread sleep for 5 seconds before attempting the chartobjects activation but still threw an exception. The excel window was up and done loading if that's what you mean by active. Even after calling xlWorkSheet.Activate() before sleeping the thread. Thanks for the comment! – Caleb W. Aug 15 '17 at 20:29
  • 1
    No need to sleep the thread, *that* won't change anything. By "active" I mean you **must** call `xlWorkSheet.Activate()` *before* you try to `.Activate()` *anything* on that worksheet object (be it a `ChartObject` or a `Range` - although you typically wouldn't *want* to activate a `Range`). – Mathieu Guindon Aug 15 '17 at 20:30
  • 2
    Also.. "surely something like that wouldn't cause an issue!" - clearly you over-estimate (under-estimate?) the kind of gigantic hack embedded OLE objects are. – Mathieu Guindon Aug 15 '17 at 20:33
  • Cool thanks for the clarification. And yes I definitely am not familiar with these OLE Objects! I'm an intern and most of what I've done is just WPF application developement without using excel speadsheets or databases. yuck haha. So I did call `xlWorkSheet.Activate()` and no change :/ – Caleb W. Aug 15 '17 at 20:37
  • I have never had to activate a chart before saving. That said, you can not call `Activate` on an embedded chart; however, you can call `Activate` on the embedded `ChartObject`. Activating the `ChartObject` will not bring into view, it makes its Chart the `ActiveChart` of the `Application` object. You should verify (in Excel) that the Workbook's charts are valid. – TnTinMn Aug 16 '17 at 01:01
  • 1
    It hasn't been mentioned, but you should clean up all your COM objects, not just `xlWorkSheet`. See https://stackoverflow.com/a/158752/832052 – djv Aug 16 '17 at 01:39
  • Also https://stackoverflow.com/a/159419/832052. You may need to create a variable for each chart object and chart, and clean them all up accordingly. – djv Aug 16 '17 at 01:43
  • Thanks so much for your response....I was wondering why I had like 15 Excel processes keeping my computer from restarting that evening! – Caleb W. Aug 17 '17 at 13:34
  • 1
    @djv, please do not promote that Office COM object cleanup myth. There are COM rferences that you must explicitly release, but not these. see: [https://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable/25135685#25135685](https://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable/25135685#25135685) – TnTinMn Aug 18 '17 at 13:28

1 Answers1

0

So the solution was to update Microsoft Office on whichever PC we wanted to run the VMB from. Corporate creates this Excel spreadsheet with the data on it that this program was trying to display. Eventually, whoever creates this excel sheet got a newer version of Excel, which creates an .xlxs file.

I honestly don't know why the spreadsheet successfully opened in ReadOnly mode, but I suppose there wasn't full support there for Office 2007 and 2010. After upgrading the copy of office on the PC the program created all of the bmps. Weird and I'm sorry if this isn't much of an answer for others but this resolved my issue!

Caleb W.
  • 129
  • 2
  • 11