I have an Excel based application to test and automate its test cases using QTP. About the applicaton, it has few sheets with various buttons; eg Refresh, Report, etc.
So when ever a button is clicked; a macro is called which opens a form. On giving input to the form and clicking the submit button, it processed the data and popluates data in the same excel below it.

We as QM team have to automate the test steps which involve opening the sheet, clicking the button and checking the data obtained.

As I understand that QTP cannot recognise an excel based application and its objects (unlike Web, java and others) and also there is no add-in to support it expect its Object Model Interface.

I have tried the following options:

1. To create objects through Object Identification.
Using the option to create "user defined objects" by maping application objects to standard Windows objects.
It identifies Excel as a Window and cannot identify futher excel objects like buttons.

2. Created an Excel object: Excel.Application object
i) I could not access the button on excel using the object references (tried with OLE objects)

ii) Then tried to access the macro directly to stimulate clicking on button object. This however works only if the function or sub is not private. But in the application, the function calls are private!!

iii) With Step ii) I could access the macro function and open the "Form" through QTP: Application. Run “Macro Name”
After the form gets displayed, the script hangs and does not got to next line of QTP code, unless the form is closed manually. I am unable to feed the input data to form through VB Script.
The macro function does not quit and hangs at code: UserForm.Show .

UserForm.Show makes it mandatory to fill data on the form before performing any action. So control does not flow to next line.

iv) If the userfrom was defined as UserForm.Show VbModeless , the control comes back to QTP and can also navigate to the parent Excel window.
But this is not the case with the application.

V) After using UserForm.Show VbModeless, I tried to input data into the form; but was not successful to enter any data or click any buttons on the form.

If anyone has worked on similar application(s) before, please pour in your suggestions and experiences.
Any pointers would be gr8!!