Need help regarding IE Automation using VBA

[image_1]

I have to download a csv file from a website like 50 times a day. I want to automate the process using vba so that i can process it later in excel.
the details are :
website: http://www.nrldc.org/WBS/injsch.aspx
csv file to download labelled by : (ncr)
here is what i have tried but it is not working. need help



Sub Update()
Dim IE As Object
On Error GoTo error_handler
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate "http://www.nrldc.org/WBS/injsch.aspx"
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
.Visible = True
End With
For Each lnk In objIE.document.Links
If (InStr(lnk, "(ncr)")) Then
'Code to be executed in case it's a good link
objIEPage.Navigate lnk
While objIEPage.Busy
DoEvents
Wend
End If
Next lnk


objIE.Quit
Set IE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set IE = Nothing
End Sub

 

[image_1]

and ya link to the file is a javascript command. Dont know how to deal with that

 

[Norie]

The links are actually triggering some ASP code as far as I can see.

Something like calling code on the server side that then start's the download.

What part of the code you have isn't working?

 

[image_1]

@ norie

thx for the reply

ya i am trying to open following url:
http://www.nrldc.org/WBS/injsch.aspx
it does not require any authentication you can have a look its a simple page.
and i m successful in opening the page in an ie window through vba
but i am not able to download the csv file from it with text "(ncr)"
i will appreciate any help regarding downloading this file from the website.

 

[Norie]

I have looked at the link and it's doing what I said in my post.

When you click any of the links in red at the top some server side code is being triggered.

It's that code that's triggering the download, and as far as I can see it also creates the file to download.

I've used that sort of thing myself in the past to 'render' data from controls (eg GridView, ListView) to create a file.

So what I'm basically saying is there isn't really an existing file to download.

I hope that makes some sort of sense.

 

[image_1]

@ norie

ya tht does makes sense but i need tht file created after triggering of some code (as you mentioned ).tht data is the same as selected from the drop down menus on the site.
how can i get tht data (in the form of file or in any other way)without clicking on the link through vba?

 

[Norie]

I'm afraid the answer is you can't.

Well I couldn't find anyway to do it anyway.

At first thought that the data displayed was the data that was being downloaded, but it's not.

I found that out by actually downloading a couple of examples.

Is what's in that table just some sort of summary?

I tried changing the dropdowns to see if it changed the data but it didn't seem to.

Mind you I've no idea what the data is.

 

[image_1]

well the downloaded data(in csv format) is a collection of all the dropdown menus. the data is same. if there is a way to gather displayed data from page by selecting drop down menus it will also solve the prob.
plz reply if u know means to do so. i was searching on net and got something like this , but still not working :

['myURl = "__doPostBack('downloadncr','')"
'myIE.Navigate myURl
 
'Or
 
'myIE.Document.parentwindow.execscript "__doPostBack('downloadncr','')"
 
'Or
 
'myIE.Document.all("downloadncr").Click

red links at top : javascript:__doPostBack('downloadncr','')
as the red links at top execute some sort of java script so may be if we can execute tht script through vba but not successfull yet.
any help will be apprteciated thx

 

[headhair]

Hi image_1

Is this is the code you want?

Sub Update()
'Add YOUR website
'to Internet Explorer
'trusted website

'Open IE-Internet Options-Security-Trusted Sites-Sites
'then YOUR site

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
IE.Visible = True

'To open the website
.navigate "http://www.nrldc.org/WBS/injsch.aspx"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop

'To download the file
IE.navigate "javascript:__doPostBack('downloadncr','')"
Do While .Busy: DoEvents: Loop
End With

End Sub

 

[Norie]

image_1

Sorry for not replying earlier, if you can display all the data you want by going through all the selections and dropdowns then it might be possible to
get all the data.

It would however be a bit time consuming.

However another board member recently posted some code that uses Windows API to deal with the download dialog box.

I've not really tried it much but if I can find the code I'll give it a go.

Are you still interested?

headhair

I think the main problem, and it's quite a common one, is dealing with the download dialog.

The code you've posted is ideal for getting to the dialog stage but to actually have the download 'work' then a little more is needed.

That's where the method I mentioned above comes into it.

Fingers crossed I'll be able to find it.