Cognos ScriptEditor Macro - Sub with Variables 3

[ITUngaBunga]

Within Cognos ScriptEditor (Impromptu Version 7 Series 2), I have written some subs to perform a simple file copy.
However, it doesn't compile and I get the sub 'ArchiveDataFiles' highlighted in red.

All my Sub sets out to do is to copy two CSV files and dump them into an archive. I wrote the sub to be re-usable but not had much success.
BTW it is only the call to the sub 'ArchiveDataFiles' and that sub itself, that doesn't work. Take out this sub and the whole lot runs no bother!!
Is it because the sub has been written to take three variables?

I've posted the whole code/routine at the bottom of this msg, and would be very grateful to anyone who could point me off as to why it wont work!

Thanks in advance.

Regards

UB

'========Code Begins========
Global g_strWMSDailySalesData As String
Global g_strStylemanDailySalesData As String
Global g_strArchiveDataLocn as String

Declare Sub CreateWMSDailySales()
Declare Sub CreateStylemanDailySales()
Declare Sub ArchiveDataFiles

Sub Main()

g_strWMSDailySalesData = "\\ssoslapp\OSL_Reporting\Operations\DailySalesRec\wms_daily_sales.csv"
g_strStylemanDailySalesData = "\\ssoslapp\OSL_Reporting\Operations\DailySalesRec\styleman_daily_sales.csv"
g_strArchiveDataLocn = "\\ssoslapp\OSL_Reporting\Operations\DailySalesRec\DataArchive\"

Call CreateWMSDailySales
Call ArchiveDataFiles (g_strWMSDailySalesData, g_strArchiveDataLocn & "wms_daily_sales","csv")
Call CreateStylemanDailySales
Call ArchiveDataFiles (g_strStylemanDailySalesData, g_strArchiveDataLocn & "styleman_daily_sales","csv")

End Sub

Sub CreateWMSDailySales
Dim objImpApp As Object
Dim objImpRep As Object

Set objImpApp = CreateObject("CognosImpromptu.Application")

objImpApp.OpenCatalog "\\ssoslapp\Cognos\CATALOG\System Manager\WMS-LIVE.cat", "Creator"

Set objImpRep = objImpApp.OpenReport("\\ssoslapp\Cognos\Reports\Operatons\wms_daily_sales.imr")

objImpRep.Export g_strWMSDailySalesData, "x_ascii.flt"

objImpRep.CloseReport
rem objImpApp.Quit

Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

Sub CreateStylemanDailySales
Dim objImpApp As Object
Dim objImpRep As Object

Set objImpApp = CreateObject("CognosImpromptu.Application")

objImpApp.OpenCatalog "\\ssoslapp\Cognos\CATALOG\System Manager\Styleman.cat", "Creator"

Set objImpRep = objImpApp.OpenReport("\\ssoslapp\Cognos\Reports\Operatons\styleman_daily_sales.imr")

objImpRep.Export g_strStylemanDailySalesData, "x_ascii.flt"

objImpRep.CloseReport
rem objImpApp.Quit

Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

Sub ArchiveDataFiles(strFileForCopy As String, strDest As String, strExt As String)

Dim strFileToCopy as String
Dim strDestination as String
Dim strFileTag as String

On Error Resume Next

strFileTag = "_" & Format(Date,"YYYYMMDD")
strFileToCopy = strFileForCopy
strDestination = strDest & strFileTag & "." & strTag

FileCopy strFileToCopy, strDestination

End Sub
'========Code Ends========

 

[GJParker]

Hi UB

Your problem is when you declare the subroutines the line

Declare Sub ArchiveDataFiles

should be

Declare Sub ArchiveDataFiles(strFileForCopy, strDest As String, strExt As String)

There is also a problem with this line

strDestination = strDest & strFileTag & "." & strTag

strTag is not declared any where in the code, should strTag be strExt ?

'========Code Begins========
Global g_strWMSDailySalesData As String
Global g_strStylemanDailySalesData As String
Global g_strArchiveDataLocn as String

Declare Sub CreateWMSDailySales()
Declare Sub CreateStylemanDailySales()
Declare Sub ArchiveDataFiles(strFileForCopy As String, strDest As String, strExt As String)

Sub Main()

   g_strWMSDailySalesData = "\\ssoslapp\OSL_Reporting\Operations\DailySalesRec\wms_daily_sales.csv"
   g_strStylemanDailySalesData = "\\ssoslapp\OSL_Reporting\Operations\DailySalesRec\styleman_daily_sales.csv"
   g_strArchiveDataLocn = "\\ssoslapp\OSL_Reporting\Operations\DailySalesRec\DataArchive\"

      Call CreateWMSDailySales
      Call ArchiveDataFiles (g_strWMSDailySalesData, g_strArchiveDataLocn & "wms_daily_sales","csv")
      Call CreateStylemanDailySales
      Call ArchiveDataFiles (g_strStylemanDailySalesData, g_strArchiveDataLocn & "styleman_daily_sales","csv")
           
End Sub

Sub CreateWMSDailySales
   Dim objImpApp As Object
   Dim objImpRep As Object
   
      Set objImpApp = CreateObject("CognosImpromptu.Application")

      objImpApp.OpenCatalog "\\ssoslapp\Cognos\CATALOG\System Manager\WMS-LIVE.cat", "Creator"

      Set objImpRep = objImpApp.OpenReport("\\ssoslapp\Cognos\Reports\Operatons\wms_daily_sales.imr")

      objImpRep.Export g_strWMSDailySalesData, "x_ascii.flt"

         objImpRep.CloseReport
         rem objImpApp.Quit

   Set objImpRep = Nothing
   Set objImpApp = Nothing
