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
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 rowASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
jimbo99999
Glad that I could assist :-)
ASKER
I will try without formatting code.