Access VBA - Convert to Number

Thank you for looking at my question,

I have a button on an Access form that fires a macro to export a query to an Excel worksheet. I added the code below to go through the worksheet and convert the numbers stored as text to numbers. The first time I ran the thing it worked as I expected but, for no reason I can see, doesn't work now.

Can anybody suggest a foolproof, repeatable way of achieving the result.
    Dim objXLPurchase As Object
    Dim objBKPurchase As Object
    Dim objSHPurchase As Object
    
    ''Purchase Detail
    Set objXLPurchase = CreateObject("Excel.Application")
    Set objBKPurchase = objXLPurchase.Workbooks.Open("\\Production\Costings\Purchase_Detail.xlsx")
    Set objSHPurchase = objBKPurchase.Worksheets.Item("qryPurchase_Detail_Export")
    
    lngRow = 1
    lngNumRows = 0
    With objSHPurchase
        Do While .Cells(lngRow, 1) <> ""
            lngNumRows = lngNumRows + 1
            lngRow = lngRow + 1
        Loop
    End With
    
    strRange = "A1:P" & lngNumRows
    
    With objSHPurchase.Range(strRange)
        If IsNumeric(.Value) = True Then
            .Value = .Value * 1
        End If
    End With
    
    objXLPurchase.DisplayAlerts = False
    objBKPurchase.Save
    objXLPurchase.Quit
        
    Set objSHPurchase = Nothing
    Set objBKPurchase = Nothing
    Set objXLPurchase = Nothing

Open in new window


I have also tried using
    With objSHPurchase.Range(strRange)
        If IsNumeric(.Value) = True Then
            .NumberFormat="General"
            .Value = .Value * 1
        End If
    End With

Open in new window

CrxfrdAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Barry CunneyConnect With a Mentor Commented:
Hi Crxfrd
Please try the following:

With objSHPurchase.Range(strRange)
        If IsNumeric(.Value) = True Then
            .NumberFormat="Number"
            .Value = CInt(Val(.Value * 1))
        End If
    End With

Open in new window

0
 
Barry CunneyCommented:
Hi Crxfrd,
Please try the following:

 With objSHPurchase.Range(strRange)
        If IsNumeric(.Value) = True Then
            .NumberFormat="General"
            .Value = Val(.Value * 1)
        End If
    End With

Open in new window

0
 
CrxfrdAuthor Commented:
>Barry Cunney,

Hi Barry, sorry to say that didn't change anything - still got the little green flag in the top left corner of the cells concerned and the error message when you click into one of those cells.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Barry CunneyCommented:
Hi Crxfrd,
Please try the following:

With objSHPurchase.Range(strRange)
        If IsNumeric(.Value) = True Then
            .NumberFormat="General"
            .Value = CInt(Val(.Value * 1))
        End If
    End With

Open in new window

0
 
CrxfrdAuthor Commented:
Hi Barry,

Made the change and still no result.
0
 
CrxfrdAuthor Commented:
Barry, Thank you for your help
0
All Courses

From novice to tech pro — start learning today.