How do I get something like IE > View > Source . . . Navigate therewith VBA

R

ryguy7272

Hello friends! I have a SharePoint URL, which I define as this:

URL = https://collaboration.co.net/sites/US/Shared Documents/

Here is my VBA:

setrestart:
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")

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

Application.StatusBar = "Processing your Request. Please wait..."
xlFile = strpath & "/" & strFullString & ".xls" '& ActiveWorkbook.Name
activeWB = strFullString & ".xls"

Now . . . I’m trying to see if files are checked out from the
SharePoint site, so I’m thinking of using a line of VBA, as such

If InStr(1, URL, "Checked Out To:") > 1 Then
‘ . . . do something
End If

Of, course, the URL is just the string from above. What I really want
to do is something like go into IE > View > Source . . . THEN and only
then, I can start to use my If InStr command. I’m sure I’ll have a
combination of If InStr commends, actually.

So, my question is, how do I get something like IE > View > Source and
assign that to some variable, URL2, or whatever?

Thanks!!!
 
R

ron

Hello friends!  I have a SharePoint URL, which I define as this:

URL =https://collaboration.co.net/sites/US/Shared Documents/

Here is my VBA:

setrestart:
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")

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

Application.StatusBar = "Processing your Request. Please wait..."
xlFile = strpath & "/" & strFullString & ".xls" '& ActiveWorkbook.Name
activeWB = strFullString & ".xls"

Now . . . I’m trying to see if files are checked out from the
SharePoint site, so I’m thinking of using a line of VBA, as such

If InStr(1, URL, "Checked Out To:") > 1 Then
‘ . . . do something
End If

Of, course, the URL is just the string from above.  What I really want
to do is something like go into IE > View > Source . . . THEN and only
then, I can start to use my If InStr command.  I’m sure I’ll have a
combination of If InStr commends, actually.

So, my question is, how do I get something like IE > View > Source and
assign that to some variable, URL2, or whatever?

Thanks!!!
Click to expand...

If your macro needs to have IE opened and has already done so, then

my_var = ie.document.body.innerhtml

will assign the source code behind the current web page to the
variable "my_var". If there is no need to have IE open, then the
following construction will also assign the source code to "my_var".

my_url = "http://www.whatever"
Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "GET", my_url, False
my_obj.send
my_var = my_obj.responsetext
Set my_obj = Nothing

Since IE is not opened, this latter construction will run much more
quickly. It is paticularly advantageous if you are collecting data
from multiple urls...Ron
 
R

ryguy7272

If your macro needs to have IE opened and has already done so, then

        my_var = ie.document.body.innerhtml

will assign the source code behind the current web page to the
variable "my_var".  If there is no need to have IE open, then the
following construction will also assign the source code to "my_var".

    my_url = "http://www.whatever"
    Set my_obj = CreateObject("MSXML2.XMLHTTP")
    my_obj.Open "GET", my_url, False
    my_obj.send
    my_var = my_obj.responsetext
    Set my_obj = Nothing

Since IE is not opened, this latter construction will run much more
quickly.  It is paticularly advantageous if you are collecting data
from multiple urls...Ron- Hide quoted text -

- Show quoted text -
Click to expand...



Thanks Ron! I tried this:
With IE
.navigate URL
.Visible = True
While .Busy Or .readyState <> 4 Or IE.Busy = True: Wend
Set HTMLdoc = .document
End With

Dim my_var As Variant
my_var = IE.document.body.innerHTML

I get a message saying ‘Object variable or with block variable not
set’

I even put my_var inside the With . . . End With and the result was
the same.

Anyway, I don’t want to display the source so I did:
..Visible = False

So, I tried this:
Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "GET", URL, False
my_obj.send
my_var = my_obj.responseText
Set my_obj = Nothing

I step through the code and when I’m past my_var, I do the old ?
my_var, in the Immediate Window, and I get a very small sample of the
I get a small sample of HTML, but I don’t think it’s the View >
Source, and it’s only about 1% of what should be there.

