I am trying to do a simple web, from a password-protected site:

This is the link:
http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary password = ryan123 (case
sensitive). I recorded a simple macro:

Sub Macro1()

Cells.Select
Selection.ClearContents

If QueryTable = True Then
Selection.QueryTable.Delete
Else

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=*" _
, Destination:=Range("A1"))
.Name = "administrator/index.php?option=*"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If

Range("C:C,A:A").Select
Range("A1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

I log into the web site first and run the code next. The code works fine
the first time it is run, but doesn't work any subsequent times. i believe
it has something to do with this line:
..Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. I tried the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?


Thanks so much,
Ryan---



--
RyGuy

Try this.
Use your macro to log on and establish the query.
Now record a macro while doing a refresh instead of an add.

example
With ActiveSheet.QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/os?s=" & sym & "&m=" &
mdate
.Refresh BackgroundQuery:=False
End With
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
I am trying to do a simple web, from a password-protected site:

This is the link:
http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary password = ryan123 (case
sensitive). I recorded a simple macro:

Sub Macro1()

Cells.Select
Selection.ClearContents

If QueryTable = True Then
Selection.QueryTable.Delete
Else

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=*"
_
, Destination:=Range("A1"))
.Name = "administrator/index.php?option=*"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If

Range("C:C,A:A").Select
Range("A1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

I log into the web site first and run the code next. The code works fine
the first time it is run, but doesn't work any subsequent times. i
believe
it has something to do with this line:
.Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. I tried
the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?


Thanks so much,
Ryan---



--
RyGuy

Perhaps you could translate this VBScript into VBA and swap out the
generic parameters for those for your site.

ryan123/ryan123 isn't working for me.

This script doesn't do *exactly* what you want - but it might be
enough to get you started if you know VBA.

YMMV.


Dim IE,doc

Sub Main(logon,password)

WScript.Sleep(1000)
Set IE = CreateObject("InternetExplorer.Application")
WScript.Sleep(1000)

IE.Visible = False

title = Navigate("http://www.somesite.com")

If DoMatch(title,"Login") Then

Set doc = IE.Document

Set form = doc.forms("login_form")
form.elements("logon").value = logon
form.elements("password").value = password
form.submit

Do while IE.Busy
Loop

title = IE.Document.title

If DoMatch(title,"Home") Then
' user logged in normally
PeruseHome
Navigate ("http://www.somesite.com/LOGOUT/")
Else
' user can't log in
Notify()
End If
Else
' user is probably already logged in and is on the home page
If DoMatch(title,"Home") Then
PeruseHome
'logout
Navigate ("http://www.somesite.com/LOGOUT/")
End If
End If
IE.Quit
Set IE = Nothing
End Sub

Sub WriteToFile(fileName,text)
Set wFSO = CreateObject("Scripting.FileSystemObject")
Set wFile = wFSO.OpenTextFile(fileName, 2 , True)
wFile.WriteLine text
wFile.Close
End Sub

Function DoMatch(strInput, strPattern)
Dim re : Set re = New RegExp
re.IgnoreCase = True
re.Global = True
re.Pattern = strPattern
DoMatch = re.Test(strInput)
re.Pattern = ""
End Function

Function Navigate(url)
IE.Navigate url
Do while IE.Busy
Loop
WScript.Sleep(2000)
Navigate = IE.Document.title
End Function

Sub PeruseHome
text = doc.body.innerText
If DoMatch(text,"Navigation") OR DoMatch(text,"error has occurred")
Then
'Do Nothing when there is an error
Else
'Write file when things are okay
WriteToFile "C:\_sps.txt",text
End If
End Sub

Sub Notify()
Set msg = CreateObject("CDO.Message")
msg.Subject = "Monitoring username/password on sps.accountemps.com is
failing"
msg.From = "
msg.To = "
msg.TextBody = "This message was sent from an automated script."
msg.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/
configuration/sendusing") = 2
msg.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/
configuration/smtpserver") = "hqpwtload.corp.rhalf.com"
msg.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/
configuration/smtpserverport") = 25
msg.Configuration.Fields.Update
msg.Send
End Sub


Call Main("someusername","somepassword")

On Feb 20, 3:45*pm, ryguy7272
wrote:
I am trying to do a simple web, from a password-protected site:

This is the link:http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary *password = ryan123 (case
sensitive). *I recorded a simple macro:

Sub Macro1()

* * Cells.Select
* * Selection.ClearContents

* * * * If QueryTable = True Then
* * * * Selection.QueryTable.Delete
* * * * Else

* * Range("A1").Select
* * With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
* * * * , Destination:=Range("A1"))
* * * * .Name = "administrator/index.php?option=*"
* * * * .FieldNames = True
* * * * .RowNumbers = False
* * * * .FillAdjacentFormulas = False
* * * * .PreserveFormatting = True
* * * * .RefreshOnFileOpen = False
* * * * .BackgroundQuery = True
* * * * .RefreshStyle = xlInsertDeleteCells
* * * * .SavePassword = False
* * * * .SaveData = True
* * * * .AdjustColumnWidth = True
* * * * .RefreshPeriod = 0
* * * * .WebSelectionType = xlSpecifiedTables
* * * * .WebFormatting = xlWebFormattingNone
* * * * .WebTables = "3"
* * * * .WebPreFormattedTextToColumns = True
* * * * .WebConsecutiveDelimitersAsOne = True
* * * * .WebSingleBlockTextImport = False
* * * * .WebDisableDateRecognition = False
* * * * .WebDisableRedirections = False
* * * * .Refresh BackgroundQuery:=False
* * End With
* * End If

