launch xls from outlook form and return results

[robw]

Hi,

I hope someone can help me with this :-

I have created a custom form in Outlook. I have created a command button on
the form and an associated cmdbut_click() handling routine in VBScript.
Currently the code displays a msgbox and merely proves that the linkage
works ok. The behaviour I really want is as follows :-

1) when the command button is clicked a specific excel workbook
(C:\somelongpath\mycalcs.xls) is launched
2) the outlook session suspends until the xls is closed by the user.
3) when the outlook session resumes the contents of the clipboard (as
populated by macros that run within the xls) are pasted into a textbox
("calcresults")

Any pointers would be appreciated

Thanks
Rob

 

[Sue Mosher [MVP-Outlook]]

1) Use Excel methods:

Set ex = Create.Object("Excel.Application")
Set ws= ex.Workbooks.Add ("C:\somelongpath\mycalcs.xls")
ws.Display

2-3) I don't know of you can display a worksheet modally. Maybe you should build that functionality into a macro in the worksheet.