Solved

VBA control of a web page and downloading a csv file

Posted on 2010-11-08
6
1,199 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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 

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

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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