What is System.Reflection.Missing.Value
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.
I've been trying to find out what System.Reflection.Missing.
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.
I've been trying to find out what System.Reflection.Missing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In this line
excelWorkbook = excelApp.Workbooks.Add(mis
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(excelRang
the 2nd argument is if you want to specify another cell.
excelWorkbook = excelApp.Workbooks.Add(mis
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(excelRang
the 2nd argument is if you want to specify another cell.
ASKER
I've tried as you suggested and received the following erro at line
excelSheet.Range(excelRang
excelSheet.Range(excelRang
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: ASKER
Basically, What I am trying to do is pass a worksheet to the subroutine ExportMultipleGraphToExcel
brb, getting visual studio 2010 installed. I will double check it then
ASKER
Thanks
ASKER
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()
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()
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
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("ExampleExcelAut
Dim fruit As DataColumn = New DataColumn("Fruit")
fruit.DataType = System.Type.GetType("Syste
xlDataTable.Columns.Add(fr
Dim col As DataColumn = New DataColumn("Colour")
col.DataType = System.Type.GetType("Syste
xlDataTable.Columns.Add(co
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.Remov
Dim rawData(xlDataTable.Rows.C
excelApp = CreateObject("Excel.Applic
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.
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("ExampleExcelAut
Dim fruit As DataColumn = New DataColumn("Fruit")
fruit.DataType = System.Type.GetType("Syste
xlDataTable.Columns.Add(fr
Dim col As DataColumn = New DataColumn("Colour")
col.DataType = System.Type.GetType("Syste
xlDataTable.Columns.Add(co
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.Remov
Dim rawData(xlDataTable.Rows.C
excelApp = CreateObject("Excel.Applic
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.
have a look here at the mvps website http://msmvps.com/blogs/deborahk/archive/2009/07/23/writing-data-from-a-datatable-to-excel.aspx
ASKER
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"
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?
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?
ASKER
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
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
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
actually dont for worksheets because you keep creating new ones so maybe pass the worksheet name and reference the sheet by name there
ASKER
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
ASKER
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.
ASKER
excelSheet.Range(excelRang
Open in new window