Web Import

[ryguy7272]

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

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

Temporary Username = Ryan (case sensitive)
temporary password = ryan123 (case sensitive)

Then, following the manual steps, Components > RSform!Pro > Manage
Submissions, will take a user to the data that I want to import. I am trying
to automate this process. I thought I could record a macro to do this for
me, and I’ve done this many times before, but I guess because this is a
secure site, I can't record a macro, then modifying the resulting VBA; it
simply doesn’t work. You can do the import one time, but when you save the
workbook, close it, then reopen it, you lost the connection to the site, and
thus can't import anything. So, I posted my web import question here last
week.

I got a lot of help from a guy named Ron. He gave me the code below:

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

End Sub

The code works quite well, but there is one problem, which I never figured
out. The code will only import the data from the web site and stack it all
in ColumnA. What I really wanted to do was import it from the web in the row
and column format as it appeared on the web site.

Does anyone have any ideas on how to modify the code above to get it to
import from the site, in the row and column format as it is displayed on the
site? Or, can I somehow record a macro, and modify the code a bit, to
automatically import the data on that site?


Thanks so much,
Ryan---

 

[Joel]

It depends on how the webpage is set up. You may be able to use Text to
Columns Not sure if delimited or Fixed Width is appropriate. If either
method works, then if you always paste the data into the same location
Text-To-
Column will rembers the previous parameters and write the new data the same
as the previous data. So if you use the same spreadsheet you can
clearcontents (remove old data) then import the new data.

If text to column doesn't work post some lines of the data so a macro can be
written to seperate the columns.

 

[ryguy7272]

Thanks fr the look Joel. Great suggestion! Tried Text to Columns;
unfortunately, that didn't work. Now, when the code fires, it seems to
copy/paste the actual VBA onto the active sheet! It doesn't even do the
import from that web site now! Very bizarre.

I've done web imports many, many, many times before, and never encountered
such difficulties. I believe it has something to do with the nature of this
particular site; the level of security seems to be an issue. Normally, I
import from web sites that don't require a login and password. Actually, the
login and password seems to work fine, but once Excel (automatically)
navigates to the page of interest, the data that is imported is totally
screwed up. Before, everything was stacked in one single column; now, all I
see is my own VBA...no data from the site.

Any ideas on this? Anyone?

TIA,
Ryan----

 

[Joel]

Your response is confussing. What did you do to get the VBA onto the
worksheet?

I would still like to see an example of the data to see if I can un-jumble
the data with a macro.

 

[gimme_this_gimme_that]

Two people have already answered your question. I posted before and
you didn't look at the code.

 

[ryguy7272]

It is very weird. I’m been programming in Excel for several years, just a
little at first and much more now, but I have never seen anything like this.
I can’t figure out why it will not do the import, but rather copy/paste the
macro, itself into the activesheet. I’m thinking that there must be another
way of doing this. Do you have any alternative ideas Joel?

Thanks,
Ryan---

 

[ryguy7272]

Thanks! I fiddled with all that code from 2/20/09; tried to get it working,
but I couldn’t get anything going. Ron’s code seemed to work for a day or
two, then Excel seemed to just copy/paste the actual VBA code in the module
into the activesheet. Again, nothing has worked out for me. I’ll Google for
an answer; I’ll definitely do a little ore research on this. I would surmise
that the issue is with the ‘secure’ site. I’ve imported web data hundreds of
times before, all from non-secure sites, and never experienced the problems
that I’m experiencing now. I’ll bet anything that Excel can do it, I just
don’t know enough about it to do it myself. I don’t even know enough about
secure sites to modify the samples of code that I received.

Thanks,
Ryan---