Solved

How to pull current value out of a string

Posted on 2013-01-24
8
234 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

864 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

24 Experts available now in Live!

Get 1:1 Help Now