Visual Basic and Impromptu 1

[tonioJ]

Can anybody help me on how to incorporate impromptu with visual basic???

 

[griffindm]

The same API calls listed in the Cognos Macro language help file will work when called via VB.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[tonioJ]

Thank you very much for your reply Mr. Dave Griffin.. By the way, I am new to Cognos Impromptu. I had figured out what you mean...

Another question... how do you filter data in impromptu using Macro that will be called in Visual Basic for automation.

 

[tonioJ]

hello I have a question... can Impromptu use the MS ACCESS database as the source of data with using ODBC??

 

[griffindm]

tonioj,

1) Create a prompt in the report, and pass it from VB to Impromptu with the OpenReport method. It must be passed as a string variable.

2) Impromptu supports ODBC from a variety of databases, including Access.

The answers to all of these questions are in the documentation for either Impromptu or for the Cognos Macro Editor.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[tonioJ]

hello dave,
again, thank you very much for your reply... it's very helpful.. i will keep in touch with you and other guys through this forum if ever i have a concern... thanks... more power....

 

[tonioJ]

is there a waty or a property i can set so that the list frame of impromptu sub report can be resized automatically depending on the number of row/s or record/s. ?? please advise me...

 

[tonioJ]

URGENT! hello! can anybody help me! is there a way to preview a report directly to visual basic? is there a way or a property i can set so that the list frame of impromptu sub report can be resized automatically depending on the number of row/s or record/s. ?? and also can anybody give me an example on how to pass a prompt value in string from visual basic to impromptu via script... please advise me...

 

[lstephane]

Hi griffindm,

I just tryed the API calls from Cognos Script to VB and it doesn't work.

I'm using object to open my catalog than my report and I want to export results to a text file.

I'm not able to use objects like I do into my Cognos scripts.

Can you help me on that issue ?

Thanks.
Stéphane Lambert, Analyst/Programmer - Cognos Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[griffindm]

Stephane,

I'm not a regular VB programmer, and I do not call Cognos products from VB myself. I do occasionally call an ActiveX control from the Cognos macro language, which works fine.

All I can tell you is that the macro language (which is actually Southbridge Basic Language from Mystic River Software. You can find references to it as SBL in the macro help files) was compatible with VB through VB3. I can't help you issues on later versions. Perhaps someone else here can.

Good luck,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[lstephane]

HI,

I finally found the problem.

Few months ago, I installed Series 7 on my PC and both Imr 6.0 & Imr 7.0 was installed.

I removed the Series 7 version.

Now the registry for CognosImpromtpu.Application is corrupt.

To fix the problem, I found an article on the Microsoft KB site. Here is the article.

--- BEGIN OF ARTICLE ---
INFO: Troubleshooting Error 429 When Automating Office Applications
The information in this article applies to:
Microsoft Excel 2000
Microsoft Visual Basic Professional Edition for Windows 5.0
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 5.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Access 2002
Microsoft Access 2000
Microsoft Access 97
Microsoft Excel 2002
Microsoft Excel 97 for Windows
Microsoft FrontPage 2002
Microsoft FrontPage 2000
Microsoft Outlook 2002
Microsoft Outlook 2000
Microsoft Outlook 97
Microsoft Outlook 98
Microsoft PowerPoint 2002
Microsoft PowerPoint 2000
Microsoft PowerPoint 97 for Windows
Microsoft Word 2002
Microsoft Word 2000
Microsoft Word 97 for Windows
This article was previously published under Q244264
SUMMARY
When you use the New operator or CreateObject function in Microsoft Visual Basic to create an instance of a Microsoft Office application, you may get the following error message:

Run-time error '429': ActiveX component can't create object
This error occurs when the requested Automation object could not be created by COM, and is therefore unavailable to Visual Basic. The error is typically seen on certain computers but not others.

This article provides some troubleshooting tips to help you diagnose and resolve common problems that are known to cause this error.
MORE INFORMATION
Unlike some errors in Visual Basic, there is no one cause to an error 429. The problem happens because of an error in the application or system configuration, or a missing or damaged component. Finding the exact cause is a matter of eliminating possibilities. If you encounter this error on a client computer, there are a number of things you will need to check to isolate and resolve the error.

The items later give you some practical suggestions for troubleshooting this error when you work with Office Applications. Some of this information may also apply to non-Office COM servers as well, but this article assumes you are trying to automate Microsoft Office.
Checking the Code
The first place to begin looking for the problem is in the code. Before you can troubleshoot the error, you need to know where the error occurs. Try to narrow it down to a single line of code.

When you find the code that is failing, try to do the following:

Make sure the code uses explicit object creation. Any problem is easier to spot and identify if the problem is narrowed to a single action. For example, do not do the following:
Application.Documents.Add 'DON'T USE THIS!!



or:


Dim oWordApp As New Word.Application 'DON'T USE THIS!!
'... some other code
oWordApp.Documents.Add

Both of these methods use implicit object creation. Microsoft Word is not started until the variable is called at least once. Since the variable could be called in different parts of the program, this could make the problem hard to localize. Also, it is not clear whether the problem is with creating the Application object or the Document object.

Instead, make explicit calls to create each object separately:
Dim oWordApp As Word.Application
Dim oDoc As Word.Document
Set oWordApp = CreateObject("Word.Application&quot
'... some other code
Set oDoc = oWordApp.Documents.Add

This makes the problem easier to isolate and makes the code more readable.
When creating an instance of an Microsoft Office application, use CreateObject instead of New. CreateObject more closely maps to the creation process used by most Visual C++ clients, and allows for possible changes in the server's CLSID between versions. CreateObject can be used with both early-bound and late-bound objects.
Verify that the ProgID string passed to CreateObject is correct and that it is version independent (that is,, use "Excel.Application" rather than "Excel.Application.8&quot. It could be that the system that is failing has an older or newer version of Microsoft Office than the version you specified in the ProgID.
To aid in debugging applications that cannot be run in the IDE, use the Erl command to report the line number of the line that fails. For example, the following code will tell you which Automation object cannot be created (Word or Excel):
Dim oWord As Word.Application
Dim oExcel As Excel.Application

On Error Goto err_handler

1: Set oWord = CreateObject("Word.Application&quot
2: Set oExcel = CreateObject("Excel.Application&quot

' ... some other code

err_handler:
MsgBox "The code failed at line " & Erl, vbCritical

Use a combination of message boxes and line numbers to track down the error.
Try using late binding (that is,, Dim oWordApp As Object). Early bound objects require their custom interfaces to be marshaled across process boundaries. If there is a problem marshaling a custom interface during CreateObject or New, you will get an error 429. A late bound object uses a system-defined interface (IDispatch) that does not require a custom proxy in order to be marshaled. Try using a late bound object to see if this makes a difference.

If the problem occurs only when the object is early-bound, the problem is with the server application, and can typically be corrected by reinstalling the application (see later).
If you are automating from ASP or an MTS component, use CreateObject instead of Server.CreateObject(). Using Server.CreateObject will instantiate the Office application under the identity of an MTS package which is known to cause problems with Microsoft Office.
Checking the Automation Server
The most common reasons for an error with CreateObject or New are problems with the server application itself. Typically, these problems are with the configuration or setup of the application. Here are some items to check:

Verify the Microsoft Office application you want to Automate is installed on the local computer, and make sure that you can start the application from the Start and then Run dialog box. If the program cannot be started manually, it will not work through automation.
Re-register the application by typing the path to the server in the Start and then Run dialog box, and then append /RegServer to the end of the line. Press OK. This should silently run the application and re-register it as a COM server. If the problem is with a missing registry key, this will typically correct it.
Check the LocalServer32 key under the CLSID for the application you want to Automate. Make sure it points to the correct location for the application, and make sure the path name is in a short path (DOS 8.3) format. While it is not a requirement that a server be registered using a short path name, long path names that include embedded spaces have been known to cause problems on some systems (see later).

To check the path key stored for the server, start the Windows Registry Editor by typing regedit in the Start and then Run dialog box. Navigate to the HKEY_CLASSES_ROOT\Clsid key. Under this key you will find the CLSIDs for the registered automation servers on the system. Using the values later, find the key that represents the Office application you want to Automate and check its LocalServer32 key for the path.
+========================+=========================================+
| Office Server | CLSID Key |
+========================+=========================================+
| Access.Application | {73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9} |
+------------------------+-----------------------------------------+
| Excel.Application | {00024500-0000-0000-C000-000000000046} |
+------------------------+-----------------------------------------+
| FrontPage.Application | {04DF1015-7007-11D1-83BC-006097ABE675} |
+------------------------+-----------------------------------------+
| Outlook.Application | {0006F03A-0000-0000-C000-000000000046} |
+------------------------+-----------------------------------------+
| PowerPoint.Application | {91493441-5A91-11CF-8700-00AA0060263B} |
+------------------------+-----------------------------------------+
| Word.Application | {000209FF-0000-0000-C000-000000000046} |
+------------------------+-----------------------------------------+

Does the path match the actual location of the file? Be aware that short path names can give you the impression that a path is correct when it may not be. For example, both Microsoft Office and Microsoft Internet Explorer (if installed in their default locations) will have a short path similar to "C:\PROGRA~1\MICROS~X\" where X is some number. It is not immediately obvious that you are looking at from a short path name.

You can test that the path is indeed correct by copying the value from the registry and pasting it into the Start and then Run dialog box (remove the /Automation switch before running the application). Does the application start when you select OK? If yes, then the server is registered correctly. If not, you should replace the value of the LocalServer32 key with the correct path (use a short path name if possible).
Problems have been known to occur when automating Word or Excel if either the Normal.dot template (Word) or the Excel.xlb resource file (Excel) has become corrupt. To test if a corruption has occurred, search the local hard drives to find all instances of Normal.dot or *.xlb. (Please note that if you are running Windows 2000, Windows NT, or Windows 95/98 with profiles enabled, you may find multiple copies of these files, one for each user profile on the system.) Temporarily rename the Normal.dot file(s) or the *.xlb file(s), and re-run your Automation test (Word and Excel will create these files if they cannot find them). Does the code now work? If yes, then the files you renamed should be deleted since they are corrupt. If not, you should rename them back to their original names so any custom settings saved in these files won't be lost.
If you are on a Windows NT or Windows 2000 system, run the application under the Administrator account. Office servers require read/write access to the registry and disk drive, and may not properly load if your current security settings deny this privilege.
Checking the System
System configuration can also cause problems with the creation of out-of-process COM servers. The following are some things to check on systems where the error occurs:
Does the problem happen with any out-of-process server? If you have an application that just uses a particular COM server (for example,, Word), you'll want to test a different out-of-process server to make sure the problem is not with COM layer itself. If no out-of-process COM server can be created on that system, then a reinstallation of the OLE system files (see below) or a reinstallation of the operating system will be required to resolve the issue.
Check the version numbers for the OLE system files that manage Automation. These files are typically installed as a set, and should match build numbers. An improperly configured setup utility can mistakenly install the files separately, causing them to become mismatched. To avoid problems with Automation, you should check the files to make sure the files match builds.

You will find the Automation files in the Windows\System or Winnt\System32 directory. The following is the list of files to check:
+---------------+-------------+----------------+
| File Name | Version | Date Modified |
+---------------+-------------+----------------+
| Asycfilt.dll | 2.40.4275 | March 08, 1999 |
| Oleaut32.dll | 2.40.4275 | March 08, 1999 |
| Olepro32.dll | 5.0.4275 | March 08, 1999 |
| Stdole2.tlb | 2.40.4275 | March 08, 1999 |
+---------------+-------------+----------------+

Check the file version by right-clicking on the file in Explorer and selecting Properties from the popup menu. The values most important are the last four digits of the file version (the build number) and the date last modified. You want to make sure these values are the same for all the Automation files.

Please note that the version numbers and dates given above are for example purposes only. Your values may differ. The important thing is that these values match each other, rather than this table.

If the files don't match build numbers or modified dates, you can download a self-extracting utility that will update your Automation files. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

235420 FILE: VBRun60sp5.exe Installs Visual Basic 6.0 SP5 Run-Time Files


Windows NT 4.0 has a known problem with starting Automation servers that live in a folder that contains an embedded space in the name, and/or resembles another folder whose first 8 characters are identical. For example, a server living in C:\Program Files\SomeFolder may fail to start during a call to CreateObject if there is another folder on the system called C:\Program Stuff\SomeFolder. For more information on, see the following Knowledge Base article:For additional information about this problem and steps to workaround it, click the article number below to view the article in the Microsoft Knowledge Base:

185126 BUG: COM/OLE Server Fails to Start on Windows NT 4.0


Reinstalling Microsoft Office
If none of the preceding steps helps to resolve the problem, consider uninstalling and reinstalling Microsoft Office. Microsoft recommends that you uninstall the existing version first, and then reinstall from the original installation disks.

For a complete list of the items to be removed, please see the following Knowledge Base articles:

219423 OFF2000: How to Completely Remove Microsoft Office 2000



158658 OFF97: How to Completely Remove Microsoft Office 97


REFERENCES
For additional information about troubleshooting the '429' error message, click the article number below to view the article in the Microsoft Knowledge Base:

240377 HOWTO: Insure Jet 3.5 Is Installed Correctly (Part I)

For the latest information and sample code regarding Microsoft Office Automation, please see the Microsoft Online support site at:

http://support.microsoft.com/support/officedev/

Last Reviewed: 9/11/2002
Keywords: kbAutomation kbDSupport kbinfo KB244264

--- END OF ARTICLE ---

Enjoy !
Stéphane Lambert, Analyst/Programmer - Cognos Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca