Solved

What is  System.Reflection.Missing.Value

Posted on 2010-09-01
18
1,667 Views
Last Modified: 2012-05-10
Hello,
I'm trying to debug a vb.net program.  It uses automation to create graphs and it is failing at  
 Dim misValue As Object = System.Reflection.Missing.Value
I've been trying to find out what System.Reflection.Missing.Value is and what it does
0
Comment
Question by:chtullu135
  • 9
  • 9
18 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33581070
maybe this as described here http://msdn.microsoft.com/en-us/library/system.reflection.missing.value.aspx
used to hold something for handle default parameter values on functions
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 33581083
code example here, easier than me trying to explain lol http://msdn.microsoft.com/en-us/library/system.reflection.missing.aspx
0
 

Author Comment

by:chtullu135
ID: 33581344
I've got a hazy idea of what it does.  Basically it substitutes for a missing argument.  Basically I am getting an com error on the line .  I am trying to pass a worksheet to a function where a graph will be added to it.
excelSheet.Range(excelRange, misValue).Value2 = rawData
 Public Shared Sub ExportMultipleGraphToExcel(ByVal datTable As DataTable, ByVal strMetricName As String, ByVal strUnits As String, ByVal strGraphStyle As String, ByVal MyWs As Excel.Worksheet)
        'Dim filename As String
        Dim excelApp As New Excel.Application
        'Try
        If Not datTable Is Nothing AndAlso datTable.Rows.Count > 0 Then

            Dim excelWorkbook As Excel.Workbook
            Dim excelSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            'excelApp = New Excel.ApplicationClass
            'excelApp.Visible = True

            excelWorkbook = excelApp.Workbooks.Add(misValue)
            'excelSheet = excelWorkbook.Sheets(MyWs.Name)
            excelSheet = MyWs
            'excelApp.ScreenUpdating = True
            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)
            'excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper

            ' Dim visibleColCount As Integer = 0 
            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("BudgetYear")
            xlDataTable.Columns.Remove("MetricName")
            'xlDataTable.Columns.Remove("Units")
            'xlDataTable.Columns.Remove("TechnologySetCode")

            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
                'Str += dt.Columns(i).HeaderText & vbTab 
                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
            ' 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
                'For c As Integer = 0 To 1
                excelApp.Range("A:A").Insert(Excel.XlDirection.xlToRight)

                excelApp.Range(strx).Value = Headers(c).ToString
                'Next c

                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

                        excelApp.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)

                'MessageBox.Show(Mid(strLastCell, 1, Len(strLastCell) - 1).ToString)
                .SeriesCollection(1).XValues = "=Sheet1!$C$1:$" & Mid(strLastCell, 1, Len(strLastCell) - 1) & "$1"


                '.SeriesCollection(1).XValues = "=Sheet1!$F$1:$N$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

                excelSheet.Range("A2", strLastCell).NumberFormat = "0.0"

                MessageBox.Show("Chart Completed")

            End With

            'Set file format number to 56 if excel version <> 2007 
            'If Not filename.EndsWith(".xlsx") Then
            '    excelWorkbook.SaveAs(filename, 56)

            'Else
            '    excelWorkbook.SaveAs(filename)
            'End If

            MsgBox("File generated successfully at ") '& filename)

        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()

        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
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.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33581541
In this line
excelWorkbook = excelApp.Workbooks.Add(misValue)

Can you get away with not having to specify misValue? I think it takes a template name which is optional anyway.


Regarding the dodgy line, I am not sure what you are trying to do there. Setting a range will work with just one argument surely

excelSheet.Range(excelRange).Value2 = rawData

the 2nd argument is if you want to specify another cell.
0
 

Author Comment

by:chtullu135
ID: 33581679
I've tried as you suggested and received the following erro at line
 excelSheet.Range(excelRange).Value2 = rawData
System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2147221080
  Message="Exception from HRESULT: 0x800401A8"
  Source="Microsoft.Office.Interop.Excel"
  StackTrace:
       at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
       at BADS.Utilities.ExportMultipleGraphToExcel(DataTable datTable, String strMetricName, String strUnits, String strGraphStyle, Worksheet& MyWs) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\Utilities.vb:line 295
       at BADS.frmExportGraph.CreateGraphTable(Int32 intDatasetGroupId, String strName, Worksheet ws) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmExportGraph.vb:line 218
       at BADS.frmExportGraph.btnExport_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmExportGraph.vb:line 149
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at BADS.frmMultipleDatasetGraphs.ExportGraphsToolStripMenuItem_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmMultipleDatasetGraphs.vb:line 757
       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ToolStrip.WndProc(Message& m)
       at System.Windows.Forms.MenuStrip.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at BADS.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Open in new window

0
 

Author Comment

by:chtullu135
ID: 33581862
Basically, What I am trying to do is pass a worksheet to the subroutine ExportMultipleGraphToExcel and populate it with a graph.  After it is populated, the next worksheet is populated with a graph and so on
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33581956
brb, getting visual studio 2010 installed. I will double check it then
0
 

Author Comment

by:chtullu135
ID: 33582129
Thanks
0
 

Author Comment

by:chtullu135
ID: 33582296
Here is the complete code as well as an explanation
I have a form called ExportGraph.  It has a button called btnExport which when clicked loops through a datagridview in order to populate a list object called dgl

I then call Utilities.CreateWorkbook() in order to create a workbook.  I then loop through dgl in order to add a new worksheet for every object in the list to the workbook I created.  This is done via function OpenWorkbook.  I return a worksheet from OpenWorkbook which I then pass to subroutine CreateGraphTable.  At the end of this subroutine I call the ExportMultipleGraphToExcel subroutine which is where the graphs are created and where they populate specific spreadsheet.  This is where I am having my problem
 Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim dgl As New List(Of DatasetGroup)
        'Try
        For Each row As DataGridViewRow In dgvDatasetGroups.Rows
            Dim cell As DataGridViewCell = row.Cells(8)
            If CBool(cell.Value) = True Then
                Dim dg As New DatasetGroup
                Dim indexcell As DataGridViewCell = row.Cells(0)
                dg.DatasetGroupId = indexcell.Value
                cell = row.Cells(1)
                dg.Name = cell.Value
                dgl.Add(dg)
            End If
        Next

        Dim strWorkbookPath As String = Utilities.CreateWorkbook()
        MessageBox.Show(strWorkbookPath, "Returned Workbook Path")
        For Each dg As DatasetGroup In dgl
            Dim strName As String = dg.Name.ToString
            Dim ws As New Excel.Worksheet
            ws = Utilities.OpenWorkbook(strWorkbookPath, strName)
            CreateGraphTable(Convert.ToInt32(dg.DatasetGroupId), strName, ws)
        Next
        'Catch ex As Exception
        'MessageBox.Show(ex.Message, ex.GetType.ToString)
        'End Try
    End Sub

 Public Shared Function CreateWorkbook() As String
        Dim filename As String
        Dim excelApp As Excel.Application
        Dim excelWorkbook As Excel.Workbook
        Dim excelSheet As Excel.Worksheet
        Dim SaveFileDialog As New SaveFileDialog



        If My.Settings.DefaultLocation = "" Then
            SaveFileDialog.InitialDirectory = "C:\"
        Else
            SaveFileDialog.InitialDirectory = My.Settings.DefaultLocation.ToString
        End If

        SaveFileDialog.Filter = "xlsx files (*.xlsx)|*.xlsx"
        SaveFileDialog.FilterIndex = 2
        SaveFileDialog.RestoreDirectory = True

        'Try
        '***************************Creates Spreadsheet*********************************************

        If SaveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then

            filename = SaveFileDialog.FileName

            'Validate file name
            If Convert.ToString(filename) = "" Then
                Return Nothing
                Exit Function
            End If

            'Create excel instance
            Dim misValue As Object = System.Reflection.Missing.Value
            excelApp = New Excel.ApplicationClass
            'excelApp.Visible = True
            excelWorkbook = excelApp.Workbooks.Add(misValue)
            excelSheet = excelWorkbook.Sheets("sheet1")
            excelApp.ScreenUpdating = True
            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)
            excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper
            If Not filename.EndsWith(".xlsx") Then
                excelWorkbook.SaveAs(filename, 56)
            Else
                excelWorkbook.SaveAs(filename)
            End If

            MsgBox("File generated successfully at " & filename)
            excelWorkbook.Close()
            excelApp.Quit()
            excelWorkbook = Nothing
            excelApp = Nothing
            Return filename
        Else
            Return Nothing
        End If
        KillExcel(excelApp)
    End Function

Public Shared Function OpenWorkbook(ByVal strFilePath, ByVal strWorkSheetName) As Excel.Worksheet

        'declares the variables
        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)
        Dim ws As Excel.Worksheet = Nothing
        'try to open the workbook and a worksheet
        Try
            excelBook = excelApp.Workbooks.Open(strFilePath)
            ws = excelBook.Worksheets.Add
            excelApp.Visible = True
            If Len(strWorkSheetName) > 31 Then
                strWorkSheetName = InputBox("The length of " & strWorkSheetName & " exceeds the limit of 31 characters " & vbCrLf & "Please enter a name not exceeding 31 characters", "Max Size Exceeded")
            End If
            ws.Name = strWorkSheetName
            Return ws
        Catch ex As Exception
            Return Nothing
            MsgBox(ex.Message, ex.GetType.ToString)
        Finally
            'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this
            'The service (excel.exe) will continue to run
            excelBook.Close()
            excelApp.Quit()

            excelBook = Nothing
            excelApp = Nothing

            NAR(excelApp)
            'VERY IMPORTANT
            GC.Collect()
        End Try

    End Function
    Public Shared Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
        Finally
            o = Nothing
        End Try
    End Sub

 Private Sub CreateGraphTable(ByVal intDatasetGroupId As Integer, ByVal strName As String, ByVal ws As Excel.Worksheet) 'As DataTable
        Dim graphs As New List(Of GraphReport)
        Dim dset As New DataSet("MyDataSet")
        Dim strTitleName As String = ""
        Dim strUnits As String = ""
        Dim dpl As New List(Of DatasetPair)
        'Try
        dpl = DatasetPairDB.GetExportDatasetGroupDetail(intDatasetGroupId)

        For Each dp As DatasetPair In dpl
            Dim objgraph As New GraphReport
            objgraph.DataVersionIdOne = dp.DataVersionIdOne.ToString
            objgraph.DataVersionIdTwo = dp.DataVersionIdTwo.ToString
            objgraph.MetricName = dp.MetricName.ToString
            objgraph.GraphTitle = strName
            objgraph.GraphType = dp.GraphType
            graphs.Add(objgraph)
        Next

        For Each g As GraphReport In graphs

            If MatchingMetricUnits(g) = Nothing Then
                MessageBox.Show(g.DataVersionIdOne.ToString & " and " & g.DataVersionIdTwo.ToString & " do not have corresponding units!", "Erro")
                'Return Nothing
                Exit Sub
            Else
                strUnits = MatchingMetricUnits(g)
                Units = strUnits
            End If

            Dim ds As New DataTable
            ds = ReportDB.GetDeltaSeriesData(g.DataVersionIdOne, g.DataVersionIdTwo, g.MetricName)

            Dim dtgv As New DataTable
            dtgv = ReportDB.GetDeltaReportData(g.DataVersionIdOne, g.DataVersionIdTwo, g.MetricName)

            If dtgv.Rows.Count < 1 Then
                MessageBox.Show("No Data")

                Exit Sub
            End If

            Dim newtable As New DataTable

            dset.Tables.Add(dtgv)
            For Each table As DataTable In dset.Tables
                newtable.Merge(table)
            Next
            dgvGraphResults.DataSource = newtable
            Me.DgvTable = newtable

            Dim strSeriesName As String = g.DataVersionIdOne.ToString & " - " & g.DataVersionIdTwo.ToString
            MySeriesName = strSeriesName

            strTitleName = g.GraphTitle.ToString
            MyMetricName = g.GraphTitle.ToString
            MyGraphStyle = g.GraphType

        Next
        Dim strGraphStyle As String = MyGraphStyle
        Utilities.ExportMultipleGraphToExcel(DgvTable, MyMetricName, Units, strGraphStyle, ws)
        'Catch ex As Exception
        'MessageBox.Show(ex.Message.ToString & Err.Number.ToString)
        'End Try
    End Sub

  'Dim filename As String
        Dim excelApp As New Excel.Application
        'Try
        If Not datTable Is Nothing AndAlso datTable.Rows.Count > 0 Then

            Dim excelWorkbook As Excel.Workbook
            Dim excelSheet As Excel.Worksheet
            MessageBox.Show(System.Reflection.Missing.Value.ToString)
            'Dim misValue As Object = System.Reflection.Missing.Value
            'excelApp = New Excel.ApplicationClass
            'excelApp.Visible = True

            ' excelWorkbook = excelApp.Workbooks.Add(misValue)
            'excelSheet = excelWorkbook.Sheets(MyWs.Name)
            excelSheet = MyWs

            'excelApp.ScreenUpdating = True
            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)
            'excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper

            ' Dim visibleColCount As Integer = 0 
            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("BudgetYear")
            xlDataTable.Columns.Remove("MetricName")
            'xlDataTable.Columns.Remove("Units")
            'xlDataTable.Columns.Remove("TechnologySetCode")

            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
                'Str += dt.Columns(i).HeaderText & vbTab 
                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
            excelSheet.Range(excelRange).Value = 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
                'For c As Integer = 0 To 1
                excelApp.Range("A:A").Insert(Excel.XlDirection.xlToRight)

                excelApp.Range(strx).Value = Headers(c).ToString
                'Next c

                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

                        excelApp.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)

                'MessageBox.Show(Mid(strLastCell, 1, Len(strLastCell) - 1).ToString)
                .SeriesCollection(1).XValues = "=Sheet1!$C$1:$" & Mid(strLastCell, 1, Len(strLastCell) - 1) & "$1"


                '.SeriesCollection(1).XValues = "=Sheet1!$F$1:$N$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

                excelSheet.Range("A2", strLastCell).NumberFormat = "0.0"

                MessageBox.Show("Chart Completed")

            End With

            'Set file format number to 56 if excel version <> 2007 
            'If Not filename.EndsWith(".xlsx") Then
            '    excelWorkbook.SaveAs(filename, 56)

            'Else
            '    excelWorkbook.SaveAs(filename)
            'End If

            MsgBox("File generated successfully at ") '& filename)

        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()

        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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33582887
Right, regarding the initial problem, trying to understand why setting the range value fails. I have a new install and added excel library as well as used excel namespace yet I can't use early binding. I must be missing something simple.
Anyways, I decided to knock up a test bed using late binding so here I am creating object.

I manually created a datatable and tried to dump that, set your rawdata, convert to dataset but failed to get it going.
So another option, not so elegant but works, is to iterate thru your datatable


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim excelApp As Object
        Dim excelWorkbook As Object
        Dim excelSheet As Object
        Dim excelRange As Object
        Dim sRange As String = "A1:B3"
        Dim xlDataTable As DataTable = Nothing
        Dim row1, row2, row3 As DataRow

        xlDataTable = New DataTable("ExampleExcelAuto")
        Dim fruit As DataColumn = New DataColumn("Fruit")
        fruit.DataType = System.Type.GetType("System.String")
        xlDataTable.Columns.Add(fruit)

        Dim col As DataColumn = New DataColumn("Colour")
        col.DataType = System.Type.GetType("System.String")
        xlDataTable.Columns.Add(col)

        row1 = xlDataTable.NewRow()
        row1.Item("Fruit") = "Apple"
        row1.Item("Colour") = "Red"
        xlDataTable.Rows.Add(row1)
        row2 = xlDataTable.NewRow()
        row2.Item("Fruit") = "Bananas"
        row2.Item("Colour") = "Yellow"
        xlDataTable.Rows.Add(row2)
        row3 = xlDataTable.NewRow()
        row3.Item("Fruit") = "Grapes"
        row3.Item("Colour") = "Black"
        xlDataTable.Rows.Add(row3)

        Dim ds As New DataSet()
        ds = New DataSet()
        'creating a dataset
        ds.Tables.Add(xlDataTable)

        'xlDataTable.Columns.Remove("MetricName")
        Dim rawData(xlDataTable.Rows.Count, xlDataTable.Columns.Count) As Object

        excelApp = CreateObject("Excel.Application")
        excelApp.visible = True
        excelWorkbook = excelApp.Workbooks.Add
        excelSheet = excelWorkbook.sheets(1)
        excelRange = excelSheet.range(sRange)

