daverichardson
asked on
Slow Export
I am using the following code to export my data to excel but the export is very slow does anyone know how I can speed it up?
Private Sub ExtractData()
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)
excelApp.Visible = False
With excelWorksheet
Dim ds As New DataSet("DataSetName")
Dim dr As DataRow
Dim myConnection As New OleDb.OleDbConnection("Pro vider=Micr osoft.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(("SELEC T TblOutstandingOrders.*, Complete.*, CompletePrint.*, TblDeliveryDates.*, Pulled.* FROM Pulled RIGHT JOIN (CompletePrint RIGHT JOIN (TblDeliveryDates RIGHT JOIN (Complete RIGHT JOIN TblOutstandingOrders ON Complete.CompleteSalesOrde r = TblOutstandingOrders.Sales Order) ON TblDeliveryDates.DeliveryD ateSalesOr der = TblOutstandingOrders.Sales Order) ON CompletePrint.CompleteSale sOrder = TblOutstandingOrders.Sales Order) ON Pulled.PulledSalesOrder = TblOutstandingOrders.Sales Order"), myConnection)
Dim i As Integer = 2
Try
myCommand.CommandType = CommandType.Text
myAdapter.SelectCommand = myCommand
myAdapter.SelectCommand.Co nnection = myConnection
myAdapter.Fill(ds)
Catch ex As Exception
End Try
.Range("A1").Value = " Account "
.Range("A1").Font.Bold = True
.Range("A1").ColumnWidth = 10
.Range("B1").Value = " Customer "
.Range("B1").Font.Bold = True
.Range("B1").ColumnWidth = 35.71
.Range("C1").Value = " Date "
.Range("C1").Font.Bold = True
.Range("C1").ColumnWidth = 9.43
.Range("D1").Value = " Description "
.Range("D1").Font.Bold = True
.Range("D1").ColumnWidth = 23
.Range("E1").Value = " Details "
.Range("E1").Font.Bold = True
.Range("E1").ColumnWidth = 23
.Range("F1").Value = " Due Date "
.Range("F1").Font.Bold = True
.Range("F1").ColumnWidth = 9.71
.Range("G1").Value = " EMB "
.Range("G1").Font.Bold = True
.Range("G1").ColumnWidth = 5.43
.Range("H1").Value = " Emb/Pr "
.Range("H1").Font.Bold = True
.Range("H1").ColumnWidth = 13.71
.Range("I1").Value = " PR "
.Range("I1").Font.Bold = True
.Range("I1").ColumnWidth = 4
.Range("J1").Value = " Sales Order "
.Range("J1").Font.Bold = True
.Range("J1").ColumnWidth = 12.14
.Range("K1").Value = " Special Date "
.Range("K1").Font.Bold = True
.Range("K1").ColumnWidth = 14.86
.Range("L1").Value = " Value "
.Range("L1").Font.Bold = True
.Range("L1").ColumnWidth = 7.29
.Range("M1").Value = " Delivery Date "
.Range("M1").Font.Bold = True
.Range("M1").ColumnWidth = 13.71
.Range("N1").Value = " Pulled "
.Range("N1").Font.Bold = True
.Range("N1").ColumnWidth = 7.29
.Range("O1").Value = " Complete Emb "
.Range("O1").Font.Bold = True
.Range("O1").ColumnWidth = 14.86
.Range("P1").Value = " Complete Print "
.Range("P1").Font.Bold = True
.Range("P1").ColumnWidth = 15.14
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")
.Range("M" & i.ToString).Value = dr("DeliveryDate")
.Range("N" & i.ToString).Value = dr("Pulled")
.Range("O" & i.ToString).Value = dr("Complete")
.Range("P" & i.ToString).Value = dr("CompletePrint")
i += 1
Next
Catch ex As Exception
End Try
excelApp.Visible = True
End With
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ExtractData()
End Sub
Private Sub ExtractData()
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets
excelApp.Visible = False
With excelWorksheet
Dim ds As New DataSet("DataSetName")
Dim dr As DataRow
Dim myConnection As New OleDb.OleDbConnection("Pro
Dim myAdapter As New OleDb.OleDbDataAdapter
Dim myCommand As New OleDb.OleDbCommand(("SELEC
Dim i As Integer = 2
Try
myCommand.CommandType = CommandType.Text
myAdapter.SelectCommand = myCommand
myAdapter.SelectCommand.Co
myAdapter.Fill(ds)
Catch ex As Exception
End Try
.Range("A1").Value = " Account "
.Range("A1").Font.Bold = True
.Range("A1").ColumnWidth = 10
.Range("B1").Value = " Customer "
.Range("B1").Font.Bold = True
.Range("B1").ColumnWidth = 35.71
.Range("C1").Value = " Date "
.Range("C1").Font.Bold = True
.Range("C1").ColumnWidth = 9.43
.Range("D1").Value = " Description "
.Range("D1").Font.Bold = True
.Range("D1").ColumnWidth = 23
.Range("E1").Value = " Details "
.Range("E1").Font.Bold = True
.Range("E1").ColumnWidth = 23
.Range("F1").Value = " Due Date "
.Range("F1").Font.Bold = True
.Range("F1").ColumnWidth = 9.71
.Range("G1").Value = " EMB "
.Range("G1").Font.Bold = True
.Range("G1").ColumnWidth = 5.43
.Range("H1").Value = " Emb/Pr "
.Range("H1").Font.Bold = True
.Range("H1").ColumnWidth = 13.71
.Range("I1").Value = " PR "
.Range("I1").Font.Bold = True
.Range("I1").ColumnWidth = 4
.Range("J1").Value = " Sales Order "
.Range("J1").Font.Bold = True
.Range("J1").ColumnWidth = 12.14
.Range("K1").Value = " Special Date "
.Range("K1").Font.Bold = True
.Range("K1").ColumnWidth = 14.86
.Range("L1").Value = " Value "
.Range("L1").Font.Bold = True
.Range("L1").ColumnWidth = 7.29
.Range("M1").Value = " Delivery Date "
.Range("M1").Font.Bold = True
.Range("M1").ColumnWidth = 13.71
.Range("N1").Value = " Pulled "
.Range("N1").Font.Bold = True
.Range("N1").ColumnWidth = 7.29
.Range("O1").Value = " Complete Emb "
.Range("O1").Font.Bold = True
.Range("O1").ColumnWidth = 14.86
.Range("P1").Value = " Complete Print "
.Range("P1").Font.Bold = True
.Range("P1").ColumnWidth = 15.14
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")
.Range("M" & i.ToString).Value = dr("DeliveryDate")
.Range("N" & i.ToString).Value = dr("Pulled")
.Range("O" & i.ToString).Value = dr("Complete")
.Range("P" & i.ToString).Value = dr("CompletePrint")
i += 1
Next
Catch ex As Exception
End Try
excelApp.Visible = True
End With
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ExtractData()
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
daverichardson, thank you for accepring my answer, could you please fill feedback?
I can provide more info if you need about CSV files.
Thanks,
Jigit
I can provide more info if you need about CSV files.
Thanks,
Jigit
ASKER
Dave