Solved

How to pull current value out of a string

Posted on 2013-01-24
8
235 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
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 142

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction Authors who set out to write any sort of lengthy piece for online submission—be it a long question or comment on a technical form, an article, or a substantial blog entry—often find it useful to work up a draft in an editor other t…
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…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

776 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