I can see a tremendous benefit in understanding these techniques here, but
still am not quite getting it. Id like to try this one more time, please.
I believe the current, and final, challenge is to log into this secure web
site (Ive done this many times before; each time I was on a non-secure site,
and everything worked fine). Here is my code:
Sub test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
..Visible = True
..navigate "http://www.countrybobsdemo.com/administrator/"
..Top = 100
..Left = 100
..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
'Range("A1").Select
'ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False
'Range("A1").Select
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_62"
.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
..Quit
End With
End Sub
When I open my WB and run the code nothing happens. I dont want an
Auto_Open or On-Open event. I just want to open the WB, click the Command
Button, and have the code log me into the web site. This step seems to fail
every time. However, if I record the steps, as such:
http://www.countrybobsdemo.com/administrator/
Login = Ryan
Password = ryan123
Components RSForm!Pro Manage Submissions Import...then everything
works just fine the next time I run the code. Next, if I log into the web
site, clear the sheet, and re-query for data, the data updates just fine.
The problem is the initial login. I cant seem to establish the initial
connection to the data unless I go through the steps just described above,
and that kind of nullified the point of setting up the macro to do this for
me.
Any other ideas Ron?
BTW, I was never able to get this part to work:
ie.ExecWB 17, 2
ie.ExecWB 12, 0
Range("A1").Select
ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select
Thank for everything,
Ryan---
--
RyGuy
" wrote:
On Feb 23, 9:14 am, ryguy7272
wrote...snip...
This works perfectly fine if I clear all the data from the previous run, do
the import, clear, import, and so on. However, it does not do the initial
import if I close the WB and reopen it. I guess Excel cant establish the
initial connection to the secured site! If I record a macro during my login
steps, and copy/paste the web import under Rons original code, things are
fine. Again, if I close the WB and open it, Excel doesnt seem to know how
to find the web site. I suspect this has something to do with the
login/security. Ive done web imports many times before without a problem,
but all prior experience is with non-secure web sites. Ron, any other ideas
on how to establish a connection when the WB is opened? Anyone?
Ryan...The macro is not coded to auto run upon workbook open. You
would need to use an "on workbook open" event handler to accomplish
this. You would also need additional code to clear out the old data
prior to import or identify another location to import to if you want
to keep the old import data. BTW, were you unable to get the
following copy /paste sequence to work?
' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB 17, 2
ie.ExecWB 12, 0
Range("A1").Select
ActiveSheet.PasteSpecial Format:="text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select
....ron