Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import XML data...Is there a size limit on the import? | Excel Discussion (Misc queries) | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
Import Macro in PERSONAL.XLS will not import to my main document | Excel Programming | |||
PLEASE!! How can I un-grey the 'Data-Import External Data-Import Data' option ??? | Excel Programming | |||
I can import Access Tables. But, I can't import Access queries | Excel Programming |