For the sake of the archives, post your final.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
Ah ha!! Finally got it. Your code + my own, yielded the results I was
after. Thanks so much Ron!!

Regards,
Ryan---

--
RyGuy


" wrote:

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

...

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