Empty bmp file when exporting ChartObject from excel sheet in VB.net

Question

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):

Here is an example of the bmp being created when it tries to export a chart that is not visible in the excel window: 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:

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.

Answer 1

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!