Automate Schedule and Export Report to Excel

[Jimmy24]

I have a report in Cognos for Members Phone List. I want to automate the process and do the following:


1.Write a macro to automaticaly export the report to excel.
2.Create an schedule that run everyday at 9:00 am the automaticaly create the export that goes to excel.

Please any help will be strongly appreciate it.

Regards,

Jim

 

[drlex]

Jim,
Since you don't mention the application you're using, I can't suggest appropriate code to run the report. You might like to look at the support documentation (pdf format) for CognosScript, which is VBA-like, to be found in the documentation folder on your install. The file names are in 8.3 format and so are initially hard to determine.

The latest CognosScript one (supplied with the 7.4 products I have) is called 'cgspmac.pdf' and is an introduction to CognosScript, whilst Scheduler is described in another ('schd_mac.pdf').

If you prefer, you can always call compiled macros in Windows via a batch file according to a windows schedule or other automated process (e.g. SQL server xp_cmdshell)

C:
CD\Progra~1\Cognos\cer5\bin\
runmac32.exe "openlink test.mcx"

This runs a compiled macro called openlink test which is in the same pathway as the macro running executable.

I have found a need to process some reports in Excel, and this can be done within the same macro by calling the Excel application as an Object.

   Set objExcel = CreateObject("Excel.Application")
   Set objExcelOpen = objExcel
   objExcel.Visible = 1
   objExcel.Application.Workbooks.Open strreport 
   
   ...process report

   objExcel.Application.Workbooks(1).SaveAs strreport, Fileformat:=-4143
   objExcel.Application.Workbooks(1).Close 
   objExcel.Application.Quit

Happy friday,
lex

soi la, soi carré