Archive of Mr Excel Message Board

Back to Other Office and Excel archive index
Back to archive home

force xcel to interpret text from access as a function

Posted by Trevor on January 08, 2002 10:53 PM

I get data from an access data base and view it in excel

It would be great if functions typed in a field in access worked when sought refreshed from the access.

eg. an access field is a list of excel functions.

However when I tried it it refreshed in excel as data only. I created a macro to convert to operable function , but it would be good if I could format the receptive excel cell so that it automatically acknowledged it as a function.

Have I confused others as much as I am??

tia Trevor

Re: force xcel to interpret text from access as a function

Posted by Dave c on January 09, 2002 10:04 AM

Write a macro that runs after the data is imported that creates a new column with the result of the formula:

therow = 1
While Cells(therow, 1) <> "" ' assuming your access data starts at row 1 column 1 and is continuous

Cells(therow, 15).Formula = Cells(therow,3).Value 'where 15 is a new column for the formula result and 3 is the access formula

therow = therow + 1
Wend

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.