Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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_rsform&task=submissions.manag e&formId=*" _ , 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Perhaps you could translate this VBScript into VBA and swap out the
generic parameters for those for your site. ryan123/ryan123 isn't working for me. This script doesn't do *exactly* what you want - but it might be enough to get you started if you know VBA. YMMV. Dim IE,doc Sub Main(logon,password) WScript.Sleep(1000) Set IE = CreateObject("InternetExplorer.Application") WScript.Sleep(1000) IE.Visible = False title = Navigate("http://www.somesite.com") If DoMatch(title,"Login") Then Set doc = IE.Document Set form = doc.forms("login_form") form.elements("logon").value = logon form.elements("password").value = password form.submit Do while IE.Busy Loop title = IE.Document.title If DoMatch(title,"Home") Then ' user logged in normally PeruseHome Navigate ("http://www.somesite.com/LOGOUT/") Else ' user can't log in Notify() End If Else ' user is probably already logged in and is on the home page If DoMatch(title,"Home") Then PeruseHome 'logout Navigate ("http://www.somesite.com/LOGOUT/") End If End If IE.Quit Set IE = Nothing End Sub Sub WriteToFile(fileName,text) Set wFSO = CreateObject("Scripting.FileSystemObject") Set wFile = wFSO.OpenTextFile(fileName, 2 , True) wFile.WriteLine text wFile.Close End Sub Function DoMatch(strInput, strPattern) Dim re : Set re = New RegExp re.IgnoreCase = True re.Global = True re.Pattern = strPattern DoMatch = re.Test(strInput) re.Pattern = "" End Function Function Navigate(url) IE.Navigate url Do while IE.Busy Loop WScript.Sleep(2000) Navigate = IE.Document.title End Function Sub PeruseHome text = doc.body.innerText If DoMatch(text,"Navigation") OR DoMatch(text,"error has occurred") Then 'Do Nothing when there is an error Else 'Write file when things are okay WriteToFile "C:\_sps.txt",text End If End Sub Sub Notify() Set msg = CreateObject("CDO.Message") msg.Subject = "Monitoring username/password on sps.accountemps.com is failing" msg.From = " msg.To = " msg.TextBody = "This message was sent from an automated script." msg.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/ configuration/sendusing") = 2 msg.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/ configuration/smtpserver") = "hqpwtload.corp.rhalf.com" msg.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/ configuration/smtpserverport") = 25 msg.Configuration.Fields.Update msg.Send End Sub Call Main("someusername","somepassword") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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_rsform&task=submissions.manag e&formId=2" _ , 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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. *I’ve done this many times before, but can’t seem to get it working this time. *I tried the examples that people posted here, but couldn’t 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 doesn’t work any longer, even if I am still logged into the web site! *That’s why I am posting here; I don’t 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 doesn’t 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 DoEvents Loop Else End If ' Navigate to the desired page .navigate "http://www.countrybobsdemo.com/administrator/ index.php?option=com_rsform&task=submissions.manag e&formId=2" ' this should all be on 1 line Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop ' 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 .Quit End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 DoEvents Loop Else End If ' Navigate to the desired page .navigate "http://www.countrybobsdemo.com/administrator/ index.php?option=com_rsform&task=submissions.manag e&formId=2" ' this should all be on 1 line Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop ' 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 .Quit End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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!! *I’ve 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. *I’ve done this many times before, but can’t seem to get it working this time. *I tried the examples that people posted here, but couldn’t 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 doesn’t work any longer, even if I am still logged into the web site! *That’s why I am posting here; I don’t 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 doesn’t 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 ie.ExecWB 12, 0 select all of the web page, and then copy it to the clipboard. Sometimes, depending what VB references you have set, there can be a late / early binding issue. Try Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy DoEvents Loop and ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT in place of the 3 lines discussed above and see if it runs. The macro continues to run without problem on my pc...ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Right, I always post my final results, and will do so now. There is a pretty
big caveat though; code does NOT work as I though it did. Below is what I am working with now (sans comments): Sub Macro1() Set ie = CreateObject("InternetExplorer.Application") With ie ..Visible = True ..navigate "http://www.countrybobsdemo.com/administrator/" ..Top = 100 ..Left = 100 ..Height = 400 ..Width = 400 Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop 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 Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop Else End If ' Navigate to the desired page ..navigate "http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" ' this should all be on 1 line Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" _ , Destination:=Range("A1")) .Name = "index.php?option=com_rsform&task=submissions.mana ge&formId=2_76" .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 ..Quit End With End Sub This works perfectly fine if I clear all the data from the previous run, do the import, clear, import, and so on. However, it does not do the initial import if I close the WB and reopen it. I guess Excel cant establish the initial connection to the secured site! If I record a macro during my login steps, and copy/paste the web import under Rons original code, things are fine. Again, if I close the WB and open it, Excel doesnt seem to know how to find the web site. I suspect this has something to do with the login/security. Ive done web imports many times before without a problem, but all prior experience is with non-secure web sites. Ron, any other ideas on how to establish a connection when the WB is opened? Anyone? Thanks so much, Ryan----- -- RyGuy "ryguy7272" wrote: 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 - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
On Feb 23, 9:14*am, ryguy7272
wrote...snip... This works perfectly fine if I clear all the data from the previous run, do the import, clear, import, and so on. *However, it does not do the initial import if I close the WB and reopen it. *I guess Excel can’t establish the initial connection to the secured site! *If I record a macro during my login steps, and copy/paste the web import under Ron’s original code, things are fine. *Again, if I close the WB and open it, Excel doesn’t seem to know how to find the web site. *I suspect this has something to do with the login/security. *I’ve done web imports many times before without a problem, but all prior experience is with non-secure web sites. *Ron, any other ideas on how to establish a connection when the WB is opened? *Anyone? Ryan...The macro is not coded to auto run upon workbook open. You would need to use an "on workbook open" event handler to accomplish this. You would also need additional code to clear out the old data prior to import or identify another location to import to if you want to keep the old import data. BTW, were you unable to get the following copy /paste sequence to work? ' Copy the entire web page and then paste it as text into the worksheet ie.ExecWB 17, 2 ie.ExecWB 12, 0 Range("A1").Select ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False Range("A1").Select ....ron |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
I can see a tremendous benefit in understanding these techniques here, but
still am not quite getting it. Id like to try this one more time, please. I believe the current, and final, challenge is to log into this secure web site (Ive done this many times before; each time I was on a non-secure site, and everything worked fine). Here is my code: Sub test() Set ie = CreateObject("InternetExplorer.Application") With ie ..Visible = True ..navigate "http://www.countrybobsdemo.com/administrator/" ..Top = 100 ..Left = 100 ..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 ' Navigate to the desired page ..navigate "http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" ' this should all be on 1 line Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop ' Copy the entire web page and then paste it as text into the worksheet 'ie.ExecWB 17, 2 'ie.ExecWB 12, 0 'Range("A1").Select 'ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False 'Range("A1").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" _ , Destination:=Range("A1")) .Name = "index.php?option=com_rsform&task=submissions.mana ge&formId=2_62" .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 ..Quit End With End Sub When I open my WB and run the code nothing happens. I dont want an Auto_Open or On-Open event. I just want to open the WB, click the Command Button, and have the code log me into the web site. This step seems to fail every time. However, if I record the steps, as such: http://www.countrybobsdemo.com/administrator/ Login = Ryan Password = ryan123 Components RSForm!Pro Manage Submissions Import...then everything works just fine the next time I run the code. Next, if I log into the web site, clear the sheet, and re-query for data, the data updates just fine. The problem is the initial login. I cant seem to establish the initial connection to the data unless I go through the steps just described above, and that kind of nullified the point of setting up the macro to do this for me. Any other ideas Ron? BTW, I was never able to get this part to work: ie.ExecWB 17, 2 ie.ExecWB 12, 0 Range("A1").Select ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False Range("A1").Select Thank for everything, Ryan--- -- RyGuy " wrote: On Feb 23, 9:14 am, ryguy7272 wrote...snip... This works perfectly fine if I clear all the data from the previous run, do the import, clear, import, and so on. However, it does not do the initial import if I close the WB and reopen it. I guess Excel cant establish the initial connection to the secured site! If I record a macro during my login steps, and copy/paste the web import under Rons original code, things are fine. Again, if I close the WB and open it, Excel doesnt seem to know how to find the web site. I suspect this has something to do with the login/security. Ive done web imports many times before without a problem, but all prior experience is with non-secure web sites. Ron, any other ideas on how to establish a connection when the WB is opened? Anyone? Ryan...The macro is not coded to auto run upon workbook open. You would need to use an "on workbook open" event handler to accomplish this. You would also need additional code to clear out the old data prior to import or identify another location to import to if you want to keep the old import data. BTW, were you unable to get the following copy /paste sequence to work? ' Copy the entire web page and then paste it as text into the worksheet ie.ExecWB 17, 2 ie.ExecWB 12, 0 Range("A1").Select ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False Range("A1").Select ....ron |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
When I open my WB and run the code nothing happens. *I don’t want an
Auto_Open or On-Open event. *I just want to open the WB, click the Command Button, and have the code log me into the web site. * Ryan, what is the "command button", is it a macro button, i.e. click it and the macro runs? If you open the VB editor and step through your code, does it execute all of the lines, but nothing gets pasted in your workbook? When I run your code, nothing gets pasted into my workbook either. The following section of your code is not importing anyhting. I think you need to replace it with my piece of code right above it that you have commented out. Again, with my code, I get the desired import every time. In addition to the more standard references (VB - Tools - References), I have references set to Microsoft Office 10.0 Object Library and Microsoft Forms 2.0 Object Library. Make sure you have these references set. Remove your code, un comment mine and let me know what error you get...Ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Rob, when I run your code from 2/22/2009 1:24 PM PST, as it is, nothing
really happens. No import, no update of any kind; that is why I put my: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" _ , Destination:=Range("A1")) 'etc., etc., etc. under your code. Then, it worked, but only when I was already logged into the web site. I say nothing really happened, but hitting Ctrl+End one time took me to Cell Q18, the end of the used range. Then I added a Worksheet, and ran the macro again (on the ActiveSheet) and hit Ctrl+End and the Active Cell was A2!! The end of the used range is now A2!! This is totally bizarre as far as Im concerned, unless Excel really is importing nothing at all€¦ The results are too inconsistent to conclusively determine what is going on. If I activate any of those sheets (where the end of the used range was Cell Q18 or Cell A2), and select all cells, between A and 1, and hit delete, Excel tells me that the data is associated with an external source. Do you want to delete€¦ So, I know something was imported, but it seems to be just nulls, or whatever, nothing really. Even when I change the cell color, hit Ctrl+~, nothing is there. If I change the color of the font, nothing is there. I'm on a computer at my school now; same result. I guess this is not a problem specific to my home PC (ThinkPad). I get no results when I run it from a button, but one time I did get results when I walked through, via F8, line by line. However, this is really formatted much different than what is on the web site; everything is stacked in ColumnA so I have several hundred rows and not just 9 to 10 rows like to see on the web site. Id probably have to do much more VBA work to get it in the format that is useful to me. When I deleted everything in the sheet and stepped through the code, nothing was imported. Again, too inconsistent to make a determination. Ron, can you send me the WB that you have? Maybe the answer is in there somewhere. (remove the x) Thanks, Ryan--- -- RyGuy " wrote: When I open my WB and run the code nothing happens. I dont want an Auto_Open or On-Open event. I just want to open the WB, click the Command Button, and have the code log me into the web site. Ryan, what is the "command button", is it a macro button, i.e. click it and the macro runs? If you open the VB editor and step through your code, does it execute all of the lines, but nothing gets pasted in your workbook? When I run your code, nothing gets pasted into my workbook either. The following section of your code is not importing anyhting. I think you need to replace it with my piece of code right above it that you have commented out. Again, with my code, I get the desired import every time. In addition to the more standard references (VB - Tools - References), I have references set to Microsoft Office 10.0 Object Library and Microsoft Forms 2.0 Object Library. Make sure you have these references set. Remove your code, un comment mine and let me know what error you get...Ron |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
On Feb 23, 4:40*pm, ryguy7272
wrote...snip... However, this is really formatted much different than what is on the web site; everything is stacked in ColumnA so I have several hundred rows and not just 9 to 10 rows like to see on the web site. *I’d probably have to do much more VBA work to get it in the format that is useful to me. * Not really, just change ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False to ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False When I deleted everything in the sheet and stepped through the code, nothing was imported. *Again, too inconsistent to make a determination. *Ron, can you send me the WB that you have? *Maybe the answer is in there somewhere. I didn't save a workbook, I just copy and paste my code from above into a module in a new WB and it runs everytime. Did you check the references that you have set?..Ron |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
All appropriate references are checked off; thanks for the tip. You know
wnat it does...I noticed this before but didn't mention it...it pastes, whatever is in the memory, into the active sheet. I followed your suggestion and changed: ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False to ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False I then run the code and what do I see in the active sheet... ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False Although it opens a new IE browser, fills in the username and password and naviagtes to the page I am interested in, it doesn't actually import anything. Instead of doing the import, it just pastes whatever is on the clipboard into the active sheet... I am out of ideas... -- RyGuy " wrote: On Feb 23, 4:40 pm, ryguy7272 wrote...snip... However, this is really formatted much different than what is on the web site; everything is stacked in ColumnA so I have several hundred rows and not just 9 to 10 rows like to see on the web site. Id probably have to do much more VBA work to get it in the format that is useful to me. Not really, just change ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False to ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False When I deleted everything in the sheet and stepped through the code, nothing was imported. Again, too inconsistent to make a determination. Ron, can you send me the WB that you have? Maybe the answer is in there somewhere. I didn't save a workbook, I just copy and paste my code from above into a module in a new WB and it runs everytime. Did you check the references that you have set?..Ron |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Ok Ron, I don't know what was causing the import process to fail before, but
I tried your code a few times this AM, with a fresh Excel workbook, and it seems to be working ok now, except for one thing. It imports every element in ColumnA, but what I really want to do is import the text on the screen, just as it is. I tried changing this: ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False to ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False It just imports everything into ColumnA, as HTML. I tried this too: ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False That just causes the code to fail and I have to go to the VBE to reset the Sub. At this point I'm pretty much convinced that I can import the text from the web browser, with the columns and rows aligned just as they are on the web page. I can't figure out the syntax for that though. Do you have any idea how I can do that? Thanks so much for everything, Ryan--- -- RyGuy "ryguy7272" wrote: All appropriate references are checked off; thanks for the tip. You know wnat it does...I noticed this before but didn't mention it...it pastes, whatever is in the memory, into the active sheet. I followed your suggestion and changed: ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False to ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False I then run the code and what do I see in the active sheet... ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False Although it opens a new IE browser, fills in the username and password and naviagtes to the page I am interested in, it doesn't actually import anything. Instead of doing the import, it just pastes whatever is on the clipboard into the active sheet... I am out of ideas... -- RyGuy " wrote: On Feb 23, 4:40 pm, ryguy7272 wrote...snip... However, this is really formatted much different than what is on the web site; everything is stacked in ColumnA so I have several hundred rows and not just 9 to 10 rows like to see on the web site. Id probably have to do much more VBA work to get it in the format that is useful to me. Not really, just change ActiveSheet.PasteSpecial Format:="text", Link:=False, DisplayAsIcon:=False to ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False When I deleted everything in the sheet and stepped through the code, nothing was imported. Again, too inconsistent to make a determination. Ron, can you send me the WB that you have? Maybe the answer is in there somewhere. I didn't save a workbook, I just copy and paste my code from above into a module in a new WB and it runs everytime. Did you check the references that you have set?..Ron |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Import
Hummm, I could swear that I posted back this afternoon, but as I look now, I
dont see it. Anyway, as I mentioned before, I finally got the import process working and now I am wondering if there is a way to get the results from the web page imported into Excel, as it appears in the browser. Now, when the data is imported, everything is stacked up in Column A. I am hoping to find a way to do this import so the rows and columns come in as they are in the browser. Ron, do you have any ideas about how to do this? Thanks so much for everything!! Ryan--- -- RyGuy "ryguy7272" wrote: Rob, when I run your code from 2/22/2009 1:24 PM PST, as it is, nothing really happens. No import, no update of any kind; that is why I put my: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.countrybobsdemo.com/administrator/index.php?option=com_rsform&task=submissions.manag e&formId=2" _ , Destination:=Range("A1")) 'etc., etc., etc. under your code. Then, it worked, but only when I was already logged into the web site. I say nothing really happened, but hitting Ctrl+End one time took me to Cell Q18, the end of the used range. Then I added a Worksheet, and ran the macro again (on the ActiveSheet) and hit Ctrl+End and the Active Cell was A2!! The end of the used range is now A2!! This is totally bizarre as far as Im concerned, unless Excel really is importing nothing at all€¦ The results are too inconsistent to conclusively determine what is going on. If I activate any of those sheets (where the end of the used range was Cell Q18 or Cell A2), and select all cells, between A and 1, and hit delete, Excel tells me that the data is associated with an external source. Do you want to delete€¦ So, I know something was imported, but it seems to be just nulls, or whatever, nothing really. Even when I change the cell color, hit Ctrl+~, nothing is there. If I change the color of the font, nothing is there. I'm on a computer at my school now; same result. I guess this is not a problem specific to my home PC (ThinkPad). I get no results when I run it from a button, but one time I did get results when I walked through, via F8, line by line. However, this is really formatted much different than what is on the web site; everything is stacked in ColumnA so I have several hundred rows and not just 9 to 10 rows like to see on the web site. Id probably have to do much more VBA work to get it in the format that is useful to me. When I deleted everything in the sheet and stepped through the code, nothing was imported. Again, too inconsistent to make a determination. Ron, can you send me the WB that you have? Maybe the answer is in there somewhere. (remove the x) Thanks, Ryan--- -- RyGuy " wrote: When I open my WB and run the code nothing happens. I dont want an Auto_Open or On-Open event. I just want to open the WB, click the Command Button, and have the code log me into the web site. Ryan, what is the "command button", is it a macro button, i.e. click it and the macro runs? If you open the VB editor and step through your code, does it execute all of the lines, but nothing gets pasted in your workbook? When I run your code, nothing gets pasted into my workbook either. The following section of your code is not importing anyhting. I think you need to replace it with my piece of code right above it that you have commented out. Again, with my code, I get the desired import every time. In addition to the more standard references (VB - Tools - References), I have references set to Microsoft Office 10.0 Object Library and Microsoft Forms 2.0 Object Library. Make sure you have these references set. Remove your code, un comment mine and let me know what error you get...Ron |
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 |