Solved

Access VBA - Convert to Number

Posted on 2016-09-29
6
40 Views
Last Modified: 2016-10-05
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

0
Comment
Question by:Crxfrd
  • 3
  • 3
6 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
 

Author Comment

by:Crxfrd
Comment Utility
>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
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Crxfrd
Comment Utility
Hi Barry,

Made the change and still no result.
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Crxfrd
Comment Utility
Barry, Thank you for your help
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now