retrieve data with password

[Gregfox]

Hello,
I would like to use VBA code to get data from a web site. I know how to do this, but the site has a password in order to see the data. Does anyone have code to enter passwords, and then retrieve data?
Thank you.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Trevor G]

Try recording a Macro to use a Webquery and go to the site and enter your information to retrieve data, that will give you the VBA code and then in the code you would need to add the password details.

 

[Gregfox]

Thank you, but I've tried that, it's the password detail I need. In the macro, I go to the site, enter username and password, import the data, and all is well, until I log out and try it again, I get an hour glass, than nothing appears in the sheet.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Trevor G]

What code did you end up with?

Here is a sample that should work

[FONT=Calibri]Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = [COLOR=green]http://www.enteryourwebsiteaddresshere
[/COLOR]Const PostUser As String = "login=User Name" [COLOR=green]'Change user name here
[/COLOR]Const PostPassword As String = "&pass=password" [COLOR=green]'Change password here
[/COLOR]    
MyPost = PostUser & PostPassword
    
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
    
End Sub[/FONT]<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

 

[Gregfox]

Thanks, I'll try it!

 

[Gregfox]

Sub Login_WebQuery()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim MyPost As String<o></o>
Const MyUrl As String = http://www.ibm.com <Syntax error<o></o>
Const PostUser As String = "login=User Name" 'Change user name here<o></o>
Const PostPassword As String = "&pass=password" 'Change password here<o></o>
<o></o>
MyPost = PostUser & PostPassword<o></o>
<o></o>
With ActiveSheet.QueryTables.Add(Connection:= _<o></o>
"URL;" & MyUrl, Destination:=Cells(1, 1))<o></o>
.PostText = MyPost<o></o>
.BackgroundQuery = True<o></o>
.TablesOnlyFromHTML = True<o></o>
.Refresh BackgroundQuery:=False<o></o>
.SaveData = True<o></o>
End With<o></o>
<o></o>

End Sub
Thanks Trevor, but as noter above SYNTAX error on line 3

 

[Trevor G]

There must be another page or url line, as when I clicked the link it took me to http://www.ibm.com/us/en/sandbox/ver2/

Even when you type in the ibm web address it still shows the above in the URL line.

Could you use your web engine to give you the exact URL line..

 

[Gregfox]

I put the error line in "s and it lost the syntax error, but still didn't log in.
I got
User ID
Password
Case Sensitive

CODE FOLLOWS....
Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "https://onlineservices.wachovia.com...gin&url=/myAccounts.aspx?referrer=authService"
Const PostUser As String = "User ID=PLACEIDHERE" 'Change user name here
Const PostPassword As String = "&Password=PLACEPASSWORDHERE" 'Change password here

MyPost = PostUser & PostPassword

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MyUrl, Destination:=Cells(1, 1))
.PostText = MyPost
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

End Sub

 

[Trevor G]

Do you have to add a line for selecting your service selection from the drop down? So you might need that and state what the service you are wanting to use.

 

[Gregfox]

No, no drop down. Just the normal User ID and Password. I tried several other things , but still get User ID
Password