Solved

VBA control of a web page and downloading a csv file

Posted on 2010-11-08
6
1,162 Views
Last Modified: 2012-05-10
I'm connecting to a java based website, filling in a webform and submitting the form for export to excel in a csv format. All is good up to that point where I need a way to have the file auto download.
My code so far gets me to the point where the file download box appears, giving me the option to open, save or cancel.
I want the code to save the file to my computer without using sendkeys to tab to the correct button on the popup. This is because I need the code to run, even if the computer screen is inactive (locked)

My code so far (i've removed the website and logon details for security):

Sub myTest()

Dim IE
Dim objShell

    Set IE = CreateObject("InternetExplorer.Application")
    Set objShell = CreateObject("WScript.Shell")
    With IE
        .Left = 50
        .Top = 20
        .Height = 540
        .Width = 950
        .MenuBar = 0
        .Toolbar = 1
        .StatusBar = 0
        .navigate ""     'website here
        .Visible = True
    End With

    'wait until IE has finished loading itself.
    Do While IE.ReadyState <> 4
        DoEvents
    Loop
   
With IE.Document
    .forms(0).adminname.Value = ""             'enter username
    .forms(0).Password.Value = ""           'enter password

End With

Do While IE.ReadyState <> 4
        DoEvents
    Loop

IE.navigate "javascript:submitForm()"

Do While IE.ReadyState <> 4
        DoEvents
    Loop
   
IE.navigate "" 'navigate to next page

Do While IE.ReadyState <> 4
        DoEvents
    Loop
   
With IE.Document
    .forms(0).Issuer.Value = "4897"                
End With

Call IE.Document.parentWindow.execScript("setExportAndSubmit(true); this.value='Processing'")       'submit the export command


End Sub
0
Comment
Question by:hybridnotts
  • 3
  • 3
6 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 34096630
It's pretty hard to tell if it's possible to help here, or even if it's possible to do, without more information.
It sounds like some code is being called and it's that code that is actually doing the download.
When you get the download dialog box does the file have a specific name based on what's been entered on the page, or is it just something generic like download.csv?
0
 

Author Comment

by:hybridnotts
ID: 34099877
I'm running IE8 and its just the standard file download box that pops up.
The javascript code i'm calling from the website is their download function which submits the form and prompts the dowload of a csv file. That filename differs dependent on the information on the form (see attached image).
download.bmp
0
 
LVL 33

Expert Comment

by:Norie
ID: 34101369
That's what I mean - the file has been generated by code.
It's sort of in memory rather than being an actual file, until you actually save it.
If it was just a link you were clicking there would normally be a filename/path etc that you could use to download the file with code.
With this kind of download I've never actually seen code that will do what you want.
You seem to have tried running the script but that script is just calling other code, it's the code that is being called that's actually doing the download part.
Do you know anything about that code? Is it on the page or is it server-side?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:hybridnotts
ID: 34101381
Unfortunately its server side and not something I have access to.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 34101483
Well the only other way I can think of would be to use the Windows API to somehow either open the file or save it.
With that you might be able to save the file with a name you know to a location you know and take things from there.
0
 

Author Closing Comment

by:hybridnotts
ID: 34101558
Although not hte answer I wanted, it is a definative answer and backed up my own ideas.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 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

9 Experts available now in Live!

Get 1:1 Help Now