Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Slow Export

Posted on 2004-08-07
4
Medium Priority
?
208 Views
Last Modified: 2010-05-18
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("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.*, Complete.*, CompletePrint.*, TblDeliveryDates.*, Pulled.* FROM Pulled RIGHT JOIN (CompletePrint RIGHT JOIN (TblDeliveryDates RIGHT JOIN (Complete RIGHT JOIN TblOutstandingOrders ON Complete.CompleteSalesOrder = TblOutstandingOrders.SalesOrder) ON TblDeliveryDates.DeliveryDateSalesOrder = TblOutstandingOrders.SalesOrder) ON CompletePrint.CompleteSalesOrder = TblOutstandingOrders.SalesOrder) ON Pulled.PulledSalesOrder = TblOutstandingOrders.SalesOrder"), myConnection)
                Dim i As Integer = 2

                Try
                    myCommand.CommandType = CommandType.Text
                    myAdapter.SelectCommand = myCommand
                    myAdapter.SelectCommand.Connection = 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
0
Comment
Question by:daverichardson
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
Jigit earned 200 total points
ID: 11745748
Save the data into CSV file (Comma Separated), then open it with Excel, apply style as you wish and save as XLS file.

HTH,
Jigit
0
 

Author Comment

by:daverichardson
ID: 11745860
Could you point me in the right direction on how to do this

Dave
0
 
LVL 4

Expert Comment

by:Jigit
ID: 12034108
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Integration Management Part 2
Loops Section Overview

886 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