How can I use VBA to find a pattern such as ‘Checked Out To:’ and
everything between the quotes?

The Excel file will have 16 characters, 20 with the .xls extension.
Then there is a Return. Then ‘Checked Out To:’ with last name comma
first name and there MAY be some brackets after that. Everything will
ALWAYS be between quotes.

“9900010X10G00904.xls
Checked Out To: Jupree, Jasper [C-TO]”

“9900010X10G11208.xls
Checked Out To: Malfree, Jim”

I’m experimenting with a concept such as this:
If InStr(1, my_var, "Checked Out To: " & activeWB = "Checked Out To: "
& activeWB, vbTextCompare) 0 Then
‘Something . . . MsgBox ()
End If

There is a pattern, as described above, but I’m not sure how to parse
everything between the quotes.

Just so you know, all data comes from a URL; the my_var variable comes
from this URL . . . all of this works fine!
(Thanks Ron . . . )

Any ideas on how to handle this?

Thanks!!

On Aug 18, 2:28*pm, ryguy7272 wrote:
How can I use VBA to find a pattern such as ‘Checked Out To:’ and
everything between the quotes?

The Excel file will have 16 characters, 20 with the .xls extension.
Then there is a Return. *Then ‘Checked Out To:’ with last name comma
first name and there MAY be some brackets after that. *Everything will
ALWAYS be between quotes.

“9900010X10G00904.xls
Checked Out To: Jupree, Jasper [C-TO]”

“9900010X10G11208.xls
Checked Out To: Malfree, Jim”

I’m experimenting with a concept such as this:
If InStr(1, my_var, "Checked Out To: " & activeWB = "Checked Out To: "
& activeWB, vbTextCompare) 0 Then
‘Something . . . MsgBox ()
End If

There is a pattern, as described above, but I’m not sure how to parse
everything between the quotes.

Just so you know, all data comes from a URL; the my_var variable comes
from this URL . . . all of this works fine!
(Thanks Ron . . . )

Any ideas on how to handle this?

Thanks!!

How about something like...

pos_1 = instr(1, my_var, "Checked Out To", vbTextCompare)