What am I doing wrong?
 
R

ron

Thanks Ron!  I tried this:
With IE
    .navigate URL
    .Visible = True
    While .Busy Or .readyState <> 4 Or IE.Busy = True: Wend
    Set HTMLdoc = .document
End With

Dim my_var As Variant
my_var = IE.document.body.innerHTML

I get a message saying ‘Object variable or with block variable not
set’

I even put my_var inside the With . . . End With and the result was
the same.
Click to expand...

I use the following construction, does it work for you?

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.whatever"
.Top = 50
.Left = 530
.Height = 400
.Width = 400

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

End With

' assign the source code to a variable
my_var = ie.document.body.innerhtml

' do stuff

ie.Quit

If it fails for you, let me know which references you have set
Anyway, I don’t want to display the source so I did:
.Visible = False

So, I tried this:
    Set my_obj = CreateObject("MSXML2.XMLHTTP")
    my_obj.Open "GET", URL, False
    my_obj.send
    my_var = my_obj.responseText
    Set my_obj = Nothing

I step through the code and when I’m past my_var, I do the old ?
my_var, in the Immediate Window, and I get a very small sample of the
I get a small sample of HTML, but I don’t think it’s the View >
Source, and it’s only about 1% of what should be there.
Click to expand...

Yep, that's been my experience as well. I only see the last 50-100 or
so lines of code when I do that. But I'd bet that all the code is
contained in the variable, it has been for me. Check it by looking
for certain words that are in the source code by using instr() with
the variable. Or you could use something like

my_var = ie.Document.body.innerhtml
rr = Split(my_var, Chr(13))
For c = LBound(rr) To UBound(rr)
ActiveCell = rr(c)
ActiveCell.Offset(1, 0).Select
Next

to put the variable contents onto a sheet to compare visually against
the source code...Ron
 
R

ryguy7272

I use the following construction, does it work for you?

    Set ie = CreateObject("InternetExplorer.Application")

    With ie
        .Visible = True
        .Navigate "http://www.whatever"
        .Top = 50
        .Left = 530
        .Height = 400
        .Width = 400

' Loop until the page is fully loaded
        Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
             DoEvents
        Loop

    End With

' assign the source code to a variable
    my_var = ie.document.body.innerhtml

' do stuff

    ie.Quit

If it fails for you, let me know which references you have set




Yep, that's been my experience as well.  I only see the last 50-100 or
so lines of code when I do that.  But I'd bet that all the code is
contained in the variable, it has been for me.  Check it by looking
for certain words that are in the source code by using instr() with
the variable.  Or you could use something like

        my_var = ie.Document.body.innerhtml
        rr = Split(my_var, Chr(13))
        For c = LBound(rr) To UBound(rr)
            ActiveCell = rr(c)
            ActiveCell.Offset(1, 0).Select
        Next

 to put the variable contents onto a sheet to compare visually against
the source code...Ron- Hide quoted text -

- Show quoted text -
Click to expand...


That appears to work!!! Thanks you sir!
BTW, are you in the NYC area? If so, I'll buy you a pint for this!

Thanks again!
Ryan---
 
R

ron

That appears to work!!!  Thanks you sir!
BTW, are you in the NYC area?  If so, I'll buy you a pint for this!

Thanks again!
Ryan---- Hide quoted text -

- Show quoted text -
Click to expand...

Ryan...Glad it worked! I've received lots of help over the years from
this NG; it's nice to be able to give something back...Ron
 
R

ryguy7272

Ryan...Glad it worked!  I've received lots of help over the years from
this NG; it's nice to be able to give something back...Ron- Hide quoted text -

- Show quoted text -
Click to expand...


Yeap, I've received, and given, lots of advice. It goes both ways.
Wayyyyyyyyy more information here than you will ever find in any book,
or even dozens of books. Books are a great place to start learning,
but when you have very specific questions, which are very technical,
you come here!!!
 
Top