Solved

How to pull current value out of a string

Posted on 2013-01-24
8
232 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
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 …

707 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

18 Experts available now in Live!

Get 1:1 Help Now