Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Slow Export

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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 Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

765 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