Solved

Invalid Cast Exception when exporting to excel via vb.net

Posted on 2010-09-18
7
2,274 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
Comment Utility
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
Comment Utility
and the 2nd image (well, actually its the first)
references.png
0
 

Author Comment

by:chtullu135
Comment Utility
When I try to access those properties, they are grayed out
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Expert Comment

by:nepaluz
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 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

12 Experts available now in Live!

Get 1:1 Help Now