Solved

How to pull current value out of a string

Posted on 2013-01-24
8
239 Views
Last Modified: 2013-02-02
I have the following string:

200.00            01/18/2013      Smith, Joe      Chk Nbr 0

Repeated with different employees and amounts in a text file I am parsing (tab deliminted).  I can get the cursor to the start of the 200.00 but can not find a way once there to assign a currency variable the value, in this case $200.

I thought about going one character at a time in a loop until I reach the end of the 200.00 but am not able to stop at the tab successfully to obtain a selected.text .value correctly.

Any ideas or suggestions how to obtain the currency value from this line?
0
Comment
Question by:thandel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38817584
Can you supply a sample of the data file ... replace names or other specifics with gibberish, a few lines is all?

Do you want the edits into the original file?

Chris
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38817605
The tab character is  ascii #8, which may help you
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38818311
You mention 'cursor' and you have posted in the Word zone, so I guess that  you are doing this by opening the text file as a Word document.

This macro shows how to parse the document text.
Sub ParseTextDocument()
Dim rng As Range
Dim para As Paragraph
Dim strText() As String

Dim curAmount As Currency
Dim dtDate As Date
Dim strName As String
Dim strCheque As String
Dim strChNum As String

For Each para In ActiveDocument.Paragraphs
    Set rng = para.Range.Duplicate
    rng.MoveEnd wdCharacter, -1
    If Len(rng) > 0 Then
        strText = Split(rng.Text, vbTab)
        curAmount = CCur(strText(0))
        dtDate = CDate(strText(1))
        strName = strText(2)
        strCheque = strText(3)
        strChNum = Split(strCheque, " ")(2)
        Debug.Print curAmount, dtDate, strName, strChNum
    End If
Next para
End Sub

Open in new window

0
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 

Author Comment

by:thandel
ID: 38822015
Sorry jumping the gun, just found out that

        curAmount = CCur(strText(0))
        dtDate = CDate(strText(1))
        strName = strText(2)
        strCheque = strText(3)

are given an error of "type mismatch"
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38823585
You are really advised to help us help you.  Type conversion issues are because Graham assumed something about your data which seems to be wrong.  Like I asked earlier, if you provide s a sample of the data ... in html.text/doc or whatever is the real format we can understand and more importantly test our response.

Chris
0
 

Author Comment

by:thandel
ID: 38824988
Really no need, its just a txt file with various lines as sampled above.   Ex

200.00            01/18/2013      Smith, Joe      Chk Nbr 0
300.00            01/19/2013      Smith, Joe      Chk Nbr 1
400.00            01/20/2013      Smith, Joe      Chk Nbr 2
500.00            01/21/2013      Smith, Joe      Chk Nbr 3

Data changes but the context/format remains the same.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 38825739
I am assuming here that you are using VBS and if so save the following as a file with a .vbs suffix, modifying the line for the strFile definition to point to your file:

I have assumed the worst case which is a dollar symbol for currency.  If wrong then simply replace "$$" with for example "£".

Dim strFile
Dim FSO
Dim fil
Dim str
	
	strFile = "C:\Documents and Settings\<USER>\Desktop\VBS\add-dollar.txt"
	Set FSO = CreateObject("scripting.filesystemobject")
	If Not FSO.FileExists(strFile) Then
		Set FSO = Nothing
		MsgBox "Exiting as file NOT found."
		Wscript.Quit
	End If
	str = FSO.OpenTextFile(strFile,1,false,0).ReadAll
    With CreateObject("vbscript.regexp")
        .ignorecase = True
        .MultiLine = True
        .Global = True
        .Pattern = "([0-9]*\.[0-9]{0,2})(.*)"
        str = .replace(str, "$$" & "$1$2")
		Set fil = FSO.OpenTextFile(strFile, 2, True) 
        fil.Write str 
        fil.Close 
     End With

	Set FSO = Nothing

Open in new window


If using VBA within Word then much the same applies except wrap the code with a sub definition.

Chris
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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