Do until pos_1 = 0
pos_2 = instrrev(my_var, "xls", pos_1, vbTextCompare)
pos_3 = instr(pos_1, my_var, """", vbTextCompare)
my_text = mid(my_var, -17 + pos_2, pos_3 - (-17 + pos_2))
' might need a line like
' my_text=replace(my_text, chr(13),"")
' in order to remove line breaks
pos_1 = instr(pos_3, my_var, "Checked Out To", vbTextCompare)
Loop

....Ron

On Wed, 18 Aug 2010 13:28:40 -0700 (PDT), ryguy7272
wrote:

How can I use VBA to find a pattern such as ‘Checked Out To:’ and
everything between the quotes?

The Excel file will have 16 characters, 20 with the .xls extension.
Then there is a Return. Then ‘Checked Out To:’ with last name comma
first name and there MAY be some brackets after that. Everything will
ALWAYS be between quotes.

“9900010X10G00904.xls
Checked Out To: Jupree, Jasper [C-TO]”

“9900010X10G11208.xls
Checked Out To: Malfree, Jim”

I’m experimenting with a concept such as this:
If InStr(1, my_var, "Checked Out To: " & activeWB = "Checked Out To: "
& activeWB, vbTextCompare) 0 Then
‘Something . . . MsgBox ()
End If

There is a pattern, as described above, but I’m not sure how to parse
everything between the quotes.

Just so you know, all data comes from a URL; the my_var variable comes
from this URL . . . all of this works fine!
(Thanks Ron . . . )

Any ideas on how to handle this?

Thanks!!

It's not clear to me, from what you write, exactly what is in my_var,
and exactly what result you want returned. The examples you give make
it appear as if you just want to return my_var; probably without the
quotation marks, provided the sub-string Checked Out To: is present in
the string.

Is it really that simple?

If so, just use Instr to check for the presence of Checked Out To:
and, if present, return my_var (or mid(my_var,2, len(my_var)-2 if you
need to remove the quotes).

A more detailed description and examples of what you want would be
helpful.

On Aug 18, 9:06*pm, Ron Rosenfeld wrote:
On Wed, 18 Aug 2010 13:28:40 -0700 (PDT), ryguy7272





wrote:
How can I use VBA to find a pattern such as ‘Checked Out To:’ and
everything between the quotes?

The Excel file will have 16 characters, 20 with the .xls extension.
Then there is a Return. *Then ‘Checked Out To:’ with last name comma
first name and there MAY be some brackets after that. *Everything will
ALWAYS be between quotes.

“9900010X10G00904.xls
Checked Out To: Jupree, Jasper [C-TO]”

“9900010X10G11208.xls
Checked Out To: Malfree, Jim”

I’m experimenting with a concept such as this:
If InStr(1, my_var, "Checked Out To: " & activeWB = "Checked Out To: "
& activeWB, vbTextCompare) 0 Then
‘Something . . . MsgBox ()
End If

There is a pattern, as described above, but I’m not sure how to parse
everything between the quotes.

Just so you know, all data comes from a URL; the my_var variable comes
from this URL . . . all of this works fine!
(Thanks Ron . . . )

Any ideas on how to handle this?

Thanks!!

It's not clear to me, from what you write, exactly what is in my_var,
and exactly what result you want returned. *The examples you give make
it appear as if you just want to return my_var; probably without the
quotation marks, provided the sub-string Checked Out To: is present in
the string.

Is it really that simple?

If so, just use Instr to check for the presence of Checked Out To:
and, if present, return my_var (or mid(my_var,2, len(my_var)-2 if you
need to remove the quotes).

A more detailed description and examples of what you want would be
helpful.- Hide quoted text -

- Show quoted text -

Thanks guys!! I can't use a loop; it will be too slow. I was suing a
loop before and had to rethink this due to performance issues.


Here’s what I’m trying to do:
URL = "https:// . . . " < -- secure web site
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")

With IE
.navigate URL
.Visible = False
.Top = 50
.Left = 50
.Height = 50
.Width = 50
'Wait for page to load
While .Busy Or .readyState < 4 Or IE.Busy = True: Wend
Set HTMLdoc = .document
End With

Dim my_var As String
Dim my_file As String
Dim my_user As String
my_var = ""
my_var = IE.document.body.innerHTML

Now, my_var is a HUGE string. It is supposed to have everything from
the URL. If I go to View Source on my web browser, well, that’s the
‘my_var’

Your code gave the this:
‘javascript:return WebForm_OnSubmit();’

I can’t figure out why. It’s in the site, close to the top, but it
has nothing to do with what I’m looking for. I’m looking for
something like this:
8834310G10X09999.xls
Checked Out To: COLE, TIMMY

Basically, this is the text ‘Checked Out To:’ and the name of the
Excel file.

The name of the Excel file comes from he
activeWB = strFullString & ".xls"

I need to think about this a bit. If you come up with something else
in the meantime, please do post back.

Thanks!!

On Fri, 20 Aug 2010 12:44:26 -0700 (PDT), ryguy7272
wrote:

Now, my_var is a HUGE string. It is supposed to have everything from
the URL. If I go to View Source on my web browser, well, that’s the
‘my_var’

Post from a few lines before, through a few lines after, the segment
that you wish to extract.

On Aug 20, 4:20*pm, Ron Rosenfeld wrote:
On Fri, 20 Aug 2010 12:44:26 -0700 (PDT), ryguy7272

wrote:
Now, my_var is a HUGE string. *It is supposed to have everything from
the URL. *If I go to View Source on my web browser, well, that’s the
‘my_var’

Post from a few lines before, through a few lines after, the segment
that you wish to extract.

This is pretty much what I’m searching for:

Lines Befo
<tr class="ms-alternating"
<td class="ms-vb-icon"
<a tabIndex="-1" onclick="return
DispEx(this,event,'TRUE','FALSE','TRUE','SharePoin t.OpenDocuments.
3','0','SharePoint.OpenDocuments','','','11','11', '0','1','0x400001f07fff1bff')"
href="/sites/P/SharedDoc/8834310G10X09999.xls"
<img title="8834310G10X09999.xls
Checked Out To: COLE, TIMMY" alt="8834310G10X09999.xls
Checked Out To: COLE, TIMMY" src="/_layouts/images/icxls.gif"
border="0" complete="complete"/

Line After:
<td height="100%" class="ms-vb-title"

I want to extract this, and pop it into a MsgBox:
8834310G10X09999.xls
Checked Out To: COLE, TIMMY

There’s always a hard return after the .xls

One HUGE string should be in he
my_var = IE.document.body.innerHTML

Then, I want to look for the name of the Excel file and the name of
the person, in the 'my_var'

Does this make sense?

On Fri, 20 Aug 2010 13:32:29 -0700 (PDT), ryguy7272
wrote:

On Aug 20, 4:20*pm, Ron Rosenfeld wrote:
On Fri, 20 Aug 2010 12:44:26 -0700 (PDT), ryguy7272

wrote:
Now, my_var is a HUGE string. *It is supposed to have everything from
the URL. *If I go to View Source on my web browser, well, that’s the
‘my_var’

Post from a few lines before, through a few lines after, the segment
that you wish to extract.

This is pretty much what I’m searching for:

Lines Befo
<tr class="ms-alternating"
<td class="ms-vb-icon"
<a tabIndex="-1" onclick="return
DispEx(this,event,'TRUE','FALSE','TRUE','SharePoi nt.OpenDocuments.
3','0','SharePoint.OpenDocuments','','','11','11' ,'0','1','0x400001f07fff1bff')"
href="/sites/P/SharedDoc/8834310G10X09999.xls"
<img title="8834310G10X09999.xls
Checked Out To: COLE, TIMMY" alt="8834310G10X09999.xls
Checked Out To: COLE, TIMMY" src="/_layouts/images/icxls.gif"
border="0" complete="complete"/

Line After:
<td height="100%" class="ms-vb-title"

I want to extract this, and pop it into a MsgBox:
8834310G10X09999.xls
Checked Out To: COLE, TIMMY

There’s always a hard return after the .xls

One HUGE string should be in he
my_var = IE.document.body.innerHTML

Then, I want to look for the name of the Excel file and the name of
the person, in the 'my_var'

Does this make sense?

In your example, you have two (2) instances of Checked Out To preceded
by an excel file name that ends with a hard return.

The following code will return both of them:

====================================
Option Explicit
Sub GetCheckedOut()
Dim re As Object, mc As Object, m As Object
Dim my_var As String, s As String
Const sPat As String = _
"\b\w+\.xls[\r\n]Checked Out To:[\sA-Z,]+\b"

my_var = Range("$A$3").Text
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True
re.ignorecase = False

If re.test(my_var) = True Then
Set mc = re.Execute(my_var)
For Each m In mc
s = s & vbLf & vbLf & m
Next m
s = Mid(s, 3)
MsgBox (s)
End If
End Sub
====================================

On Fri, 20 Aug 2010 12:44:26 -0700 (PDT), ryguy7272
wrote:

I’m looking for
something like this:
8834310G10X09999.xls
Checked Out To: COLE, TIMMY

Basically, this is the text ‘Checked Out To:’ and the name of the
Excel file.

The name of the Excel file comes from he
activeWB = strFullString & ".xls"

I need to think about this a bit. If you come up with something else
in the meantime, please do post back.

Thanks!!

As I alluded to in my previous post, in order to locate specific data,
you need to be specific in your description; AND your description
needs to be unambiguous.

In the example you give, you show only two lines
Line 1 contains ONLY an excel file name with a .xls suffix
Line 2 starts with "Checked Out To:" and ends with a name that
is all in capitals, but may include spaces and a comma.

Is that how these entries will always appear within my_var? And will
they always be on two consecutive lines with nothing else on those
lines?

Will there always be just a single entry in my_var, or can there be
multiple entries and a need to extract all of them?

Here's a code snippet that might pull out what you need, if my
assumptions are valid, but will return NOTHING if they are not.

In this case, my_var is the contents of A3, but you can change that to
inner.text or something similar.

==============================
Option Explicit
Sub GetCheckedOut()
Dim re As Object, mc As Object, m As Object
Dim my_var As String
Const sPat As String = _
"\b\w+\.xls[\s\S]+?Checked Out To:[\sA-Z,]+\b"

my_var = Range("$A$3").Text
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True
re.ignorecase = False

If re.test(my_var) = True Then
Set mc = re.Execute(my_var)
For Each m In mc
Debug.Print m
Next m
End If
End Sub
=============================