End Sub

Sub CreateStylemanDailySales
   Dim objImpApp As Object
   Dim objImpRep As Object
   
      Set objImpApp = CreateObject("CognosImpromptu.Application")

      objImpApp.OpenCatalog "\\ssoslapp\Cognos\CATALOG\System Manager\Styleman.cat", "Creator"

      Set objImpRep = objImpApp.OpenReport("\\ssoslapp\Cognos\Reports\Operatons\styleman_daily_sales.imr")

      objImpRep.Export g_strStylemanDailySalesData, "x_ascii.flt"

         objImpRep.CloseReport
         rem objImpApp.Quit

   Set objImpRep = Nothing
   Set objImpApp = Nothing
End Sub

Sub ArchiveDataFiles(strFileForCopy As String, strDest As String, strExt As String)

Dim strFileToCopy as String
Dim strDestination as String
Dim strFileTag as String

On Error Resume Next

strFileTag = "_" & Format(Date,"YYYYMMDD")
strFileToCopy = strFileForCopy
strDestination = strDest & strFileTag & "." & strExt

  FileCopy strFileToCopy, strDestination

End Sub
'========Code Ends========

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[ITUngaBunga]

Hi Gary,

Many thanks for this - and pointing out the erroneous variable - you were indeed correct with the strExt -(sound of head banging against wall, table keyboard....!)
Just started with a Company where I'm the only person doing the job - no-one to catch/critique the glaringly obvious!

Sincere thanks.

UB

 

[GJParker]

UB

I think you'll find many people in the same position as you here on Tek-Tips. I know i'm the only person here at my current employers who has the foggiest about Cognos applications. I found the forum invaluable when I first started using the Cognos applications so many moons ago.

As a tip if you add this line to the top of your macro then any undeclared variables will be highlighted.

Option Explicit

Also if you have the Sub Main as the last sub in the macro you won't need to declare the sub routines at all.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

Gary,
The Option Explicit is worth a star on its own - so many of my macros start life as cut and pastes from pre-existing ones, that variable confusion is rife.
Hope your new job is going well.
Lex

soi la, soi carré

 

[GJParker]

Hi drlex

happy new year and thanks for the star.

I haven't started my new job yet, still serving my 3 months notice period which I'm trying my best to negotiate down. I'm finding it quite frustrating as i don't have anything to do here, the company haven't even advertised for a replacement yet so I can't even start a handover.

Where as my new employers took delivery of the Cognos 8 software yesterday and are pushing me for a start date, so they can book training and get the software installed.



Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

They've already got 8 for you in advance? Man, their resellers must be persuasive! Plus you're getting trained up by them? You're in clover, Mr Parker, I tell ya, clover! (I'm just jealous....)

soi la, soi carré

 

[GJParker]

I would be if I could just get some movement around here. LOL

Gary Parker
MIS Data Analyst
Manchester, England

 

[ITUngaBunga]

I've just started using Cognos having come from MS Access (and some SQL Server) environment.
It seems the Cognos ScriptEditor is a very close relative to the VBA language, so I'm quite chuffed as I used to do a fair bit of VBA stuf and found very enjoyable!
Scouring Google (and their groups) I found very little regarding the Cognos ScriptEditor, so I hope to learn something from here and give something back to others along the way.

I didn't realise TekTips was such a slick set-up either!

Anyhow thanks for the pointers, it is appreciated - hope your notice period flies, I worked a month of mine and it was purgatory! - especially being out of the loop and not having a replacement to give a handover to!

Cheers

UB

 

[drlex]

UB
If you haven't come across it already, the Help file on the script editor is not too bad (and repeats much of what is in the pdf in the Documentation folder).
In Help, click on the 'Find' tab, and type in VBA. You should be able to display a page entitled "How the CognosScript Language Compares to Visual Basic and Word Basic" which might illuminate your learnings.
Friday tomorrow. w00t!
lex

soi la, soi carré

 

[ITUngaBunga]

Hey drlex - that was a very good pointer, thanks.

Is it possible to have a Cognos script open (and execute) a VBScript? My VBScript encapsulates what I'm ultimately wanting to achieve in a Cognos macro;
open an Excel file,
refresh the data within said Excel file,
copy & paste the worksheet to another workbook,
save the copy away for archive.

All this kerfuffle because I need to capture the data at a point in time as it can't be recreated (well, not easily!)

Yes - friday tomorrow - I could get used to these 4 day weeks!!

Cheers,

UB

 

[drlex]

Thanks, UB.
It may be possible - I've got no VBA or VB background (closest being B.A.S.I.C. back in 1978), so I keep within CognosScript. One can call MS functions like sleep or shell out to a Batch file. One can call Excel within CognosScript by specifying it as an object and adding the objectname prior to the VB commands, but there is some missing functionality.
e.g.

Set objExcel = CreateObject("Excel.Application")
   objExcel.Visible = 1
      For x = 1 to 4
        objExcel.Application.Workbooks.Open strfilename(x) & strtemp & ".xls"
        objExcel.Application.DisplayAlerts = False
        objExcel.Rows("2:7").Select
etc

soi la, soi carré

 

[ITUngaBunga]

Excellent drlex

From the sample script it could just be possible that I could do the whole nine yards from Cognos and ditch the VBScript route!
It's got me thinking now, anyhow!

BTW, whilst it's great working on my own here I miss being able to bounce any ideas around - so thanks for the quick responses, pointers and suggestions etc.

Cheers

UB