Using VBA to Download a CSV file from IBM COGNOS in JAVASCRIP.

[rossmcn1]

Hi

I am very new to VBA, and I am using a working model to learn as I go. So appologies for my lack of correct terminology etc.

Basically I am trying to download sales figures from our IBM COGNOS sales database using VBA code.

I have no issue opening the page with the data I want.

Using:

Sub OpenPage()
Set browser = CreateObject("InternetExplorer.Application")
browser.navigate ("http://blahblahblahblah")
browser.StatusBar = False
browser.Toolbar = False
browser.Visible = True
browser.Resizable = False
browser.AddressBar = False
End Sub

This brings up a page with a table.

Manually, I can download a .csv file to bring this into excel for manipulation.
This is done by clicking on a button which brings up a prompt asking if I want to open or save the ppweb.csv file. (I want to save it, lets say to the desktop for now)

I am assuming this is all now in JavaScript (the icon is a gif button). Therefore when I run a recorded macro whilst manually clicking to save the ppweb.csv file, it brings up this module code:

Workbooks.Open filename:= _
"http://blahblah"
ActiveWindow.Visible = False
Windows("ppdscgi.exe").Visible = True
End Sub


When i right click/properties onto the gif icon on the COGNOS page the URL for the icon is the same as the purple above.

The trouble I encounter is then when I re-run the code, it brings up a COGNOS webpage which is not related (I assume it is bringing up the ppdscgi.exe) It is not leaving me with an open .csv file from which I am easily able to manipulate the data.


Is there anyway I can complete what I want to do fully automatically?
The URL is an intranet, password incripted site, and therefore these links will not work. Therefore, sorry if you don't have enough information to complete this request.

Any help you could provide me would be greatly welcome.

Kind regards

Ross

 

[Cindy Ellis]

Hi Ross, and Welcome to MrExcel,
I happen to work for the same parent organization as you
On that note, even though the links you provided are to password-protected information, it's probably not a good idea to post those kinds of links without changing the company name part to "xxxxx" or something like that. If I can figure out how, I'll ask the administrator to edit that part out of the link.

Regarding your question, the problem with what you're trying to do is that much of the link includes specific "session" information relative to your particular login instance. If you were to log back in and re-record it, I'm pretty sure the particulars of the link will be different.

I've only used our Cognos apps a few times (and not the particular system you're accessing), but if I recall, there is a way to save your report parameters. This won't automate it completely, but once you have the exported csv file of a consistent format, you can certainly use the macro capabilities of VBA to analyze the data.

Sorry I can't be of more help,

Cindy

 

[rossmcn1]

Cindy thanks,

That is great.

Hadn't even realised it had a company link in that URL! Woops!

Thanks for your information. It will really help. As long as it is automated to the maximum degree, we will still have a use for it.
You are correct, you can manipulate COGNOS to generate a set format.

Just wondered whether you could engage the JavaScript to perform the task.

Thanks for your reply.

Much Appreciated

ross