asked on

Export to Excel

Is it possible to export dataset or data adapter records to excel

I need to export the results of my Datagrid, I have tried it with a report without much success

Dave

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ptakja

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

daverichardson

ASKER

I have adapted the following code, it adds the column headers but doesn’t populate the data any ideas

Private Sub ExtractData()

'Initializes Excel and creates a new workbook/worksheet
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets(1), Excel.Worksheet)
'Makes Excel invisible to the user until spreadsheet is populated
excelApp.Visible = False
With excelWorksheet
'Creates connections to pull data to DataSet
Dim dr As DataRow
Me.OleDbConnection1.Open()
Dim myAdapter As New OleDb.OleDbDataAdapter
Dim myCommand As New OleDb.OleDbCommand(("SELECT Account, Customer, [Date], Description, Details, [Due Date], EMB, [Emb/Pr], PR, SalesOrder, [Special Date], Type, ['Value] FROM TblOutstandingOrders"), OleDbConnection1)
Dim i As Integer = 2
'Bind myAdapter to myCommand
Try
myCommand.CommandType = CommandType.Text
myAdapter.SelectCommand = myCommand
myAdapter.SelectCommand.Connection = OleDbConnection1
myAdapter.Fill(DsAll)

Catch ex As Exception

End Try

'Format cell headings
.Range("A1").Value = " Account "
.Range("A1").Font.Bold = True
.Range("A1").ColumnWidth = 15
.Range("B1").Value = " Customer "
.Range("B1").Font.Bold = True
.Range("B1").ColumnWidth = 15
.Range("C1").Value = " Date "
.Range("C1").Font.Bold = True
.Range("C1").ColumnWidth = 15
.Range("D1").Value = " Description "
.Range("D1").Font.Bold = True
.Range("D1").ColumnWidth = 15
.Range("E1").Value = " Details "
.Range("E1").Font.Bold = True
.Range("E1").ColumnWidth = 15
.Range("F1").Value = " Due Date "
.Range("F1").Font.Bold = True
.Range("F1").ColumnWidth = 15
.Range("H1").Value = " EMB "
.Range("H1").Font.Bold = True
.Range("H1").ColumnWidth = 15
.Range("G1").Value = " Emb/Pr "
.Range("G1").Font.Bold = True
.Range("G1").ColumnWidth = 15
.Range("H1").Value = " PR "
.Range("H1").Font.Bold = True
.Range("H1").ColumnWidth = 15
.Range("I1").Value = " Sales Order "
.Range("I1").Font.Bold = True
.Range("I1").ColumnWidth = 15
.Range("J1").Value = " Special Date "
.Range("J1").Font.Bold = True
.Range("J1").ColumnWidth = 15
.Range("K1").Value = " Value "
.Range("K1").Font.Bold = True
.Range("K1").ColumnWidth = 15

'Populate Excel spreadsheet
Try
For Each dr In DsAll.Tables(0).Rows
.Range("A").Value = dr("Account ")
.Range("B").Value = dr("Customer ")
.Range("C").Value = dr("Date")
.Range("D").Value = dr("Description")
.Range("E").Value = dr("Details ")
.Range("F").Value = dr("Due Date ")
.Range("G").Value = dr("EMB ")
.Range("H").Value = dr("Emb/Pr ")
.Range("I").Value = dr("PR ")
.Range("J").Value = dr("SalesOrder ")
.Range("K").Value = dr("Special Date ")
.Range("L").Value = dr("Value ")

i += 1
Next
Catch ex As Exception

End Try
'Make Excel visible
excelApp.Visible = True
End With

daverichardson

ASKER

Sorted it

Private Sub ExtractData()

'Initializes Excel and creates a new workbook/worksheet
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets(1), Excel.Worksheet)
'Makes Excel invisible to the user until spreadsheet is populated
excelApp.Visible = False
With excelWorksheet
'Creates connections to pull data to DataSet
Dim ds As New DataSet("DataSetName")
Dim dr As DataRow
Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data.mdb;Persist Security Info=False")
Dim myAdapter As New OleDb.OleDbDataAdapter
Dim myCommand As New OleDb.OleDbCommand(("SELECT TblOutstandingOrders.* FROM TblOutstandingOrders"), myConnection)
Dim i As Integer = 2
'Bind myAdapter to myCommand
Try
myCommand.CommandType = CommandType.Text
myAdapter.SelectCommand = myCommand
myAdapter.SelectCommand.Connection = myConnection
myAdapter.Fill(ds)
Catch ex As Exception

End Try
'Format cell headings
.Range("A1").Value = " Account "
.Range("A1").Font.Bold = True
.Range("A1").ColumnWidth = 15
.Range("B1").Value = " Customer "
.Range("B1").Font.Bold = True
.Range("B1").ColumnWidth = 15
.Range("C1").Value = " Date "
.Range("C1").Font.Bold = True
.Range("C1").ColumnWidth = 15
.Range("D1").Value = " Description "
.Range("D1").Font.Bold = True
.Range("D1").ColumnWidth = 15
.Range("E1").Value = " Details "
.Range("E1").Font.Bold = True
.Range("E1").ColumnWidth = 15
.Range("F1").Value = " Due Date "
.Range("F1").Font.Bold = True
.Range("F1").ColumnWidth = 15
.Range("G1").Value = " EMB "
.Range("G1").Font.Bold = True
.Range("G1").ColumnWidth = 15
.Range("H1").Value = " Emb/Pr "
.Range("H1").Font.Bold = True
.Range("H1").ColumnWidth = 15
.Range("I1").Value = " PR "
.Range("I1").Font.Bold = True
.Range("I1").ColumnWidth = 15
.Range("J1").Value = " Sales Order "
.Range("J1").Font.Bold = True
.Range("J1").ColumnWidth = 15
.Range("K1").Value = " Special Date "
.Range("K1").Font.Bold = True
.Range("K1").ColumnWidth = 15
.Range("L1").Value = " Value "
.Range("L1").Font.Bold = True
.Range("L1").ColumnWidth = 15

'Populate Excel spreadsheet
Try
For Each dr In ds.Tables(0).Rows
.Range("A" & i.ToString).Value = dr("Account")
.Range("B" & i.ToString).Value = dr("Customer")
.Range("C" & i.ToString).Value = dr("Date")
.Range("D" & i.ToString).Value = dr("Description")
.Range("E" & i.ToString).Value = dr("Details")
.Range("F" & i.ToString).Value = dr("Due Date")
.Range("G" & i.ToString).Value = dr("EMB")
.Range("H" & i.ToString).Value = dr("Emb/Pr")
.Range("I" & i.ToString).Value = dr("PR")
.Range("J" & i.ToString).Value = dr("SalesOrder")
.Range("K" & i.ToString).Value = dr("Special Date")
.Range("L" & i.ToString).Value = dr("'Value")

i += 1
Next
Catch ex As Exception

End Try
'Make Excel visible
excelApp.Visible = True
End With
Thanks for the help

kaifong78

How about export the data from Excel to DataGird?

Can sent me a code or sample?

Thanks