Solved

Invalid Cast Exception when exporting to excel via vb.net

Posted on 2010-09-18
7
2,291 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now