• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help regarding Web Query **might be non - excel question**

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


I need help regarding web query.

I am trying to import data from BSE.


PFB the link.


http://www.bseindia.com/markets/Equity/EQReports/MarketWatch.aspx?expandable=2


If I run this query, I get the data perfectly.

The problem is there more than 23 such tables available on this link, Page 1,2,3,4,... so on.


If we click on this link, we can see the page 1 only, if we click on page 4 or 8 or 10 also, the link does not get changed.


How do I import data from page 2 and so on.

By defualt the link imports data for page 1 only.


Is is out of scope for VBA, moderator sorry if I have asked the wrong question.


Please delete if you have to.
 
Hi, sachinbizboy!


That's because the site doesn't expose the data in tables in a way that a web query could retrieve it easily but instead of that it handles pages thru javascript, that's why you don't see an URL change.


In this case you're lucky, if you check at top right there's an option for exporting data to Excel (in fact in .csv format), I run it and retrieved 2443 rows. Is that what you're looking for?


Regards!
 
Hi SirJB7, good morning & thanks a lot for the help.


Yes Sir, I want that data of 2443 rows, but I can not download that .csv by VBA.


If I could download that .csv file by VBA that solves all the problems.


But for downloading the .csv file, as you said "handles pages thru javascript, that's why you don't see an URL change".


Sir is there any way that I could download this file by VBA.

The end user do not want to download this .csv file manuaaly, he want to be downloaded auto.


Have a nice day.
 
Hi, sachinbizboy!

Couldn't find out any way to obtain a link for download the file, it's handled directly by the code and it can't be trapped to bypass JavaScript procedures, so you'll have to manually download it.

Regards!
 
Hi Sachin ,


Can you try this ?

[pre]
Code: 
Private Sub Download_File()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

With IE
.Visible = False
.Navigate "http://www.bseindia.com/markets/Equity/EQReports/MarketWatch.aspx?expandable=2"

'        Wait while IE loading...
Do While .Busy
Application.Wait DateAdd("s", 1, Now)
Loop

.document.getElementById("ctl00$ContentPlaceHolder1$imgDownload").Click
End With

'Here is where the windows pop up box opens asking to open save or cancel
Application.Wait (Now + TimeValue("0:00:02"))
SendKeys "%S"

Application.Wait (Now + TimeValue("0:00:05"))
SendKeys "{ENTER}"

Application.Wait (Now + TimeValue("0:00:02"))
SendKeys "{ESC}"

' Clean up
Set IE = Nothing

End Sub
[/pre]
Not very reliable , but seems to work.


Narayan
 
SirJB7 no problem at all, actually this is the best forum with best people who are helping me immensely since last 2 years .

Sorry for late reply, have a nice day Sir.


Narayan Sir, thanks a lot for the help, I am trying this, will share the results ASAP.

Have a nice day.
 
Sir, I tried running this macro, but I am not getting any window pop-up.

I am using excel 2010, I pasted this code in the "Modules" in the code window.


The code does not give any bug, but not producing any results.


I also pasted this code in "This workbook" but same thing happens.


Could you please tell what I am doing wrong.
 
Hi Sachin ,


This code works for me in any sheet section , as well as in the ThisWorkbook section.


One thing can be that your IE security settings may be preventing the file download ; do you get the Information bar in IE asking for your permission to download ?


Try adding the URL to the list of Trusted Sites , and then check whether the following two settings are checked in your IE security settings for Trusted Sites :


1. Automatic prompting for ActiveX controls - Enable


2. Automatic prompting for File downloads - Enable


Narayan
 
Sir, thanks a lot for the help.


I have done the changes as per your advice.

The BSE page is getting opened now, but I am getting a bug.


Run Time Error 2147417848 (80010 108)


Automation error

The object invoked has disconnected from its client.


I do not know what is this, might be cause of my pc settings.

I will do this at home & share the results.


Once again, thanks a lot for the help & your valuable time.


Have a nice day.
 
Sir, I tried running this code.

Now the page is getting opened, after I save the file on desktop the code gives me following bug.


Run Time error 2147467259 (80004005)


Method 'Document' of object 'IWebbrowser2' failed.


Could you please advice.
 
Hi Sachin ,


I'll try and find out , but in the meantime , can you download this file , run the same procedure from within it , and see what happens ?


https://www.dropbox.com/s/fxjp5udde1h34xn/IE_Automation.xlsm


Narayan
 
Sir, sure I will download the file & share the results.


P.S. - I will download the file at home so I might be late in my reply.


Have a nice day ahead !
 
Sir I am using Windows server 2008 R2 Enterprise.


Will this help, actually I am using thin client & not able to see "My Computer" icon.
 
@NARAYANK991

Hi!

That was my first choice when I tried to download the file but since the object is an .gif icon (excelicon.gif) which doesn't have a OnClick procedure so it didn't worked for me during the tests.

I now tested your uploaded file and I get a 424 object required error in the ".document..." statement. Checked the references and none is missing.

Regards!
 
Sir, I have just downloaded the file, will share the results ASAP.


Thanks a lot for the help & your valuable time.


Good night to you and SirJB7.


P.S. - so the secret is out, SirJB7's real name is Pablo :-)
 
Good morning Sir, could not reply earlier due to sickness.


The macro is working at home, but does not work at office.

Must be because of the pc settings at the office.


When I tried the macro at home, it gives me a dialog box to save the file.

I can see this box ony after minimizing all the windows.


Please advice me if I am getting correct results or do you see something different.


Thanks a lot for the help & your valuable time.
 
Hi, sachinbizboy!

I can't get it work at any of four computers, 3 with Win7x64 and Office 2010x86, 1 with Win8x64 and Office 2013x86. What are the difference of environments between our work and office machines, would you please detail OS and Office version? Thanks!

Regards!


@NARAYANK991

Hi!

Same question as for sachinbizboy, would you please post in which environments have you tested it working, and if at any didn't work too? Thanks!

Regards!
 
You must log in or register to reply here.
Back
Top