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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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. "ryguy7272" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Two people have already answered your question. I posted before and
you didn't look at the code. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
It is very weird. Im 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 cant figure out why it will not do the import, but rather copy/paste the macro, itself into the activesheet. Im thinking that there must be another way of doing this. Do you have any alternative ideas Joel? Thanks, Ryan--- -- RyGuy "Joel" wrote: 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. "ryguy7272" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Thanks! I fiddled with all that code from 2/20/09; tried to get it working,
but I couldnt get anything going. Rons 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. Ill Google for an answer; Ill definitely do a little ore research on this. I would surmise that the issue is with the €˜secure site. Ive imported web data hundreds of times before, all from non-secure sites, and never experienced the problems that Im experiencing now. Ill bet anything that Excel can do it, I just dont know enough about it to do it myself. I dont even know enough about secure sites to modify the samples of code that I received. Thanks, Ryan--- -- RyGuy " wrote: Two people have already answered your question. I posted before and you didn't look at the code. |
Reply |
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 |