Solved

Slow Export

Posted on 2004-08-07
4
193 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
[X]
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
  • 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

Independent Software Vendors: 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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

734 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