Solved

Invalid Cast Exception when exporting to excel via vb.net

Posted on 2010-09-18
7
2,328 Views
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
        Try
            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


                xlDataTable.Columns.Remove("MetricName")

                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
                Next

                '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
                    Next
                Next
                ' 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
                'MessageBox.Show(R.ToString)

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

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

                    'set data lables for bars
                    .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
                    '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
                            Next
                        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("A:A").Insert(Excel.XlDirection.xlToRight)
                    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
                    Next

                    '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"
                    Else
                        excelSheet.Range("A2", strLastCell).NumberFormat = "0.0"
                    End If


                End With

            End If


        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)

            'Finally
            '    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

0
Comment
Question by:chtullu135
  • 4
  • 3
7 Comments
 
LVL 17

Expert Comment

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

http://blogs.msdn.com/b/vsto/archive/2008/05/20/common-pitfalls-during-pia-deployment-and-installation.aspx

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)


reference1.png
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 33708933
and the 2nd image (well, actually its the first)
references.png
0
 

Author Comment

by:chtullu135
ID: 33709156
When I try to access those properties, they are grayed out
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

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

Expert Comment

by:nepaluz
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)
0
 

Author Comment

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

Accepted Solution

by:
nepaluz earned 500 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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 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