Macro Returns Only 15 Digits

eriond · Aug 9th 2007

I am using Excel 2003. I am trying to importing a 20 character from a barcode scanner to cell K2 in an Excel spreadsheet. When the part is scanned, the program handling the scanner data shows the data as being 12345678901234567890 . I am running a macro that retrieves that data and places it in cell K2. The number that appears in cell K2 is 12345678901234500000 . The macro module code reads:

Code

Sub GetSWData()
    Dim WedgeData As Variant
    Dim MyPort As String
    Dim Chan As Long
    Dim StringVar As String
    Dim DataIn As Double
    MyPort = "COM1"
    Application.DisplayAlerts = False
    Chan = DDEInitiate("WinWedge", MyPort)
    WedgeData = DDERequest(Chan, "Field(1)")
    DataIn = WedgeData(1)
    Range("K2").Value = WedgeData(1)
    DDETerminate Chan
End Sub

Display More

I am guessing that one of the Dim statements has the wrong word after As but nothing I have tried gets me past this point. I am not above offering a bribe for an answer!

Richie(UK) · Aug 9th 2007

Re: Macro Module Only Returns 15 Of 20 Digits

Hi,

This sounds like a result of Excel's limitations. If you look in the Help files for Excel and look at Calculation Specifications the number precision is indicated as being 15 digits.

Not sure if this will work but have you tried importing the data as a string data type?

eriond · Aug 9th 2007

Re: Macro Module Only Returns 15 Of 20 Digits

Is the string class normally used to bring in text? I would like to have those 20 digits treated as text, if that is possible. When I declare certain variables as strings, the number that appears in K2 is in scientific notation form. When I try to change the cell format to text, the sample shown in the window is also in scientific notation. Good ol' Mr. Gates!

btadams · Aug 9th 2007

Re: Macro Module Only Returns 15 Of 20 Digits

try replacing this:

Code

Range("K2").Value = WedgeData(1)

with this:

Code

Range("K2").Value = Cstr(WedgeData(1))

Anonymous · Aug 9th 2007

btadams, Please use code tags for all VBA code.

**norie** · Aug 9th 2007

Re: Macro Module Only Returns 15 Of 20 Digits

Try formatting the destination cell as Text before you put anything in it.

RichardSchollar · Aug 9th 2007

Re: Macro Module Only Returns 15 Of 20 Digits

Hi

Does this amendment work? I believe this was what Richie was recommending:

Code

Sub GetSWData() 
    Dim WedgeData As Variant 
    Dim MyPort As String 
    Dim Chan As Long 
    Dim StringVar As String 'are you actually using this var?
    Dim DataIn As String
    MyPort = "COM1" 
    Application.DisplayAlerts = False 
    Chan = DDEInitiate("WinWedge", MyPort) 
    WedgeData = DDERequest(Chan, "Field(1)") 
    DataIn = WedgeData(1) 
    With Range("K2")
       .NumberFormat = "@"
       .Value = WedgeData(1) 
    End With
    DDETerminate Chan 
End Sub

Display More

Richard

eriond · Aug 11th 2007

Re: Macro Returns Only 15 Digits

Comrades,
Thanks to all of you who went to the effort to try and help me with this problem. The problem has been solved and I thought to let you know how we got around the problem. It appears that when Excel sees strings over 15 characters that consist of only numbers, it treats it as a number regardless of the variable type declaration. So, we placed an * into the 21st digit of the incoming data. This forced Excel to treat the incoming data as a string and the world was once again, a happy place. FYI, I was not the one who came up with the solution.
Thanks again for all the help!

Macro Returns Only 15 Digits

Participate now!

Share