VB Script newbie - SaveAs retrieved Document

[KieronShaw]

Hi Guys,

First of all apologies if this specific question has been asked elsewhere, I did a search but was unable to find anything that matches my requirements exactly.

I am new to Tek-Tips & fairly new to Visual basic, I have been playing around for a few months now but have only just taken the time to look into the possibilities.

My question is very simple I'm sure but as a complete novice who has gotten where he is with trial & error & a little bit of luck I've hit a bit of a stumbling block. Please find my Script below:

Set IE = CreateObject("InternetExplorer.Application")
set WshShell = WScript.CreateObject("WScript.Shell")
IE.Navigate "[URL unfurl="true"]http://172.19.70.3/ccc/login.do?action=welcomeLogin"[/URL]
IE.Visible = True
Wscript.Sleep 1000
IE.Document.loginform.userid.Value = "********"
IE.Document.loginform.password.Value = "**********"
WshShell.AppActivate "IE"
WshShell.SendKeys "{ENTER}"
IE.Navigate "[URL unfurl="true"]http://172.19.70.3/ccc/contextSwitch.do?action=searchACOSCustomer"[/URL]
IE.Navigate "[URL unfurl="true"]http://172.19.70.3/ccc/inbox.do?hdnAction=initialScreenDisplay"[/URL]
IE.Navigate "[URL unfurl="true"]http://172.19.70.3/ccc/inbox.do?hdnAction=exportToExcel&event=excel&origin=-1&email1=&forename=&surname=&workgroup=-1&advisor=-1&datefrom=22/08/2011&dateto=23/08/2011&num=&status=1&calldatetype=-1"[/URL]

As you can see this script essentially opens a web page & navigates to a page, logs in, navigates to a specific page & then 'Exports/Downloads' a document based on criteria embeded in the final URL.

This then opens the document in excel (normally under the name 'inbox.do' but this is not always the case), what I'm then required to do is save the document (name/location unimportant at the moment) so that I can then reference it in a later Excel document.

As I said I'm sure it's very easy to do but I'm struggling to get it to work!

Many thanks in Advance

 

[Geates]

If the save window pops up, use sendkeys to confirm? (Just a guess)

do : loop until wshShell.AppActivate "File Download"
WshShell.SendKeys "%S"

do : loop until wshShell.AppActivate "Save As"
WshShell.SendKeys "%S"

Also, shouldn't this line read WshShell.AppActivate "[red]Internet Explorer[/red]" and not WshShell.AppActivate "[red]IE[/red]"?

-Geates



"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[KieronShaw]

Hi Geates,

Thanks for your reply, as i said I am a complete novice at the moment when it comes to this kind of thing.

As far as that line goes "IE" seems to work but I've changed it to "InternetExplorer", this code was a copy/paste job which I manipulated to my needs.

The code you suggested won't work I don't think, there is no save option which pops up. Basically what I am left with at the end of the script is an Excel Document left open (Inbox.do) which I just want to SaveAs something else in a location (lets just say inbox.xls in C:\docs for arguments sake) & then close down. This is so I can then reference this document in another spreadsheet.


I'm sorry if I'm not explaining myself very well, as I said, complete novice!

 

[Geates]

Oh I see what you mean. I would think it would best be accomplished in the inbox.do web script by passing a "SaveAs" argument. Who built inbox.do? Can the code be modified?

IE.Navigate "[URL unfurl="true"]http://172.19.70.3/ccc/inbox.do?hdnAction=exportToExcel&event=excel&origin=-1&email1=&forename=&surname=&workgroup=-1&advisor=-1&datefrom=22/08/2011&dateto=23/08/2011&num=&status=1&calldatetype=-1[/URL][red]&saveas=[blue]%22[/blue]c:\docs\inbox.xls[blue]%22[/blue][/red]"

[blue]NOTE: use the URL encoded character %22 to pass quotes in a URL[/blue]

Or you could try this code. I put a "wscript.sleep 100" after each sendkeys to give the application time to react.

'bring focus to the file
do : loop until wshShell.AppActivate "inbox.do"

'open saveas dialog
wscript.sendkeys "{F12}"
wscript.sleep 100
 
'select name field
wscript.sendkeys "%n" 
wscript.sleep 100

'type name
wscript.sendkeys "inbox.xls" 
wscript.sleep 100

'select save button
wscript.sendkeys "%s"
wscript.sleep 100

'close Excel
wscript.sendkeys "%{F4}"

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer