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

URL = https://collaboration.co.net/sites/U...d%20Documents/

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

On Aug 17, 9:55*pm, ryguy7272 wrote:
Hello friends! *I have a SharePoint URL, which I define as this:

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

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

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

On Aug 18, 10:58*am, ron wrote:
On Aug 17, 9:55*pm, ryguy7272 wrote:





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

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

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

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 -



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?

On Aug 18, 12:15*pm, ryguy7272 wrote:
On Aug 18, 10:58*am, ron wrote:





On Aug 17, 9:55*pm, ryguy7272 wrote:

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

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

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

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 -

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.

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.

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

On Aug 18, 2:34*pm, ron wrote:
On Aug 18, 12:15*pm, ryguy7272 wrote:





On Aug 18, 10:58*am, ron wrote:

On Aug 17, 9:55*pm, ryguy7272 wrote:

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

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

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

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 -

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.

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.

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 -


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

On Aug 18, 2:21*pm, ryguy7272 wrote:
On Aug 18, 2:34*pm, ron wrote:





On Aug 18, 12:15*pm, ryguy7272 wrote:

On Aug 18, 10:58*am, ron wrote:

On Aug 17, 9:55*pm, ryguy7272 wrote:

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

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

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

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 -

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.

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.

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 -

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 -

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

On Aug 18, 4:40*pm, ron wrote:
On Aug 18, 2:21*pm, ryguy7272 wrote:





On Aug 18, 2:34*pm, ron wrote:

On Aug 18, 12:15*pm, ryguy7272 wrote:

On Aug 18, 10:58*am, ron wrote:

On Aug 17, 9:55*pm, ryguy7272 wrote:

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

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

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

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 -

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.

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.

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 -

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 -

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 -


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