Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to pull current value out of a string

Posted on 2013-01-24
8
Medium Priority
?
240 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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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…
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 video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Suggested Courses

715 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