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----
--
RyGuy
"Joel" wrote:
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" wrote:
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 Ive 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 doesnt 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---
--
RyGuy