Create a password. For example, if you want currency exchange, you could make the URL something like. 'Excel for Mac allows you to import data from a web query,' No, it does not. I'm also an annoyed mac user. According to your description, you want to enter the username and password from Web Query. In fact, we can get the data from Web with Web Query in Excel. But I'm afraid Web Query doesn't support to pass the specific username and password into the target web page, because username and password are the custom properties for different web pages.
FPMonkeyorgangrinderNew Member
I read on the Microsoft website that you can't run a Web Query on a password protected website so I've developed my own. I post it so that it may inspire some of you. I trawled the net for weeks and found documentelement.innertext by pure accident. I must say that I am only an amature so I apologise for my untidy programming. You will need to add a 'Microsoft Web Browser' to your sheet and use the following code........ Const READYSTATE_COMPLETE As Long = 4 dim x as integer Public Sub WebQuery() URL = 'website to be queried' Set ie = Sheet1.WebBrowser1 ie.Visible = 1 DoEvents ie.Navigate URL Do Until ie.readystate = READYSTATE_COMPLETE DoEvents Loop With ie.Document.login .loginid.Value = 'username' .password.Value = 'password' .submit End With Do Until ie.readystate = READYSTATE_COMPLETE DoEvents Loop Application.Wait Now + Timevalue('00:00:03') HTMLdata = Sheet1.WebBrowser1.Document.documentelement.innertext HTMLdata = VBA.Split(HTMLdata, Chr(13)) 'change the split as required CHR(13) is for carriage return. For x = 0 To UBound(HTMLdata) Sheet1.Range('A' & (x + 1)) = HTMLdata(x) Next x End Sub You may need to change the HTML references for the username and password. You may also want to change the CHR(13) to whatever you need as the delimiter. I have a small delay in the code to enable the page to fully load but sometimes it is better to replace it with the READYSTATE_LOADING constant. Cheers. Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |