Solved

Slow Export

Posted on 2004-08-07
4
190 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 50 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

763 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