* * Range("C:C,A:A").Select
* * Range("A1").Activate
* * Selection.Delete Shift:=xlToLeft
* * Range("A1").Select
End Sub

I log into the web site first and run the code next. *The code works fine
the first time it is run, but doesn't work any subsequent times. *i believe
it has something to do with this line:
.Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. *I tried the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?

Thanks so much,
Ryan---

--
RyGuy

Ryan...How about something like this

Sub test()
Set ie = CreateObject("InternetExplorer.Application")

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

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

' Which web page are you on? If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
my_var = ie.document.body.innerhtml
If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
Set ipf = ie.document.all.Item("username")
ipf.Value = "Ryan"

Set ipf = ie.document.all.Item("passwd")
ipf.Value = "ryan123"
ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
Else
End If

' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB 17, 2
ie.ExecWB 12, 0

ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

ie.Quit
End With

' Do whatever with the pasted info
' Range("C:C,A:A").Select
' Range("A1").Activate
' Selection.Delete Shift:=xlToLeft
' Range("A1").Select

End Sub

Instead of copying and pasting the webpage onto your worksheet, you
could replace that entire section with another

my_var = ie.document.body.innerhtml
or
my_var = ie.document.body.innertext

and then use text functions like "instr", "mid", etc. to extract the
info you want...Ron

http://www.countrybobsdemo.com/administrator/
Login = Ryan
Password = ryan123

Components RSForm!Pro Manage Submissions. There you will see a few
names, addresses, and a few other (personal) elements. I want to be able to
import these items from this part of the web site. Ive done this many times
before, but cant seem to get it working this time. I tried the examples
that people posted here, but couldnt get any of those working either. Now,
I am back to square 1.

When I open the Excel WB, then log onto the site, then record a macro to
import the data, everything works fine. I can delete the Web Query and
re-import several times, and everything is fine. When I close the WB, then
reopen it, it doesnt work any longer, even if I am still logged into the web
site! Thats why I am posting here; I dont understand this behavior. I
guess it has something to do with a secure login€¦maybe€¦just guessing€¦

I tried to create a small procedure to load a web browser so I could login,
based on the code posted by Ron, then call the actual import macro; nothing
has worked so far. My code is below:
Sub Import()

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://www.countrybobsdemo.com/administrator/"
.Top = 50
.Left = 530
.Height = 400
.Width = 400
End With

Call NextStep
End Sub

Sub NextStep()
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" _
, Destination:=Range("A1"))
.Name =
"index.php?option=com_rsform&task=submissions.mana ge&formId=2_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Again, when I close the WB and reopen it, the Sub doesnt do the import.

Any other ideas?

Thanks so much,
Ryan---


--
RyGuy


" wrote:

On Feb 20, 3:45 pm, ryguy7272
wrote:
I am trying to do a simple web, from a password-protected site:

This is the link:http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary password = ryan123 (case
sensitive). I recorded a simple macro:

Sub Macro1()

Cells.Select
Selection.ClearContents

If QueryTable = True Then
Selection.QueryTable.Delete
Else

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
, Destination:=Range("A1"))
.Name = "administrator/index.php?option=*"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If

Range("C:C,A:A").Select
Range("A1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

I log into the web site first and run the code next. The code works fine
the first time it is run, but doesn't work any subsequent times. i believe
it has something to do with this line:
.Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. I tried the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?

Thanks so much,
Ryan---

--
RyGuy

Ryan...How about something like this

Sub test()
Set ie = CreateObject("InternetExplorer.Application")

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

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

' Which web page are you on? If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
my_var = ie.document.body.innerhtml
If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
Set ipf = ie.document.all.Item("username")
ipf.Value = "Ryan"

Set ipf = ie.document.all.Item("passwd")
ipf.Value = "ryan123"
ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
Else
End If

' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB 17, 2
ie.ExecWB 12, 0

ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

ie.Quit
End With

' Do whatever with the pasted info
' Range("C:C,A:A").Select
' Range("A1").Activate
' Selection.Delete Shift:=xlToLeft
' Range("A1").Select

End Sub

Instead of copying and pasting the webpage onto your worksheet, you
could replace that entire section with another

my_var = ie.document.body.innerhtml
or
my_var = ie.document.body.innertext

and then use text functions like "instr", "mid", etc. to extract the
info you want...Ron

On Feb 22, 1:31*pm, ryguy7272
wrote:
http://www.countrybobsdemo.com/administrator/
Login = Ryan
Password = ryan123

Components RSForm!Pro Manage Submissions. *There you will see a few
names, addresses, and a few other (personal) elements. *I want to be able to
import these items from this part of the web site. *I’ve done this many times
before, but can’t seem to get it working this time. *I tried the examples
that people posted here, but couldn’t get any of those working either. *Now,
I am back to square 1. *

When I open the Excel WB, then log onto the site, then record a macro to
import the data, everything works fine. *I can delete the Web Query and
re-import several times, and everything is fine. *When I close the WB, then
reopen it, it doesn’t work any longer, even if I am still logged into the web
site! *That’s why I am posting here; I don’t understand this behavior. *I
guess it has something to do with a secure login…maybe…just guessing…

I tried to create a small procedure to load a web browser so I could login,
based on the code posted by Ron, then call the actual import macro; nothing
has worked so far. *My code is below:
Sub Import()

Set ie = CreateObject("InternetExplorer.Application")
* * With ie
* * .Visible = True
* * .Navigate "http://www.countrybobsdemo.com/administrator/"
* * .Top = 50
* * .Left = 530
* * .Height = 400
* * .Width = 400
* * End With

Call NextStep
End Sub

Sub NextStep()
* * With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
* * * * , Destination:=Range("A1"))
* * * * .Name =
"index.php?option=com_rsform&task=submissions.mana ge&formId=2_2"
* * * * .FieldNames = True
* * * * .RowNumbers = False
* * * * .FillAdjacentFormulas = False
* * * * .PreserveFormatting = True
* * * * .RefreshOnFileOpen = False
* * * * .BackgroundQuery = True
* * * * .RefreshStyle = xlInsertDeleteCells
* * * * .SavePassword = False
* * * * .SaveData = True
* * * * .AdjustColumnWidth = True
* * * * .RefreshPeriod = 0
* * * * .WebSelectionType = xlSpecifiedTables
* * * * .WebFormatting = xlWebFormattingNone
* * * * .WebTables = "3"
* * * * .WebPreFormattedTextToColumns = True
* * * * .WebConsecutiveDelimitersAsOne = True
* * * * .WebSingleBlockTextImport = False
* * * * .WebDisableDateRecognition = False
* * * * .WebDisableRedirections = False
* * * * .Refresh BackgroundQuery:=False
* * End With
End Sub

