Link to home
Start Free TrialLog in
Avatar of Stanley Lai
Stanley LaiFlag for Hong Kong

asked on

Would like to know how to copy a formatted number in Excel 2013 to a destination with truncated result?

Hi,

Would like to know how to copy a formatted number in Excel 2013 to a destination with truncated result?

My scenario is :
I have received an Excel worksheet daily.  The worksheet contains many formatted number data.  Yet, the format (especially the decimal points) of each number will be varying daily.  But the underlying data is fixed in length.  Due to the need to transfer the formatted data to PowerPoint slide and Word File, I need to write Excel VBA to do this.  However, I can only transfer the actual number but not the formatted number.  May I know how to transfer the formatted data to the PowerPoint slide  and Word File by using Excel VBA?

Example.
Today : The actual underlying raw data of the Excel cell B1 is : 1234.45678
Today : The formatted data as appeared in cell B1 : 1,234.457
Today : Actual raw data that need to transfer to PowerPoint Slide and Word document for cell B1 : 1234.457

Tomorrow : The actual underlying raw data of the Excel cell B1 is : 23,456.78912
Tomorrow : The formatted data as appeared in cell B1 : 23,456.8
Tomorrow : Actual raw data that need to transfer to PowerPoint Slide and Word document for cell B1 : 23456.8

May I know how to do this in Excel VBA?

Thanks.
Stanley
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

This works for me:
Sub Number()
    Dim sh As Worksheet
    Dim wdApp As Word.Application
    Dim wdDoc As Word.document
   
    Set wdApp = CreateObject("Word.application")
    wdApp.Visible = True
    Set wdDoc = wdApp.documents.Add
    Set sh = ActiveWorkbook.Sheets(1)
    wdDoc.Range.Text = sh.Cells(2, 2)
End Sub

Open in new window


It uses early binding, so needs to have a reference to Microsoft Word set.
Tomorrow : The actual underlying raw data of the Excel cell B1 is : 23,456.78912
I would expect it to be 23456.78912 as decimal values don't know about a thousand separator.
Avatar of Stanley Lai

ASKER

Even not considering  the thousand separator.  The problem of transferring truncated decimal value based on the formatted result is still a problem.
Or,put it in another way...

Under Excel, are there any functions that can check  the number of digits of a formatted cell?

For example, in cell A1

Actual valure ---> 12345.6789
Formatted value ---> 12345.68

Are there any functions (or VBA script) that will return 2 decimal places rather than 4 decimal place for cell A1?
It is not very clear what your problem is, and your sample data seems not consistent:
The actual underlying raw data of the Excel cell B1 is : 23,456.78912
If so, the data is text, not a number as numbers have no thousand separator.
the underlying data is fixed in length. 

But your sample data isn't. Or what does "length" mean?

And what does "today" and "tomorrow" mean, please?
Hi Gustav,

Sorry for describe the situation not that good.

In fact, I mentioned Today and Tomorrow image for the same worksheet and for the same cell B1 is to let you know that even for the same cell of the same sheet, the format will change daily.  For the first day, the same cell may be rounded to 3 decimal places (use cell format) while for another day, the same cell will round to 2 decimal places (use cell formatting), even the underlying value contains 4 decimal places.

For the example I mentioned, you are right, I wrongly put the underlying value with comma.  Actually, it does not contain any thousand separators.

The length I mentioned here is the decimal place length AFTER cell formatting.  For the decimal places of underlying value,it is fixed to 4 decimal places while the formatted value have varying length of decimal places length.

Wish this could explain better.

Stanley
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your prompt action.

Let me have a test and get back to you soon. ^^