Solved

Unable to open ... Cannot download the information

Posted on 2011-03-16
13
470 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:mllmar039
  • 8
  • 4
13 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35146997
The problem is not with your excel but with the website.
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35147292
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
0
 

Author Comment

by:mllmar039
ID: 35147409
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35147481
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35148348
mllmar039,

... and which version of Excel are you running?

Regards,
Brian.
0
 

Author Comment

by:mllmar039
ID: 35148582
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:redmondb
ID: 35148610
mllmar039,

Was your path a typo or did you really use "/"?   :)

Maybe best if you post the file!

Thanks,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35148648
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35149259
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.
0
 

Author Comment

by:mllmar039
ID: 35149598
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("InternetExplorer.Application")
   
    IEapp.Navigate URL
   
    While IEapp.Busy
        DoEvents
    Wend
     
    GetSourcePage = IEapp.Document.DocumentElement.outerHTML
     
    IEapp.Quit
    Set IEapp = Nothing
End Function


Then your part


Sub getData()
    Dim fso: Set fso = CreateObject("scripting.filesystemobject")
    Dim newfile: Set newfile = fso.createtextfile("C:\Temp\Multiplier.html", True)
   
    newfile.write (GetSourcePage("http://www.etfsecurities.com/en/etfscalculations/etfspricing_f3.aspx"))
    newfile.Close
End Sub

and seems to solve all issues

0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 35149964
mllmar039,

Great! Glad you were able to sort it out yourself.

I don't think you have to worry about a missing function, though you do have to remember  (unlike me!) to make sure that the Connection is pointing to the new location.

All the best,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35151063
Hey, thanks mllmar039, that was generous of you!
0
 

Author Comment

by:mllmar039
ID: 35151094
Works fine on my home PC so does work 100%, something is missing on my work PCs. Appreciate the help Brian
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now