Again, when I close the WB and reopen it, the Sub doesn’t do the import..

Any other ideas?

Thanks so much,
Ryan---

--
RyGuy



" wrote:
On Feb 20, 3:45 pm, ryguy7272
wrote:
I am trying to do a simple web, from a password-protected site:

This is the link:http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary *password = ryan123 (case
sensitive). *I recorded a simple macro:

Sub Macro1()

* * Cells.Select
* * Selection.ClearContents

* * * * If QueryTable = True Then
* * * * Selection.QueryTable.Delete
* * * * Else

* * Range("A1").Select
* * With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
* * * * , Destination:=Range("A1"))
* * * * .Name = "administrator/index.php?option=*"
* * * * .FieldNames = True
* * * * .RowNumbers = False
* * * * .FillAdjacentFormulas = False
* * * * .PreserveFormatting = True
* * * * .RefreshOnFileOpen = False
* * * * .BackgroundQuery = True
* * * * .RefreshStyle = xlInsertDeleteCells
* * * * .SavePassword = False
* * * * .SaveData = True
* * * * .AdjustColumnWidth = True
* * * * .RefreshPeriod = 0
* * * * .WebSelectionType = xlSpecifiedTables
* * * * .WebFormatting = xlWebFormattingNone
* * * * .WebTables = "3"
* * * * .WebPreFormattedTextToColumns = True
* * * * .WebConsecutiveDelimitersAsOne = True
* * * * .WebSingleBlockTextImport = False
* * * * .WebDisableDateRecognition = False
* * * * .WebDisableRedirections = False
* * * * .Refresh BackgroundQuery:=False
* * End With
* * End If

* * Range("C:C,A:A").Select
* * Range("A1").Activate
* * Selection.Delete Shift:=xlToLeft
* * Range("A1").Select
End Sub

I log into the web site first and run the code next. *The code works fine
the first time it is run, but doesn't work any subsequent times. *i believe
it has something to do with this line:
.Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. *I tried the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?

Thanks so much,
Ryan---

--
RyGuy

Ryan...How about something like this

Sub test()
* * Set ie = CreateObject("InternetExplorer.Application")

* * With ie
* * * * .Visible = True
* * * * .Navigate "http://www.countrybobsdemo.com/administrator/"
* * * * .Top = 50
* * * * .Left = 530
* * * * .Height = 400
* * * * .Width = 400

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

' Which web page are you on? *If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
* * * * my_var = ie.document.body.innerhtml
* * * * * * If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
* * * * * * * * Set ipf = ie.document.all.Item("username")
* * * * * * * * ipf.Value = "Ryan"

* * * * * * * * Set ipf = ie.document.all.Item("passwd")
* * * * * * * * * * ipf.Value = "ryan123"
* * * * * * * * ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
* * * * * * * * Do Until Not ie.Busy And ie.ReadyState = 4
* * * * * * * * * * DoEvents
* * * * * * * * Loop
* * * * * * Else
* * * * * * End If

' Copy the entire web page and then paste it as text into the
worksheet
* * * * ie.ExecWB 17, 2
* * * * ie.ExecWB 12, 0

* * * * ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
* * * * Range("A1").Select

* * * * ie.Quit
* * End With

