• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Need a primer on how to process strings in VBA

Hi experts,
I guess there must be numerous ways to compare strings,
extract pieces from strings, find substrings in larger strings
and much more.
I would like to understand some of the commonly used methods
in string processing.

That said, let's look at some real data:
A string coud be:
<a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>
One nice thing would be ways of how to find this string on a webpage (also present As String)
Then a way to extract the number 162259
And last but not least to get rid of unneccessary whitespaces.

Thanks,
Jens
0
allmer
Asked:
allmer
  • 9
  • 7
  • 4
  • +2
4 Solutions
 
flavoCommented:
Nice functions

Instr - find an occurance of a string inside a string { Instr(1,"myString","S") = 3}
Left - get the chars from the left of a string  { Left("myString",3) = "myS" }
Right - get the chars from the right of a string  { Reight("myString",3) = "ing" }
Mid - get a string from the middle of a string { Mid("myString", 2,1) = "y" - Mid(string,start from the left, number of chars)
Trim - trim off spaces

The VBA help will help you more.... Just type in the function name to Mr Clippy

So in your case you could use

Dim s as string
Dim sRetrun as string
s = <a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>
sReturn = Trim(Mid(s,Instr(1,s, ">"), Instr(3,s, "<") - Instr(1,s, ">")))

Dave
0
 
flavoCommented:
>> s = <a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>

s = "<a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>"
0
 
Steve BinkCommented:
Your best source of information is going to be the VBA help files that come with Access.  Look in the Object Browser for the module called Strings.  It includes all the functions you require.  Here's a few examples:

Left(<string>, <number of characters>) = returns the left-most # of characters
Right(<string>, <number of characters>) = returns the right-most # of characters
Mid(<string>, <start>, <length>) = returns a portion of the string <length> characters long, starting from position #<start>
Trim(<string>) = removes leading and trailing spaces
InStr(<start>, <string>, <search for>, <compare option>) = finds <search for> in <string> and returns the starting character position
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mike EghtebasDatabase and Application DeveloperCommented:
re:>get rid of unneccessary whitespaces

Dim strVar As String
strVar="<a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>"
Replace(strVar," ","")

To use this in a query, you my need a function call.  Will hel if there was question about it.

re:> find substrings in larger strings ... extract pieces from strings

Dim strVar As String
Dim strFind As String
Dim i As Integer
strVar="<a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>"
strFind ="162"
i= InStr(strVar,strFind)      '<-- i will be starting characted in the string
MsgBox Mid(String,i,5)      '<-- will extract 162259 knowing it is always 5 char long.

mike
0
 
allmerAuthor Commented:
Thanks alot
So I tried the following and it fails telling me:
illegal function or procedure call.
       Set h = IE.Document
        Dim id As String
        Dim l As Integer
        Dim r As Integer
        Dim le As Integer
        l = InStr(1, h.body.innerHTML, "dispGeneModel?db=chlre2&id=")
        r = InStr(l, h.body.innerHTML, ">")
        le = r - l
        id = Mid(h.body.innerHTML, l, le)
        MsgBox id
Something goes wrong as soon as I call the msgbox.
This is within a function, but when I return id and try
to display it within a sub it still gives an error.
Any ideas?
Jens
0
 
Steve BinkCommented:
Have you traced through it line by line to see which value is coming up NULL?
0
 
allmerAuthor Commented:
Sorry,
dont know how to do that (tracing I mean).
BTW my VB Help file is corrupt so I cannot
read that either :(
0
 
Steve BinkCommented:
LOL!  By the VB help file, I mean go to the code window, and press F2 to bring up the object browser.  In the left pane of the object list, find the module titled "Strings".  Select any function within that module and press F1.  Do you get an explanation of the function?

To trace, first you need to set a breakpoint.  Go to the code and put the cursor on this line:

Set h = IE.Document

Press F9.  You'll notice the line changes.  That sets a breakpoint, or a point at which code execution stops (breaks).  Now run it.  When it gets to that line of code, it will stop and wait for you.  Press F8 once to execute the current line.  Continue pressing F8 each time, examining the values of the variables as you go.
0
 
allmerAuthor Commented:
Ok
I found how to break and step through the code.

What happend is that the string is not found:
l = InStr(1, h.body.innerHTML, "dispGeneModel?db=chlre2&id=")
l = 0
And then it crashes.
Are there any chars above that I have to escape?
0
 
Steve BinkCommented:
No.  That means the snippet of text was not found.  Try examining the h.body.innerHTML property to see if that snippet actually exists.
0
 
Jokra_the_BarbarianCommented:
It depend on what you are trying to parse and if it is consistent in its formatting. Try this:

Private Sub Command2_Click()

Dim str As String    ' old string
Dim newstr As String ' just the id number
Dim spos As Integer  ' start position
Dim epos As Integer  ' end position

str = "<a href=/cgi-bin/dispGeneModel?db=chlre2&id=162259>162259 </a>"
spos = InStr(1, str, ">")
epos = InStr(spos, str, "<")

newstr = Mid(str, spos + 1, epos - spos - 1)
Debug.Print Trim(newstr)
Debug.Print Mid(str, 1, spos) & Trim(newstr) & Mid(str, epos, (Len(str) - epos) + 1)

End Sub
0
 
allmerAuthor Commented:
Hi, almost there.
The snippet did exist. But it still failed to search so I searched for
"dispGeneModel" first and then for "id=".
It's not beautiful but it works.

Which now leaves the "trim" problem open.
Assume a string similar to the below (html).
<pre>>C_290043 [chlre2:162259]
MPPRRLAAITAAAAAEAGCGCSSAGSTGSSALHDADFSSLPSCSTCGSCSCSSSGSCGGGSGSSSGRGSA
YMSFCLGLVLGTLYEAVLGLDLPPAIVAYHARLPDVIMGEAVVLWLTMVVMPPTAFLMAAVRIPAHFAMWSD
RTVGTHASAARAAADALLLAAISVAVNAAMHVVLYAQYRRRMRRQLRGQGGRAAAAAGVAARAREELHGL
EQQGPAGSGAAASPVREKTE*
</pre>
I extract in between "]" and "*"
Now I need to get rid of the whitespaces (linefeeds, spaces, ..) everywhere in the string
Trim didn't seem to do it right.

After that I want to update a field in a table with that information.
The field is set to Memo (Can it hold more than 2000 characters?).
The string as it is now seems to be correctly transferred to the cell, but
doesn't show up nicely because of the whitespaces.
Best,
Jens
0
 
flavoCommented:
Trim(Mid(string, Instr(1,string,"]"), Instr(1,string,"*") - Instr(1,string,"]"))

Or if its got a retrun in there not a space

Replace(Replace(Trim(Mid(string, Instr(1,string,"]"), Instr(1,string,"*") - Instr(1,string,"]")), chr(13), "", chr(11), "")

Yes, a memo field can hold 64k chars (give or take a few)

Dave
0
 
flavoCommented:
>> everywhere in the string


woops!

Replace(Replace(Replace(Mid(string, Instr(1,string,"]"), Instr(1,string,"*") - Instr(1,string,"]")), chr(13), "", chr(11), ""), " ", "")

Dave
0
 
allmerAuthor Commented:
Thanks,
I am not a friend of cryptic expression so I rewrote the above to:

        Dim seq As String
        seq = Mid(h.body.innerText, l, le)
        seq = Trim(seq)
        seq = RTrim(seq)
        seq = LTrim(seq)
        seq = Replace(seq, Chr(13), "")
        seq = Replace(seq, Chr(11), "")
        seq = Replace(seq, " ", "")
        GetGeneModelSeq = seq

I guess I am missing some whitespaces still (tabs, cr, lf)
because the content of the field now shows up as: SDFS...SDFS     ASDE....WERWE     EREW....FAGA
Note: there are still whitespaces inbetween the lines extracted from the webpage (no <BR>s, though).
Any ideas?
Jens
0
 
flavoCommented:
Trim is a combo of Ltrim and Rtrim, so you only need Trim()

The Chr(13) and chr(11) are the soft and hard return chars, so it should get rid of all the returns

maybe there are tabs??

try Relace(seq,vbtab,"")

If notcan you so us the html (post a link if you can) and i can interigate it more....


Dave
0
 
flavoCommented:
sorry..

it should be chr(10) and chr(13)
0
 
allmerAuthor Commented:
Where can I find a list of the character codes?
Thanks
Jens
0
 
flavoCommented:
I just learn them (half) off by heart..

a simple sub like this will show you

Dim i as long
For i = 1 to 255
 debug.print i & " = " & chr(i)
next


you can also go backwards like this {example using ")

?asc("""")
returns 34

Dave
0
 
flavoCommented:
this however wont show numbers 0 – 31 (these are nonprintable ASCII codes - will show as a box)
0
 
allmerAuthor Commented:
Thanks again!
I used a hammer to fix the problem:
Public Function RemoveWhitespace(str As String) As String
    str = Trim(str)
    For I = 0 To 64
        str = Replace(str, Chr(I), "")
    Next I
    RemoveWhitespace = str
End Function
Removes all characters up to 'A' since I will only expect Alphas
that is fine for me.
Thanks again.
I guess I will have to split points here.
Jens
0
 
flavoCommented:
glad we finnaly got there!

Now you can equip yourself with the power of the string functions!

Good luck with the rest of your project

Dave
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 9
  • 7
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now