Solved

VBA control of a web page and downloading a csv file

Posted on 2010-11-08
6
1,168 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

910 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

25 Experts available now in Live!

Get 1:1 Help Now