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

rossmcn1

New Member
Joined
Feb 22, 2011
Messages
3
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
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,560
Messages
6,179,519
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top