' *Do whatever with the pasted info
' * *Range("C:C,A:A").Select
' * *Range("A1").Activate
' * *Selection.Delete Shift:=xlToLeft
' * *Range("A1").Select

End Sub

Instead of copying and pasting the webpage onto your worksheet, you
could replace that entire section with another

* * * * my_var = ie.document.body.innerhtml
or
* * * * my_var = ie.document.body.innertext

and then use text functions like "instr", "mid", etc. to extract the
info you want...Ron- Hide quoted text -

- Show quoted text -

Ryan...I've just inserted a navigation line into my earlier code to
get to the desired page. It works like a charm for me. If you have a
problem, pass along the specifics. Note that some of the lines (like
the url in the navigation line) break when posting. Be sure to reset
them to a single line...ron

Sub test()
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "http://www.countrybobsdemo.com/administrator/ "
.Top = 50
.Left = 530
.Height = 400
.Width = 400

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

' Which web page are you on? If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
my_var = ie.document.body.innerhtml
If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
Set ipf = ie.document.all.Item("username")
ipf.Value = "Ryan"


Set ipf = ie.document.all.Item("passwd")
ipf.Value = "ryan123"
ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
Else
End If

' Navigate to the desired page
.navigate "http://www.countrybobsdemo.com/administrator/
index.php?option=com_rsform&task=submissions.manag e&formId=2" ' this
should all be on 1 line

Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
' Copy the entire web page and then paste it as text into the
Worksheet
ie.ExecWB 17, 2
ie.ExecWB 12, 0

ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

.Quit
End With
End Sub

Thanks for the follow up Ron. I ran the code and it looks like Excel is
running through the procedures fine, and things seems to work, but then right
at the end, instead of importing the actual data, it imports the VBA code!!
Wild!! Ive never seen that before!! This happened when I clicked Tools
Internet options Advanced Disable Script Debugging (unchecked).

If I close the window (red €˜X), then I get a message that says €˜run time
error Method €˜Busy of object €˜IWebBrowser2 failed.

I click Debug and this link is highlighted yellow:
Do Until Not ie.Busy And ie.ReadyState = 4

I understand most of this VBA, but not this line, and not this stuff:
ie.ExecWB 17, 2
ie.ExecWB 12, 0

Those look like row & column references; perhaps HTML or ASP requires these?
What am I doing wrong?

Thanks,
Ryan---




--
RyGuy


" wrote:

On Feb 22, 1:31 pm, ryguy7272
wrote:
http://www.countrybobsdemo.com/administrator/
Login = Ryan
Password = ryan123

Components RSForm!Pro Manage Submissions. There you will see a few
names, addresses, and a few other (personal) elements. I want to be able to
import these items from this part of the web site. Ive done this many times
before, but cant seem to get it working this time. I tried the examples
that people posted here, but couldnt get any of those working either. Now,
I am back to square 1.

When I open the Excel WB, then log onto the site, then record a macro to
import the data, everything works fine. I can delete the Web Query and
re-import several times, and everything is fine. When I close the WB, then
reopen it, it doesnt work any longer, even if I am still logged into the web
site! Thats why I am posting here; I dont understand this behavior. I
guess it has something to do with a secure login€¦maybe€¦just guessing€¦

I tried to create a small procedure to load a web browser so I could login,
based on the code posted by Ron, then call the actual import macro; nothing
has worked so far. My code is below:
Sub Import()

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://www.countrybobsdemo.com/administrator/"
.Top = 50
.Left = 530
.Height = 400
.Width = 400
End With

Call NextStep
End Sub

Sub NextStep()
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
, Destination:=Range("A1"))
.Name =
"index.php?option=com_rsform&task=submissions.mana ge&formId=2_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Again, when I close the WB and reopen it, the Sub doesnt do the import..

Any other ideas?

Thanks so much,
Ryan---

--
RyGuy



" wrote:
On Feb 20, 3:45 pm, ryguy7272
wrote:
I am trying to do a simple web, from a password-protected site:

