mllmar039
asked on
Unable to open ... Cannot download the information
I am trying to add a web querry for http://www.etfsecurities.com/en/etfscalculations/etfspricing_f3.aspx and it seems to come up fine in the "NEW Web Querry" box and I select the table I want, but then get the above error when I try and import. Cannot figure out why that would be.
Web-Querry-Box.jpg
error.jpg
Web-Querry-Box.jpg
error.jpg
mllmar039,
I agree with Zwiekhorst that the site is blocking (whether accidentally by design).
Assuming that you can't get the site changed, there is a workaround. Click on the button in the attached file to download the whole page (to C:\Temp\Multiplier.html" - change as required). A web query is then run against that file to give you your data.
(The idea was lifted from http://forums.whirlpool.net.au/archive/1511427.)
Regards,
Brian.
Get-Multiplier.xlsm
I agree with Zwiekhorst that the site is blocking (whether accidentally by design).
Assuming that you can't get the site changed, there is a workaround. Click on the button in the attached file to download the whole page (to C:\Temp\Multiplier.html" - change as required). A web query is then run against that file to give you your data.
(The idea was lifted from http://forums.whirlpool.net.au/archive/1511427.)
Regards,
Brian.
Get-Multiplier.xlsm
ASKER
Hi redmondb,
Have attached the html file that is created, and hinestly it is outside my understanding. I have tried it on an XP machine and a Vista machine with the same result. Any ideas?
Multiplier.html
Have attached the html file that is created, and hinestly it is outside my understanding. I have tried it on an XP machine and a Vista machine with the same result. Any ideas?
Multiplier.html
mllmar039,
Sorry about that. I'm running Excel 2010 on Windows 7. I'll try it on an older version on XP.
In the meantime, assuming that you changed the location of Multiplier.html, could you post a copy of exactly what you ran, please?
Thanks,
Brian.
Sorry about that. I'm running Excel 2010 on Windows 7. I'll try it on an older version on XP.
In the meantime, assuming that you changed the location of Multiplier.html, could you post a copy of exactly what you ran, please?
Thanks,
Brian.
mllmar039,
... and which version of Excel are you running?
Regards,
Brian.
... and which version of Excel are you running?
Regards,
Brian.
ASKER
Running excel 2007 and left the path and code unchanged on the XP machine and changed the path to the c:/user/.../desktop/ for the permision restrictions on the vista machine. Let me know if you need anything more specific can paste the exact code
mllmar039,
Was your path a typo or did you really use "/"? :)
Maybe best if you post the file!
Thanks,
Brian.
Was your path a typo or did you really use "/"? :)
Maybe best if you post the file!
Thanks,
Brian.
mllmar039,
I think that's it. I changed mine to "C:/Temp/Multiplier.html" and got your error.
So, just change the path "/" to "\" (leave the url alone).
Regards,
Brian.
I think that's it. I changed mine to "C:/Temp/Multiplier.html" and got your error.
So, just change the path "/" to "\" (leave the url alone).
Regards,
Brian.
mllmar039,
OK, it's not quite that simple. My fault - I should have told you to update the Connection with the new file name and location.
I'm updating the file to make it easy for you to change any of the details.
Regards,
Brian.
OK, it's not quite that simple. My fault - I should have told you to update the Connection with the new file name and location.
I'm updating the file to make it easy for you to change any of the details.
Regards,
Brian.
ASKER
I dont think it is the "/" "\" problem, I thought it may have been a package for a function call that I do not have as default. I have found a workaround from a different site that was not exactly what I wanted, but with you code, just needed to modify in a small way.
'Written: October 29, 2009
'Author: Leith Ross
'Summary: Returns the contents of a web page as a string.
Function GetSourcePage(ByVal URL As String) As String
Dim IEapp As Object
Set IEapp = CreateObject("InternetExpl orer.Appli cation")
IEapp.Navigate URL
While IEapp.Busy
DoEvents
Wend
GetSourcePage = IEapp.Document.DocumentEle ment.outer HTML
IEapp.Quit
Set IEapp = Nothing
End Function
Then your part
Sub getData()
Dim fso: Set fso = CreateObject("scripting.fi lesystemob ject")
Dim newfile: Set newfile = fso.createtextfile("C:\Tem p\Multipli er.html", True)
newfile.write (GetSourcePage("http://www.etfsecurities.com/en/etfscalculations/etfspricing_f3.aspx"))
newfile.Close
End Sub
and seems to solve all issues
'Written: October 29, 2009
'Author: Leith Ross
'Summary: Returns the contents of a web page as a string.
Function GetSourcePage(ByVal URL As String) As String
Dim IEapp As Object
Set IEapp = CreateObject("InternetExpl
IEapp.Navigate URL
While IEapp.Busy
DoEvents
Wend
GetSourcePage = IEapp.Document.DocumentEle
IEapp.Quit
Set IEapp = Nothing
End Function
Then your part
Sub getData()
Dim fso: Set fso = CreateObject("scripting.fi
Dim newfile: Set newfile = fso.createtextfile("C:\Tem
newfile.write (GetSourcePage("http://www.etfsecurities.com/en/etfscalculations/etfspricing_f3.aspx"))
newfile.Close
End Sub
and seems to solve all issues
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hey, thanks mllmar039, that was generous of you!
ASKER
Works fine on my home PC so does work 100%, something is missing on my work PCs. Appreciate the help Brian
The builder made sure you can't use webquerry in this website to subtract info to excel...
Sorry that I can't help out but it seems to be impossible...
Kind regards
Eric