Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

VB.Net - DataTable to Excel Running Slow

Good Day Experts!

I have a VB.Net project that queries a db and the resultset ends up in a DataTable.  Dumping it to Excel is really sluggish.  Can you give me some hints on how to increase the speed?

Thanks,
jimbo99999

'*****************************'
                'Process GetPaidFile DataTable'
                '*****************************'
                For Each row As DataRow In GetPaidFile.Rows
                    Application.DoEvents()   'Needed for Timer to flash Process label
                    ExcelRow = ExcelRow + 1
                    lblCounter.Text = ExcelRow
                    Dim ProNumber As String = ""
                    Dim ProCode As String = ""
                    Dim BillDate As Date
                    Dim AmountOfBill As Decimal = 0.0
                    Dim Reason1 As String = ""
                    Dim Reason2 As String = ""
                    Dim FromAmount As Decimal = 0.0
                    Dim InvoiceNumber As Integer = 0
                    Dim WeekReason2 As String = ""
                    Dim BilledWeight As Integer = 0
                    Dim PaidWeight As Integer = 0
                    Dim CustomerCode As String = ""
                    Dim BOLNumber As String = ""
                    Dim CustVendName As String = ""
                    Dim LocationNumber As String = ""
                    Dim DeptCode As String = ""
                    Dim FreightClass As String = ""
                    Dim InOut As String = ""
                    Dim FreightTerms As String = ""
                    Dim DiscAmount As Decimal = 0.0
                    Dim Mode As String = ""
                    Dim Mileage As String = ""
                    Dim CheckNumber As String = ""
                    Dim CheckDate As Date
                    Dim CarrierName As String = ""
                    Dim ThreeDigitZip As String = ""
                    Dim Fuel As Decimal = 0.0
                    Dim ThreePConsignee As String = ""
                    Dim ThreePZip As String = ""
                    Dim MiscCharge As Decimal = 0.0
                    Dim PTA As String = ""
                    Dim Corporation As String = ""
                    Dim Sector As String = ""
                    Dim BusinessUnit As String = ""
                    Dim SiteName As String = ""
                    Dim SiteAddress As String = ""
                    Dim SiteCity As String = ""
                    Dim SiteState As String = ""
                    Dim SiteZip As String = ""
                    Dim ReceiveDate As Date
                    Dim MisrouteRatedAmount As Decimal = 0.0
                    Dim MisrouteRoutedCarrier As String = ""
                    Dim ShipperName As String = ""
                    Dim ShipperAddress As String = ""
                    Dim ShipperCity As String = ""
                    Dim ShipperState As String = ""
                    Dim ShipperZip As String = ""
                    Dim ShipperCountry As String = ""
                    Dim ConsigneeName As String = ""
                    Dim ConsigneeAddress As String = ""
                    Dim ConsigneeCity As String = ""
                    Dim ConsigneeState As String = ""
                    Dim ConsigneeZip As String = ""
                    Dim ConsigneeCountry As String = ""
                    Dim GST As Decimal = 0.0
                    Dim DiscPercent As Decimal = 0.0
                    Dim Zone1 As String = ""
                    Dim TerritoryNumber As String = ""
                    Dim ProcessWeek As Date
                    Dim OrigProceWeek As Date
                    Dim KeyDate As Date
                    Dim Keyer As String = ""
                    '*********************'
                    'Check fields for Null'
                    '*********************'
                    If Not row.Item("PRO NUMBER") Is DBNull.Value Then
                        ProNumber = row.Item("PRO NUMBER")
                    End If
                    If Not row.Item("PRO CODE") Is DBNull.Value Then
                        ProCode = row.Item("PRO CODE")
                    End If
                    If Not row.Item("BILL DATE") Is DBNull.Value Then
                        BillDate = row.Item("BILL DATE")
                    End If
                    If Not row.Item("AMOUNT OF BILL") Is DBNull.Value Then
                        AmountOfBill = row.Item("AMOUNT OF BILL")
                    End If
                    If Not row.Item("REASON 1") Is DBNull.Value Then
                        Reason1 = row.Item("REASON 1")
                    End If
                    If Not row.Item("REASON 2") Is DBNull.Value Then
                        Reason2 = row.Item("REASON 2")
                    End If
                    If Not row.Item("FROM AMOUNT") Is DBNull.Value Then
                        FromAmount = row.Item("FROM AMOUNT")
                    End If
                    'If Not row.Item("") Is DBNull.Value Then
                    '    InvoiceNumber = row.Item("")
                    'End If
                    If Not row.Item("WEEK REASON 2") Is DBNull.Value Then
                        WeekReason2 = row.Item("WEEK REASON 2")
                    End If
                    If Not row.Item("BILLED WEIGHT") Is DBNull.Value Then
                        BilledWeight = row.Item("BILLED WEIGHT")
                    End If
                    If Not row.Item("PAID WEIGHT") Is DBNull.Value Then
                        PaidWeight = row.Item("PAID WEIGHT")
                    End If
                    If Not row.Item("CUSTOMER CODE") Is DBNull.Value Then
                        CustomerCode = row.Item("CUSTOMER CODE")
                    End If
                    If Not row.Item("BOL NUMBER") Is DBNull.Value Then
                        BOLNumber = row.Item("BOL NUMBER")
                    End If
                    If Not row.Item("CUSTOMER/VENDOR NAME") Is DBNull.Value Then
                        CustVendName = row.Item("CUSTOMER/VENDOR NAME")
                    End If
                    If Not row.Item("LOCATION NUMBER") Is DBNull.Value Then
                        LocationNumber = row.Item("LOCATION NUMBER")
                    End If
                    If Not row.Item("DEPTARTMENT CODE") Is DBNull.Value Then
                        DeptCode = row.Item("DEPTARTMENT CODE")
                    End If
                    If Not row.Item("FREIGHT CLASS") Is DBNull.Value Then
                        FreightClass = row.Item("FREIGHT CLASS")
                    End If
                    If Not row.Item("IN1/OUT2") Is DBNull.Value Then
                        InOut = row.Item("IN1/OUT2")
                    End If
                    If Not row.Item("FREIGHT TERMS") Is DBNull.Value Then
                        FreightTerms = row.Item("FREIGHT TERMS")
                    End If
                    If Not row.Item("DISCOUNT AMOUNT") Is DBNull.Value Then
                        DiscAmount = row.Item("DISCOUNT AMOUNT")
                    End If
                    If Not row.Item("TYPE DESIGNATOR") Is DBNull.Value Then
                        Mode = row.Item("TYPE DESIGNATOR")
                    End If
                    If Not row.Item("MILEAGE") Is DBNull.Value Then
                        Mileage = row.Item("MILEAGE")
                    End If
                    If Not row.Item("CHECK NUMBER") Is DBNull.Value Then
                        CheckNumber = row.Item("CHECK NUMBER")
                    End If
                    If Not row.Item("CHECK DATE") Is DBNull.Value Then
                        CheckDate = row.Item("CHECK DATE")
                    End If
                    If Not row.Item("CARRIER NAME") Is DBNull.Value Then
                        CarrierName = row.Item("CARRIER NAME")
                    End If
                    If Not row.Item("3 DIGIT ZIP") Is DBNull.Value Then
                        ThreeDigitZip = row.Item("3 DIGIT ZIP")
                    End If
                    If Not row.Item("FUEL") Is DBNull.Value Then
                        Fuel = row.Item("FUEL")
                    End If
                    If Not row.Item("3PCONSIGNEE") Is DBNull.Value Then
                        ThreePConsignee = row.Item("3PCONSIGNEE")
                    End If
                    If Not row.Item("3PZIP") Is DBNull.Value Then
                        ThreePZip = row.Item("3PZIP")
                    End If
                    If Not row.Item("MISC CHARGE") Is DBNull.Value Then
                        MiscCharge = row.Item("MISC CHARGE")
                    End If
                    If Not row.Item("AETC") Is DBNull.Value Then
                        PTA = row.Item("AETC")
                    End If
                    If Not row.Item("CORPORATION") Is DBNull.Value Then
                        Corporation = row.Item("CORPORATION")
                    End If
                    If Not row.Item("SECTOR") Is DBNull.Value Then
                        Sector = row.Item("SECTOR")
                    End If
                    If Not row.Item("BUSNIESS UNIT") Is DBNull.Value Then
                        BusinessUnit = row.Item("BUSNIESS UNIT")
                    End If
                    If Not row.Item("SITENAME") Is DBNull.Value Then
                        SiteName = row.Item("SITENAME")
                    End If
                    If Not row.Item("SITE ADDRESS") Is DBNull.Value Then
                        SiteAddress = row.Item("SITE ADDRESS")
                    End If
                    If Not row.Item("SITE CITY") Is DBNull.Value Then
                        SiteCity = row.Item("SITE CITY")
                    End If
                    If Not row.Item("SITE STATE") Is DBNull.Value Then
                        SiteState = row.Item("SITE STATE")
                    End If
                    If Not row.Item("SITE ZIP") Is DBNull.Value Then
                        SiteZip = row.Item("SITE ZIP")
                    End If
                    If Not row.Item("RECEIVE DATE") Is DBNull.Value Then
                        ReceiveDate = row.Item("RECEIVE DATE")
                    End If
                    If Not row.Item("MISROUTE RATED AMOUNT") Is DBNull.Value Then
                        MisrouteRatedAmount = row.Item("MISROUTE RATED AMOUNT")
                    End If
                    If Not row.Item("MISROUTE ROUTED CARRIER") Is DBNull.Value Then
                        MisrouteRoutedCarrier = row.Item("MISROUTE ROUTED CARRIER")
                    End If
                    If Not row.Item("SHIPPER NAME") Is DBNull.Value Then
                        ShipperName = row.Item("SHIPPER NAME")
                    End If
                    If Not row.Item("SHIPPER ADDRESS") Is DBNull.Value Then
                        ShipperAddress = row.Item("SHIPPER ADDRESS")
                    End If
                    If Not row.Item("SHIPPER CITY") Is DBNull.Value Then
                        ShipperCity = row.Item("SHIPPER CITY")
                    End If
                    If Not row.Item("SHIPPER STATE") Is DBNull.Value Then
                        ShipperState = row.Item("SHIPPER STATE")
                    End If
                    If Not row.Item("SHIPPER ZIP") Is DBNull.Value Then
                        ShipperZip = row.Item("SHIPPER ZIP")
                    End If
                    'If Not row.Item("SHIPPER ZIP") Is DBNull.Value Then
                    '    ShipperCountry = row.Item("SHIPPER ZIP")
                    'End If
                    If Not row.Item("CONSIGNEE NAME") Is DBNull.Value Then
                        ConsigneeName = row.Item("CONSIGNEE NAME")
                    End If
                    If Not row.Item("CONSIGNEE ADDRESS") Is DBNull.Value Then
                        ConsigneeAddress = row.Item("CONSIGNEE ADDRESS")
                    End If
                    If Not row.Item("CONSIGNEE CITY") Is DBNull.Value Then
                        ConsigneeCity = row.Item("CONSIGNEE CITY")
                    End If
                    If Not row.Item("CONSIGNEE STATE") Is DBNull.Value Then
                        ConsigneeState = row.Item("CONSIGNEE STATE")
                    End If
                    If Not row.Item("CONSIGNEE ZIP") Is DBNull.Value Then
                        ConsigneeZip = row.Item("CONSIGNEE ZIP")
                    End If
                    'If Not row.Item("CONSIGNEE ZIP") Is DBNull.Value Then
                    '    ConsigneeCountry = row.Item("CONSIGNEE ZIP")
                    'End If
                    If Not row.Item("GST") Is DBNull.Value Then
                        GST = row.Item("GST")
                    End If
                    If Not row.Item("DISCOUNT PERCENT") Is DBNull.Value Then
                        DiscPercent = row.Item("DISCOUNT PERCENT")
                    End If
                    If Not row.Item("ZONE") Is DBNull.Value Then
                        Zone1 = row.Item("ZONE")
                    End If
                    If Not row.Item("TERRITORY NUMBER") Is DBNull.Value Then
                        TerritoryNumber = row.Item("TERRITORY NUMBER")
                    End If
                    'If Not row.Item("TERRITORY NUMBER") Is DBNull.Value Then
                    '    ProcessWeek = row.Item("TERRITORY  NUMBER")
                    'End If
                    'If Not row.Item("TERRITORY NUMBER") Is DBNull.Value Then
                    '    OrigProcessWeek = row.Item("TERRITORY  NUMBER")
                    'End If
                    'If Not row.Item("TERRITORY NUMBER") Is DBNull.Value Then
                    '    KeyDate = row.Item("TERRITORY  NUMBER")
                    'End If
                    'If Not row.Item("TERRITORY NUMBER") Is DBNull.Value Then
                    '    Keyer = row.Item("TERRITORY  NUMBER")
                    'End If
                    '*********************************************'
                    'Add data to CheckDate and CheckAmount columns'
                    '*********************************************'
                    With oSheet.Range("A" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ProNumber
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("B" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ProCode
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("C" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "@"
                        .Value = BillDate
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("D" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = AmountOfBill
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("E" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Reason1
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("F" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Reason2
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("G" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = FromAmount
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    'With oSheet.Range("H" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Value = InvoiceNumber
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    With oSheet.Range("I" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = WeekReason2
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("J" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = BilledWeight
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("K" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = PaidWeight
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("L" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = CustomerCode
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("M" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = BOLNumber
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("N" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = CustVendName
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("O" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = LocationNumber
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("P" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = DeptCode
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("Q" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = FreightClass
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("R" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = InOut
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("S" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = FreightTerms
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("T" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = DiscAmount
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("U" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Mode
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("V" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Mileage
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("W" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = CheckNumber
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("X" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "@"
                        .Value = CheckDate
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("Y" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = CarrierName
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("Z" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ThreeDigitZip
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AA" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = Fuel
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AB" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ThreePConsignee
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AC" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ThreePZip
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AD" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = MiscCharge
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AE" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = PTA
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AF" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Corporation
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AG" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Sector
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AH" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = BusinessUnit
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AI" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = SiteName
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AJ" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = SiteAddress
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AK" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = SiteCity
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AL" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = SiteState
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AM" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = SiteZip
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AN" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "@"
                        .Value = ReceiveDate
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AO" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = MisrouteRatedAmount
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AP" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = MisrouteRoutedCarrier
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AQ" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ShipperName
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AR" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ShipperAddress
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AS" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ShipperCity
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AT" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ShipperState
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AU" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ShipperZip
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    'With oSheet.Range("AV" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Value = ShipperCountry
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    With oSheet.Range("AW" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ConsigneeName
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AX" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ConsigneeAddress
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AY" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ConsigneeCity
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("AZ" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ConsigneeState
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("BA" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = ConsigneeZip
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    'With oSheet.Range("BB" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Font.Color = 255
                    '    .Value = ConsigneeCountry
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    With oSheet.Range("BC" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = GST
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("BD" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .NumberFormat = "$###,###,##0.00"
                        .Value = DiscPercent
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("BE" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Zone1
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    With oSheet.Range("BF" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = TerritoryNumber
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    'With oSheet.Range("BG" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Value = ProcessWeek
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    'With oSheet.Range("BH" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Value = OrigProcessWeek
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    'With oSheet.Range("BI" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Value = KeyDate
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    'With oSheet.Range("BJ" + CStr(ExcelRow))
                    '    .Font.Size = 10
                    '    .Font.Name = "Segoe UI"
                    '    .Value = Keyer
                    '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    'End With
                    oSheet.Columns.AutoFit()
                Next row

Open in new window

0
Jimbo99999
Asked:
Jimbo99999
  • 2
  • 2
1 Solution
 
CodeCruiserCommented:
How many rows involved? Given what you are doing in that loop, I am not surprised. Is formatting really necessary? Does it improve it if you comment out formatting code?
0
 
Jimbo99999Author Commented:
I am just doing 1 month 640 records and it is brutal.  Total records in the end is 188,723.
I will try without formatting code.
0
 
Jimbo99999Author Commented:
It was faster without the formatting code.  However,  the speed it still slow.  But your suggestion made me think that cell be cell was slow.  So, I defined an array to put a row of data in.  Then I assign the array to the range of cells(row).  What a difference, the speed is really fast now.  Thanks for the idea.

jimbo99999
0
 
CodeCruiserCommented:
Glad that I could assist :-)
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!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now