This is the link:http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary password = ryan123 (case
sensitive). I recorded a simple macro:

Sub Macro1()

Cells.Select
Selection.ClearContents

If QueryTable = True Then
Selection.QueryTable.Delete
Else

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
, Destination:=Range("A1"))
.Name = "administrator/index.php?option=*"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If

Range("C:C,A:A").Select
Range("A1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

I log into the web site first and run the code next. The code works fine
the first time it is run, but doesn't work any subsequent times. i believe
it has something to do with this line:
.Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. I tried the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?

Thanks so much,
Ryan---

--
RyGuy

Ryan...How about something like this

Sub test()
Set ie = CreateObject("InternetExplorer.Application")

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

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

' Which web page are you on? If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
my_var = ie.document.body.innerhtml
If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
Set ipf = ie.document.all.Item("username")
ipf.Value = "Ryan"

Set ipf = ie.document.all.Item("passwd")
ipf.Value = "ryan123"
ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
Else
End If

' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB 17, 2
ie.ExecWB 12, 0

ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

ie.Quit
End With

' Do whatever with the pasted info
' Range("C:C,A:A").Select
' Range("A1").Activate
' Selection.Delete Shift:=xlToLeft
' Range("A1").Select

End Sub

Instead of copying and pasting the webpage onto your worksheet, you
could replace that entire section with another

my_var = ie.document.body.innerhtml
or
my_var = ie.document.body.innertext

and then use text functions like "instr", "mid", etc. to extract the
info you want...Ron- Hide quoted text -

- Show quoted text -

Ryan...I've just inserted a navigation line into my earlier code to
get to the desired page. It works like a charm for me. If you have a
problem, pass along the specifics. Note that some of the lines (like
the url in the navigation line) break when posting. Be sure to reset
them to a single line...ron

Sub test()
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "http://www.countrybobsdemo.com/administrator/ "
.Top = 50
.Left = 530
.Height = 400
.Width = 400

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

' Which web page are you on? If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
my_var = ie.document.body.innerhtml
If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
Set ipf = ie.document.all.Item("username")
ipf.Value = "Ryan"


Set ipf = ie.document.all.Item("passwd")
ipf.Value = "ryan123"
ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
Else
End If

' Navigate to the desired page
.navigate "http://www.countrybobsdemo.com/administrator/
index.php?option=com_rsform&task=submissions.manag e&formId=2" ' this
should all be on 1 line

Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
' Copy the entire web page and then paste it as text into the
Worksheet
ie.ExecWB 17, 2
ie.ExecWB 12, 0

ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

.Quit
End With
End Sub

On Feb 22, 3:10*pm, ryguy7272
wrote:
Thanks for the follow up Ron. *I ran the code and it looks like Excel is
running through the procedures fine, and things seems to work, but then right
at the end, instead of importing the actual data, it imports the VBA code!! *
Wild!! *I’ve never seen that before!! *This happened when I clicked Tools
Internet options Advanced Disable Script Debugging (unchecked). *

If I close the window (red ‘X’), then I get a message that says ‘run time
error’ Method ‘Busy’ of object ‘IWebBrowser2’ failed.

I click Debug and this link is highlighted yellow:
Do Until Not ie.Busy And ie.ReadyState = 4

I understand most of this VBA, but not this line, and not this stuff:
ie.ExecWB 17, 2
ie.ExecWB 12, 0

Those look like row & column references; perhaps HTML or ASP requires these?
*What am I doing wrong?

Thanks,
Ryan---

--
RyGuy



" wrote:
On Feb 22, 1:31 pm, ryguy7272
wrote:
http://www.countrybobsdemo.com/administrator/
Login = Ryan
Password = ryan123

Components RSForm!Pro Manage Submissions. *There you will see a few
names, addresses, and a few other (personal) elements. *I want to be able to
import these items from this part of the web site. *I’ve done this many times
before, but can’t seem to get it working this time. *I tried the examples
that people posted here, but couldn’t get any of those working either. *Now,
I am back to square 1. *

When I open the Excel WB, then log onto the site, then record a macro to
import the data, everything works fine. *I can delete the Web Query and
re-import several times, and everything is fine. *When I close the WB, then
reopen it, it doesn’t work any longer, even if I am still logged into the web
site! *That’s why I am posting here; I don’t understand this behavior. *I
guess it has something to do with a secure login…maybe…just guessing…

I tried to create a small procedure to load a web browser so I could login,
based on the code posted by Ron, then call the actual import macro; nothing
has worked so far. *My code is below:
Sub Import()

Set ie = CreateObject("InternetExplorer.Application")
* * With ie
* * .Visible = True
* * .Navigate "http://www.countrybobsdemo.com/administrator/"
* * .Top = 50
* * .Left = 530
* * .Height = 400
* * .Width = 400
* * End With

Call NextStep
End Sub

Sub NextStep()
* * With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
* * * * , Destination:=Range("A1"))
* * * * .Name =
"index.php?option=com_rsform&task=submissions.mana ge&formId=2_2"
* * * * .FieldNames = True
* * * * .RowNumbers = False
* * * * .FillAdjacentFormulas = False
* * * * .PreserveFormatting = True
* * * * .RefreshOnFileOpen = False
* * * * .BackgroundQuery = True
* * * * .RefreshStyle = xlInsertDeleteCells
* * * * .SavePassword = False
* * * * .SaveData = True
* * * * .AdjustColumnWidth = True
* * * * .RefreshPeriod = 0
* * * * .WebSelectionType = xlSpecifiedTables
* * * * .WebFormatting = xlWebFormattingNone
* * * * .WebTables = "3"
* * * * .WebPreFormattedTextToColumns = True
* * * * .WebConsecutiveDelimitersAsOne = True
* * * * .WebSingleBlockTextImport = False
* * * * .WebDisableDateRecognition = False
* * * * .WebDisableRedirections = False
* * * * .Refresh BackgroundQuery:=False
* * End With
End Sub

