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