Trapping Errors

[DotNetGnat]

Hi guys,

I have this following code...i have already included error handling and thanks to DoubleD who helped me with this. BUT

Sub main()
   
   On Error GoTo Err_here
   
   Dim objImpApp As Object
   Dim objImpRep As Object
   Dim objPDFPub As Object
   Dim strRpt As String
   
   strRpt="path to imr file"
   
   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.Visible 1

   objImpApp.OpenCatalog "path to catalog", , , "username", "password", -1
   Set objImpRep = objImpApp.OpenReport(strRpt)
   
   Set objPDFPub = objImpRep.PublishPDF
   filepath = "\\blah\Reports\"
   filename = "Activations" + ".pdf"

   objPDFPub.Publish filepath & filename
   
   objImpRep.CloseReport
   
Exit_Here:
   
   objImpApp.Quit
   Set objImpApp = Nothing
   Set objImpRep = Nothing
   Set objPDFPub = Nothing
   Exit Sub
   
Err_Here:
   Open "C:\ErrLogFile.txt" For Append As #1
   Print #1, "Error occured while running " & strRpt & "."
   Print #1, "Error #:" & Err & " " & Error$
   Print #1, "Error occured at " & Now() & "."
   Print #1, "********** End Of Error ***********"
   
   Close #1
   Resume Exit_Here
End Sub

the code does not work in all cases...

for example:

case 1: if there is any error in the file path or if there is any error in the printer(if i am trying to print my report) the code traps the error and logs it and moves on to run the next scheduled macro.

case 2: if the file is already opened and the macro is trying to save the same file after running through the scheduler...i just get a pop up message saying that "the file is already locked by another user". The code DOES NOT trap the error and DOES NOT MOVE ON to execute the next shceduled macro.

in the above code...the macro is trying to save it as a pdf file but if this file is already opened by some user...then i just get pop and everything freezes and error doesnt get trapped...

are there any work arounds for this situation...

Thanks

-DNG

 

[griffindm]

One easy fix to this is to make the file name unique. It will not try to overwrite an existing file, and there is no way the file could be in use as it does not exist yet. Do a search here for 'unique filename' and see if you can find a thread about using the date in the file name dynamically.

If you must use the same name, then you cannot be assured that the file might not be in use, therefore you need to investigate how to better trap and handle the error.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DotNetGnat]

Hi Dave,

Thanks for your reply. These macros create daily reports and we just overwrite them in the respective folders with the same names..so we must use the same names...

I was wondering if there is any VB sub routine that can bypass these application generated error messages windows and move to execute the other scheduled macros...

I mean when the error messages appears and manually when we click OK on the error message window...it moves on to execute the other scheduled macros...

if we can do it manually just by a click...cant we programatically come up with something that does this for us or just bypassess this stage...

Thanks

-DNG

 

[drlex]

DotNetGnat,
Dave's comments ring true; most of the time scripts are run out of working hours, so that the original output files can be overwritten without error.

Haven't tried it with Impromptu, but when calling Excel from within a CognosScript, I use a line

objExcel.Application.DisplayAlerts = False

to stop Excel putting a warning window up when overwriting the file used. Perhaps that might help.
lex

soi la, soi carré

 

[DotNetGnat]

Thanks drlex. Looks like a brilliant idea.Is there something similar for PDF thing too...

Also below is the code for one of my macro that saves the the results as an excel sheet...where do i include your DisplayAlerts = false line...

Sub main()
   
   On Error GoTo Err_Here
   
   Dim objImpApp As Object
   Dim objImpRep As Object
   Dim strExcelFileName As String
   Dim strRpt As String
   
   strRpt="path to imr file"
   
   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.Visible 1

   objImpApp.OpenCatalog "path to catalog", , , "username", "password", -1
   Set objImpRep = objImpApp.OpenReport(strRpt)
   
   
   strExcelFileName = "\\Data\reports\" & _
                      "test.xls"

   objImpRep.ExportExcel strExcelFileName
   
   
   objImpRep.CloseReport
   
Exit_Here:
   objImpApp.Quit
   Set objImpApp = Nothing
   Set objImpRep = Nothing
   Exit Sub
   
Err_Here:
   Open "\\Data\Share1\ImpErrors\ErrLogFile.txt" For Append As #1
   Print #1, "Error occured while running " & strRpt & "."
   Print #1, "Error #:" & Err & " " & Error$
   Print #1, "Error occured at " & Now() & "."
   Print #1, "********** End Of Error ***********"
   
   Close #1
   Resume Exit_Here
End Sub

thanks

-DNG

 

[123FakeSt]

DisplayAlerts won't work there .. you are only using Cognos's utility to create the Excel file without ever touching Microsoft.

The early bird gets the worm, but the second mouse gets the cheese.

 

[DotNetGnat]

thanks, yes DisplayAlerts wont work...

I tried GetErrorNumber Method and it worked fine in case where i am saving my reports as excel sheets..but it is not working in case where i am publishing my reports as PDF files...

Any suggestions

Thanks in advance

-DNG

 

[drlex]

DotNetGnat,
Oh well, thought it worth a try.
You'd better go with Dave's suggestion - use error trapping to test for permissions to write.

Suggest you save the file with a temporary name and then use FileCopy to overwrite the old file. If the 'old' file is in use, you'll get a "Permission Denied" Error that will be caught in the error trapping and so you can set notification of the problem, exit and proceed with the subsequent scripts.

Also, if you copy the required Impromptu report to a temporary name and open the copy, you'll avoid the locked report message. A quick test indicates that there's no error if the report is being used at the time of copying.


soi la, soi carré

 

[DotNetGnat]

Thanks

-DNG