MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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