Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

564 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