Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA control of a web page and downloading a csv file

Posted on 2010-11-08
6
Medium Priority
?
1,318 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 35

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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Accepted Solution

by:
Norie earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

885 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