topic Re: How to run "find" command in Excel through SAS DDE in Microsoft Integration with SAS

How to run "find" command in Excel through SAS DDE

deleted_user — Mon, 08 Dec 2008 22:05:38 GMT

I have a sas program to write data from sas into an existing EXCEL spreadsheet. I want to run "find" command in EXCEl through SAS DDE.

Here is the part of my program :

Put '[ERROR(false)]';
Put '[OPEN("'"&fname."'")]';
Put '[workbook.activate("'"&tname."'")]';
put '[SELECT("R90C28:R90C30")]';
put 'selection.find(200847)';

I keep getting error message like " DDE session not ready". I can't figure out why, as if I remove the last line of my program, it runs ok. Please help.

Thank you!

Re: How to run "find" command in Excel through SAS DDE

deleted_user — Wed, 10 Dec 2008 15:59:17 GMT

an alternative way is to record a macro.

http://support.microsoft.com/kb/128185

Re: How to run "find" command in Excel through SAS DDE

data_null__ — Wed, 10 Dec 2008 20:44:23 GMT

I "think" you need FORMULA.FIND. I was not able to test.

put '[formula.find(200847,2,1,1)]';
Syntax

FORMULA.FIND(text, in_num, at_num, by_num, dir_num, match_case)
FORMULA.FIND?(text, in_num, at_num, by_num, dir_num, match_case)
Text is the text you want to find. Text corresponds to the Find What box in the Find dialog box.
In_num is a number from 1 to 3 specifying where to search.

In_num Searches

1 Formulas
2 Values
3 Notes

At_num is the number 1 or 2 and specifies whether to find cells containing only text or also cells containing text within a longer string of characters.

At_num Searches for text as

1 A whole string (the only value in the cell)
2 Either a whole string or part of a longer string

By_num is the number 1 or 2 and specifies whether to search by rows or by columns.

By_num Searches by

1 Rows
2 Columns

Dir_num is the number 1 or 2 and specifies whether to search for the next or previous occurrence of text.

Dir_num Searches for

1 or omitted The next occurrence of text
2 The previous occurrence of text

Match_case is a logical value corresponding to the Match Case check box in the Find dialog box. If match_case is TRUE, Microsoft Excel matches characters exactly, including uppercase and lowercase; if FALSE or omitted, matching is not case-sensitive.

Re: How to run "find" command in Excel through SAS DDE

deleted_user — Tue, 24 Feb 2009 19:35:22 GMT

Thank you.

formula.find( ) works well in my situation.