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