Again, when I close the WB and reopen it, the Sub doesn’t do the import..

Any other ideas?

Thanks so much,
Ryan---

--
RyGuy

" wrote:
On Feb 20, 3:45 pm, ryguy7272
wrote:
I am trying to do a simple web, from a password-protected site:

This is the link:http://www.countrybobsdemo.com/administrator/

Temporary Username = Ryan and temporary *password = ryan123 (case
sensitive). *I recorded a simple macro:

Sub Macro1()

* * Cells.Select
* * Selection.ClearContents

* * * * If QueryTable = True Then
* * * * Selection.QueryTable.Delete
* * * * Else

* * Range("A1").Select
* * With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsf..." _
* * * * , Destination:=Range("A1"))
* * * * .Name = "administrator/index.php?option=*"
* * * * .FieldNames = True
* * * * .RowNumbers = False
* * * * .FillAdjacentFormulas = False
* * * * .PreserveFormatting = True
* * * * .RefreshOnFileOpen = False
* * * * .BackgroundQuery = True
* * * * .RefreshStyle = xlInsertDeleteCells
* * * * .SavePassword = False
* * * * .SaveData = True
* * * * .AdjustColumnWidth = True
* * * * .RefreshPeriod = 0
* * * * .WebSelectionType = xlSpecifiedTables
* * * * .WebFormatting = xlWebFormattingNone
* * * * .WebTables = "3"
* * * * .WebPreFormattedTextToColumns = True
* * * * .WebConsecutiveDelimitersAsOne = True
* * * * .WebSingleBlockTextImport = False
* * * * .WebDisableDateRecognition = False
* * * * .WebDisableRedirections = False
* * * * .Refresh BackgroundQuery:=False
* * End With
* * End If

* * Range("C:C,A:A").Select
* * Range("A1").Activate
* * Selection.Delete Shift:=xlToLeft
* * Range("A1").Select
End Sub

