Exporting a set of data to an Excel spreadsheet is a very common task. Here is an easy method for outputting any dataset with any number of tables to Excel.
Private Sub ExportGenericExcel(ByVal Title As String, ByVal Source As DataSet)
‘Should add reference in Visual Studio to Microsoft Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim r, s, t As Integer
‘Start a new workbook in Excel.
oExcel = CreateObject(“Excel.Application”)
oBook = oExcel.Workbooks.Add
Dim sar As DataSet
sar = Source
For s = 0 To sar.Tables.Count – 1
If s > 2 Then oBook.Worksheets.Add()
oSheet = oBook.Worksheets(s + 1)
oSheet.Name = Title & s.ToString
oSheet.Select()
‘Add Column Names as first row in bold
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(1, t + 1) = sar.Tables(s).Columns.Item(t).ColumnName
oSheet.cells(1, t + 1).font.bold = True
Next
For r = 0 To sar.Tables(s).Rows.Count – 1
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(r + 2, t + 1) = sar.Tables(s).Rows(r).Item(t)
If r = 0 And t = 1 Then
oSheet.cells(r + 2, t + 1).Select()
oExcel.ActiveWindow.FreezePanes = True
End If
Next
Next
Next
‘ Make sure Excel is visible and give the user control of Excel’s lifetime.
oExcel.Visible = True
oExcel.UserControl = True
‘ Make sure that you release object references.
oSheet = Nothing
oBook = Nothing
‘oExcel.Quit() Without this the Excel process will remain open as long as the calling program is running
oExcel = Nothing
End Sub
Privatetheehgjgjfdhj
Private Sub ExportGenericExcel(ByVal Title As String, ByVal Source As DataSet)
‘Should add reference in Visual Studio to Microsoft Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim r, s, t As Integer
‘Start a new workbook in Excel.
oExcel = CreateObject(“Excel.Application”)
oBook = oExcel.Workbooks.Add
Dim sar As DataSet
sar = Source
For s = 0 To sar.Tables.Count – 1
‘oBook.Worksheets.Add()
oSheet = oBook.Worksheets(s + 1)
oSheet.Name = Title & s.ToString
oSheet.Select()
‘MsgBox(oSheet.name)
‘Transfer the array to the worksheet starting at cell A2.
‘oSheet.Range(“A2”).Resize(100, 3).Value = DataArray
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(1, t + 1) = sar.Tables(s).Columns.Item(t).ColumnName
oSheet.cells(1, t + 1).font.bold = True
Next
For r = 0 To sar.Tables(s).Rows.Count – 1
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(r + 2, t + 1) = sar.Tables(s).Rows(r).Item(t)
If r = 0 And t = 1 Then
oSheet.cells(r + 2, t + 1).Select()
oExcel.ActiveWindow.FreezePanes = True
End If
Next
Next
‘oSheet.Cells(2, 2).Select()
‘oExcel.ActiveWindow.FreezePanes = True
Next
‘ Make sure Excel is visible and give the user control
‘ of Excel’s lifetime.
oExcel.Visible = True
oExcel.UserControl = True
‘ Make sure that you release object references.
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
End Sub
Sub ExportGenericExcel(ByVal Title As String, ByVal Source As DataSet)
‘Should add reference in Visual Studio to Microsoft Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim r, s, t As Integer
‘Start a new workbook in Excel.
oExcel = CreateObject(“Excel.Application”)
oBook = oExcel.Workbooks.Add
Dim sar As DataSet
sar = Source
For s = 0 To sar.Tables.Count – 1
‘oBook.Worksheets.Add()
oSheet = oBook.Worksheets(s + 1)
oSheet.Name = Title & s.ToString
oSheet.Select()
‘MsgBox(oSheet.name)
‘Transfer the array to the worksheet starting at cell A2.
‘oSheet.Range(“A2”).Resize(100, 3).Value = DataArray
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(1, t + 1) = sar.Tables(s).Columns.Item(t).ColumnName
oSheet.cells(1, t + 1).font.bold = True
Next
For r = 0 To sar.Tables(s).Rows.Count – 1
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(r + 2, t + 1) = sar.Tables(s).Rows(r).Item(t)
If r = 0 And t = 1 Then
oSheet.cells(r + 2, t + 1).Select()
oExcel.ActiveWindow.FreezePanes = True
End If
Next
Next
‘oSheet.Cells(2, 2).Select()
‘oExcel.ActiveWindow.FreezePanes = True
Next
‘ Make sure Excel is visible and give the user control
‘ of Excel’s lifetime.
oExcel.Visible = True
oExcel.UserControl = True
‘ Make sure that you release object references.
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
End Private Sub ExportGenericExcel(ByVal Title As String, ByVal Source As DataSet)
‘Should add reference in Visual Studio to Microsoft Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim r, s, t As Integer
‘Start a new workbook in Excel.
oExcel = CreateObject(“Excel.Application”)
oBook = oExcel.Workbooks.Add
Dim sar As DataSet
sar = Source
For s = 0 To sar.Tables.Count – 1
‘oBook.Worksheets.Add()
oSheet = oBook.Worksheets(s + 1)
oSheet.Name = Title & s.ToString
oSheet.Select()
‘MsgBox(oSheet.name)
‘Transfer the array to the worksheet starting at cell A2.
‘oSheet.Range(“A2”).Resize(100, 3).Value = DataArray
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(1, t + 1) = sar.Tables(s).Columns.Item(t).ColumnName
oSheet.cells(1, t + 1).font.bold = True
Next
For r = 0 To sar.Tables(s).Rows.Count – 1
For t = 0 To sar.Tables(s).Columns.Count – 1
oSheet.Cells(r + 2, t + 1) = sar.Tables(s).Rows(r).Item(t)
If r = 0 And t = 1 Then
oSheet.cells(r + 2, t + 1).Select()
oExcel.ActiveWindow.FreezePanes = True
End If
Next
Next
‘oSheet.Cells(2, 2).Select()
‘oExcel.ActiveWindow.FreezePanes = True
Next
‘ Make sure Excel is visible and give the user control
‘ of Excel’s lifetime.
oExcel.Visible = True
oExcel.UserControl = True
‘ Make sure that you release object references.
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
End Sub