---> ITERATE THRU DT HERE
        Dim j As Integer = 1
        For Each dr As DataRow In xlDataTable.Rows
            j += 1
            For i As Integer = 1 To xlDataTable.Columns.Count
                ' Add the header the first time through
                If j = 2 Then
                    excelSheet.Cells(1, i) = xlDataTable.Columns(i - 1).ColumnName
                End If
                excelSheet.Cells(j, i) = dr.Item(i - 1).ToString
            Next
        Next
    End Sub



Here I create a datatable with 2 cols and 3 rows. I created the excel range A1:B3 which is now redundant cos I couldn't get it going.
So the last loop, I populate from A1 onwards. This needs to change according to your cell.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33582905
0
 

Author Closing Comment

by:chtullu135
ID: 33586102
Thanks for the help.  I've decided to give you full credit because you did answer my initial question.  I will be posting the latter part of the question as "How can I pass a worksheet to a function"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33586638
I wanted to solve your initial problem first and get feedback. I assume it worked.

Regarding passing worksheet name, well here are some suggestions, number 1 you can try later on as part of code cleanup if you wish

1. Don't keep creating excel app object as it will slow down your code. Create once and leave workbook open, either declare as global or pass as parameter into functions

2. Try passing down the worksheet by reference instead of value eg ByRef instead of ByVal.

What is the problem when you export?
0
 

Author Comment

by:chtullu135
ID: 33587007
I think the problem had to do with how I was passing the worksheet.  I'm going to have another fresh look at the code.  I worked on it last night and still had the same problem.  i.e. the error Exception from HRESULT: 0x800401A8"
After I clean up the code, I'll see what happens.   Oh I did try passing the worksheet by reference.  I have an idea of where the problem lies
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33587131
you could create class level variables so you don't need to keep passing them around if you are going to use them in more than one place within that class
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33587146
actually dont for worksheets because you keep creating new ones so maybe pass the worksheet name and reference the sheet by name there
0
 

Author Comment

by:chtullu135
ID: 33587397
I think I see one of my problems and it relates to what you mentioned earliers about opening and closing, then reopening the workbook.  In the finally clause of the try catch statement, Iof the OpenWorkbook function I saw that I was closing the workbook and quiting Excel.  I was then passing the resuliting worksheet by value to the next function.  Now the next function also has a excel quiting and clean up routine.  I'm thinking of commenting out the code in the finally clause of the OpenWorkbook function, this way the workbook will be open when the worksheet is passed to the next function
0
 

Author Comment

by:chtullu135
ID: 33587873
That was part of the problem, as well as how I was passing the worksheet.  I can now create multiple graphs however, they are being put into multiple workbooks instead of a single workbook.  I still need to fix this.  I'll post it as a new question along with the code.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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