I log into the web site first and run the code next. *The code works fine
the first time it is run, but doesn't work any subsequent times. *i believe
it has something to do with this line:
.Name = "administrator/index.php?option=*"

I'm not sure what to change on there to make it more 'generic'. *I tried the
astrix symbol, but no luck with that.

Any ideas how to modify that line code?

Thanks so much,
Ryan---

--
RyGuy

Ryan...How about something like this

Sub test()
* * Set ie = CreateObject("InternetExplorer.Application")

* * With ie
* * * * .Visible = True
* * * * .Navigate "http://www.countrybobsdemo.com/administrator/"
* * * * .Top = 50
* * * * .Left = 530
* * * * .Height = 400
* * * * .Width = 400

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

' Which web page are you on? *If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
* * * * my_var = ie.document.body.innerhtml
* * * * * * If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
* * * * * * * * Set ipf = ie.document.all.Item("username")
* * * * * * * * ipf.Value = "Ryan"

* * * * * * * * Set ipf = ie.document.all.Item("passwd")
* * * * * * * * * * ipf.Value = "ryan123"
* * * * * * * * ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
* * * * * * * * Do Until Not ie.Busy And ie.ReadyState = 4
* * * * * * * * * * DoEvents
* * * * * * * * Loop
* * * * * * Else
* * * * * * End If

' Copy the entire web page and then paste it as text into the
worksheet
* * * * ie.ExecWB 17, 2
* * * * ie.ExecWB 12, 0

* * * * ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
* * * * Range("A1").Select

* * * * ie.Quit
* * End With

' *Do whatever with the pasted info
' * *Range("C:C,A:A").Select
' * *Range("A1").Activate
' * *Selection.Delete Shift:=xlToLeft
' * *Range("A1").Select

End Sub

Instead of copying and pasting the webpage onto your worksheet, you
could replace that entire section with another

* * * * my_var = ie.document.body.innerhtml
or
* * * * my_var = ie.document.body.innertext

and then use text functions like "instr", "mid", etc. to extract the
info you want...Ron- Hide quoted text -

- Show quoted text -

Ryan...I've just inserted a navigation line into my earlier code to
get to the desired page. *It works like a charm for me. *If you have a
problem, pass along the specifics. *Note that some of the lines (like
the url in the navigation line) break when posting. *Be sure to reset
them to a single line...ron

Sub test()
* * Set ie = CreateObject("InternetExplorer.Application")

* * With ie
* * * * .Visible = True
* * * * .navigate "http://www.countrybobsdemo.com/administrator/"
* * * * .Top = 50
* * * * .Left = 530
* * * * .Height = 400
* * * * .Width = 400

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

' Which web page are you on? *If it is the "log in" page then make the
' desired selections on the Login web page and submit the form;
' else, just copy and paste the page as text
* * * * my_var = ie.document.body.innerhtml
* * * * * * If InStr(1, my_var, "passwd", vbTextCompare) 1 Then
* * * * * * * * Set ipf = ie.document.all.Item("username")
* * * * * * * * ipf.Value = "Ryan"

* * * * * * * * Set ipf = ie.document.all.Item("passwd")
* * * * * * * * * * ipf.Value = "ryan123"
* * * * * * * * ie.document.all.Item("form-login").submit

' Loop until the page is fully loaded
* * * * * * * * Do Until Not ie.Busy And ie.ReadyState = 4

...

read more »- Hide quoted text -

- Show quoted text -

Ryan...the line

Do Until Not ie.Busy And ie.ReadyState = 4

is part of a loop. It keeps the macro looping / testing to see if the
webpage is fully loaded.

The lines

ie.ExecWB 17, 2
ie.ExecWB 12, 0

select all of the web page, and then copy it to the clipboard.

Sometimes, depending what VB references you have set, there can be a
late / early binding issue.

Try

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

and

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

in place of the 3 lines discussed above and see if it runs. The macro
continues to run without problem on my pc...ron