Solved

VBA control of a web page and downloading a csv file

Posted on 2010-11-08
6
1,181 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

808 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