Web Import

[ryguy7272]

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/admi...n=com_rsform&task=submissions.manage&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---

 

[Don Guillett]

Try this.
Use your macro to log on and establish the query.
Now record a macro while doing a refresh instead of an add.

example
With ActiveSheet.QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/os?s=" & sym & "&m=" &
mdate
.Refresh BackgroundQuery:=False
End With

 

[gimme_this_gimme_that]

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 = "(e-mail address removed)"
msg.To = "(e-mail address removed)"
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")

 

[oitbso]

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---

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

 

[ryguy7272]

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/admi...n=com_rsform&task=submissions.manage&formId=2" _
, Destination:=Range("A1"))
.Name =
"index.php?option=com_rsform&task=submissions.manage&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---

 

[oitbso]

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.manage&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




















- 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.manage&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

 

[ryguy7272]

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---

 

[oitbso]

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 clickedTools >
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












...

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

 

[ryguy7272]

Ah ha!! Finally got it. Your code + my own, yielded the results I was
after. Thanks so much Ron!!

Regards,
Ryan---

 

[Don Guillett]

For the sake of the archives, post your final.

 

[ryguy7272]

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/admi...n=com_rsform&task=submissions.manage&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/admi...n=com_rsform&task=submissions.manage&formId=2" _
, Destination:=Range("A1"))
.Name =
"index.php?option=com_rsform&task=submissions.manage&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 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?


Thanks so much,
Ryan-----

 

[oitbso]

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

 

[ryguy7272]

I can see a tremendous benefit in understanding these techniques here, but
still am not quite getting it. I’d like to try this one more time, please.
I believe the current, and final, challenge is to log into this secure web
site (I’ve 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/admi...n=com_rsform&task=submissions.manage&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/admi...n=com_rsform&task=submissions.manage&formId=2" _
, Destination:=Range("A1"))
.Name =
"index.php?option=com_rsform&task=submissions.manage&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 don’t want an
Autpen 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 can’t 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---

 

[oitbso]

When I open my WB and run the code nothing happens.  I don’t want an

Autpen 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

 

[ryguy7272]

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/admi...n=com_rsform&task=submissions.manage&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 I’m
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. I’d 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.

(e-mail address removed) (remove the x)

Thanks,
Ryan---

 

[oitbso]

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

 

[ryguy7272]

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...

 

[ryguy7272]

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---

 

[ryguy7272]

Hummm, I could swear that I posted back this afternoon, but as I look now, I
don’t 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---