Code partially running then running

[twoodsmall79]

I have been searching all over with no luck so far. From Access I have the below code that updates a couple fields and fixes a number stored as text issue from a report downloaded daily that is ultimately part of a linked table. When I run this code the first time I get no errors but only the Cell names get updated. The second (or third) time I run this code the number stored as text issues are corrected. Any ideas why this is happening and how to prevent?

Sub AccrualBalance_Update()


Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet


MySheetPath = "H:\Agreement Balances.xlsx"


Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)


Set XlSheet = XlBook.Worksheets(1)


XlSheet.Range("A1") = "Sales Organization"
XlSheet.Range("B1") = "SoldToNum"
XlSheet.Range("C1") = "Agreement Type"
XlSheet.Range("D1") = "AgreementNum"
XlSheet.Range("L1") = "EndBal"
With XlSheet.UsedRange
    .Value = .Value
End With


XlSheet.Range("A:B", "D:D").NumberFormat = "0"
   
    
XlBook.Windows(1).Visible = True
XlBook.Save
XlBook.Close
Xl.Quit


End Sub

 

[iliace]

Try putting the .Value = .Value after the NumberFormat = "0", rather than before.

 

[twoodsmall79]

Thanks for the tip. It worked like a charm. I just feel like a goof for not picking up on that.