[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Invalid Cast Exception when exporting to excel via vb.net

Posted on 2010-09-18
Medium Priority
Last Modified: 2012-05-10
I'm am having getting the following error when tryint to export to Excel from vba
"Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Range'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00020846-0000-0000-C000-000000000046}' failed due to the following error: The interface is unknown. (Exception from HRESULT: 0x800706B5)."

This error occurs on the following line of the attached code.  I thought it was being caused by mutliple instances of Excel running.  However, I monitored Windows Task Manager and saw that only one instance of Excel was running..  So that's fine.  It did work at the office.  However, I now at home and connected to the backend via vpn.
 excelSheet.Range(excelRange).Value2 = rawData
Public Shared Sub ExportMultipleGraphToExcel(ByVal datTable As DataTable, ByVal strMetricName As String, ByVal strUnits As String, ByVal strGraphStyle As String, ByRef MyWs As Excel.Worksheet)

        'Dim excelApp As New Excel.Application
            If Not datTable Is Nothing AndAlso datTable.Rows.Count > 0 Then
                Dim excelSheet As Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value
                excelSheet = MyWs

                Dim i As Integer
                Dim j As Integer
                ' Copy array of object for store data  
                Dim xlDataTable As DataTable = Nothing

                xlDataTable = datTable


                Dim rawData(xlDataTable.Rows.Count, xlDataTable.Columns.Count - 1) As Object

                'Counter for adding visible columns to array  
                Dim colCounter As Int16 = 0
                For i = 0 To xlDataTable.Columns.Count - 1
                    rawData(0, colCounter) = xlDataTable.Columns(i).ColumnName
                    colCounter += 1

                'Counter for add rows in array 
                For i = 0 To xlDataTable.Rows.Count - 1
                    colCounter = 0
                    For j = 0 To xlDataTable.Columns.Count - 1
                        ' Copy the values to the object array                              
                        rawData(i + 1, colCounter) = xlDataTable.Rows(i)(j)
                        colCounter += 1
                ' Calculate the final column letter 
                Dim finalColLetter As String = String.Empty
                Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
                Dim colCharsetLen As Integer = colCharset.Length
                If xlDataTable.Columns.Count > colCharsetLen Then
                    finalColLetter = colCharset.Substring( _
                     (xlDataTable.Columns.Count - 1) \ colCharsetLen - 1, 1)
                End If
                finalColLetter += colCharset.Substring((xlDataTable.Columns.Count - 1) Mod colCharsetLen, 1)
                ' Fast data export to Excel 
                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, xlDataTable.Rows.Count + 1)

                '****The following code is used to create variables that will be used in the Create Graph section****
                'Creates an array to hold the upper and lower limits of the range of the excel table
                Dim strExcelRange As String() = excelRange.Split(CChar(":"))
                'Stores the first cell in the range
                Dim strFirstCell As String = strExcelRange(0)
                'Stores the last cell in the range
                Dim strLastCell As String = strExcelRange(1)

                'excelSheet.Range(excelRange, misValue).Value2 = rawData
                excelSheet.Range(excelRange).Value2 = rawData
                ' Mark the first row as BOLD 
                CType(excelSheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True

                '---------------------------Create Graph----------------------------------------------
                'create chart objects
                Dim oChart As Excel.Chart
                Dim MyCharts As Excel.ChartObjects
                Dim MyCharts1 As Excel.ChartObject
                MyCharts = excelSheet.ChartObjects
                'set chart location
                MyCharts1 = MyCharts.Add(150, 290, 400, 250)

                oChart = MyCharts1.Chart
                'use the follwoing line if u want 
                'to draw chart on the default location
                oChart.Location(Excel.XlChartLocation.xlLocationAsObject, excelSheet.Name)

                Dim R As Integer = xlDataTable.Rows.Count + 1

                With oChart
                    'set data range for chart
                    Dim chartRange As Excel.Range
                    chartRange = excelSheet.Range(strFirstCell, strLastCell)
                    'set how you want to draw chart i.e column wise or row wise
                    '.PlotBy = Excel.XlRowCol.xlColumns
                    .PlotBy = Excel.XlRowCol.xlRows

                    'MessageBox.Show("Total Number of dataseries " & .SeriesCollection.count.ToString)

                    'set data lables for bars
                    'set legend to be displayed or not
                    .HasLegend = True
                    'set legend location
                    .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
                    'select chart type
                    Select Case strGraphStyle
                        Case "xlLine"
                            .ChartType = Excel.XlChartType.xlLine
                            For Each excChartSeries As Excel.Series In oChart.SeriesCollection
                                excChartSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleAutomatic
                        Case "xlColumnClustered"
                            .ChartType = Excel.XlChartType.xlColumnClustered
                        Case "xlAreaStacked"
                            .ChartType = Excel.XlChartType.xlAreaStacked
                        Case "xlColumnStacked"
                            .ChartType = Excel.XlChartType.xlColumnStacked
                    End Select

                    Dim Headers As String() = Nothing
                    'Set to number of new headers
                    Dim intH As Integer = 1
                    ReDim Headers(intH)
                    Headers(0) = "MetricName"

                    Dim strx As String = "A1"
                    Dim c As Integer = 0

                    excelSheet.Range(strx).Value = Headers(c).ToString

                    For t As Integer = 1 To chartRange.Rows.Count
                        If t = 1 Then
                            Continue For
                        Else : Dim z As String = ""
                            Dim strT As String = t.ToString
                            excelSheet.Range("A" & strT).Value = strMetricName
                            excelSheet.Range("A:A").HorizontalAlignment = Excel.Constants.xlCenter
                        End If

                    'chart title
                    .HasTitle = True
                    .ChartTitle.Text = strMetricName

                    'set titles for Axis values and categories
                    Dim xlAxisCategory, xlAxisValue As Excel.Axes
                    xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
                    xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
                    xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Forecast Years"

                    If xlDataTable.Columns.Count > colCharsetLen Then
                        finalColLetter = colCharset.Substring( _
                         (xlDataTable.Columns.Count - 1) \ colCharsetLen - 1, 1)
                    End If
                    finalColLetter += colCharset.Substring((xlDataTable.Columns.Count - 1 + intH) Mod colCharsetLen, 1)
                    excelRange = String.Format("A1:{0}{1}", finalColLetter, xlDataTable.Rows.Count + 1)

                    '****The following code is used to create variables that will be used in the Create Graph section****
                    'Creates an array to hold the upper and lower limits of the range of the excel table
                    Dim strChartRange As String() = excelRange.Split(CChar(":"))
                    'Stores the first cell in the range
                    strFirstCell = strChartRange(0)
                    'Stores the last cell in the range
                    strLastCell = strChartRange(1)
                    Dim strWorkSheetName As String
                    strWorkSheetName = excelSheet.Name.ToString

                    .SeriesCollection(1).XValues = "=" & strWorkSheetName & "!$C$1:$" & Mid(strLastCell, 1, Len(strLastCell) - 1) & "$1"
                    xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
                    xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
                    xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = strUnits
                    If strMetricName = "% Renewable Electricity Generation (Including Hydropower), annual (absolute, not compared to base)" Then
                        excelSheet.Range("A2", strLastCell).NumberFormat = "%0.0"
                    ElseIf strMetricName = "% Advanced Vehicles Based on Sales, annual (absolute, not compared to base)" Then
                        excelSheet.Range("A2", strLastCell).NumberFormat = "%0.0"
                        excelSheet.Range("A2", strLastCell).NumberFormat = "0.0"
                    End If

                End With

            End If

        Catch ex As Exception

            '    If Not excelApp.Workbooks Is Nothing Then
            '        Dim wb As Microsoft.Office.Interop.Excel.Workbook
            '        Dim ws As Microsoft.Office.Interop.Excel.Worksheet
            '        For Each wb In excelApp.Workbooks
            '            For Each ws In wb.Worksheets
            '                Marshal.FinalReleaseComObject(ws)
            '                ws = Nothing
            '            Next
            '            wb.Close(False)
            '            Marshal.FinalReleaseComObject(wb)
            '            wb = Nothing
            '        Next
            '        excelApp.Workbooks.Close()
            '    End If
            '    excelApp.DisplayAlerts = False
            '    excelApp.Quit()
            '    GC.Collect()
            '    GC.WaitForPendingFinalizers()

            '    GC.Collect()
            '    GC.WaitForPendingFinalizers()

            '    If excelApp IsNot Nothing Then
            '        Dim excelProcessId As Integer
            '        GetWindowThreadProcessId(New IntPtr(excelApp.Hwnd), excelProcessId)

            '        If excelProcessId > 0 Then
            '            KillExcel(excelApp)
            '        End If
            '    End If

        End Try

    End Sub

Open in new window

Question by:chtullu135
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 17

Expert Comment

ID: 33708930
You need to deploy the PIAs to the client machine before you can utilize interop.


if using VS 2010, you can avoid this by generating local types to be responsible for performing the interop calls to the COM library. This can be achieved for existing projects by setting the Embed Interop Types property to true in the project’s Solution Explorer reference, although this property is set to True by default for new references. Simply open Solution Explorer, select the PIA reference in the project, Right-click  on the reference and select properties of the reference , and then set Embed Interop Types to True in the window which will appear. (see images below)

LVL 17

Expert Comment

ID: 33708933
and the 2nd image (well, actually its the first)

Author Comment

ID: 33709156
When I try to access those properties, they are grayed out
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 33709207
I should have mentioned I am using vb.net 2008
LVL 17

Expert Comment

ID: 33709291
Read the blog link that I included above if you are using 2008. The properties for including interops illustrated above relate to 2010.
Find the referenced interop dll in your project on your your computer, and you can ustilise it by copying it to the executable directory (or referencing it from where it lies). I think it comes with the .net SDK (not sure about this though)

Author Comment

ID: 33709716
I've read the blog.  It looks like they are suggesting that I copy the pia to the exe folder
LVL 17

Accepted Solution

nepaluz earned 2000 total points
ID: 33709828
I doubt they suggest that, but if that is so, then go ahead. (copying the referenced dll was my CRUDE (and should have said temporary) way of resolving the error. Anyhow, I am confident you are on the right path to resolving this so, unless otherwise, I will stop monitoring this pronto.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question