Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Slow Export

Posted on 2004-08-07
4
Medium Priority
?
202 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 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 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