Import from SQL Server using an embedded formula   

I'm trying out importing data into excel from an SQL server database.
So far it's great but there is one thing I can't do - calculating an
embedded formula.  

The query looks something like:  

SELECT 1 AS Quantity, ExportPrice, '=RC[-2]*RC[-1]' AS Amount FROM
tblPriceList 

Refreshing puts the correct values into the worksheet.  However, the
3rd column shows "=RC[-2]*RC[-1]"  until I explicitly recalculate
(using Edit & Enter).  Then the correct numeric value shows up.  

When I refresh the data, the formula reappears in the cell.
 
Is there any way to automatically cause the calculation to happen
after refreshing the data?

Otherwise, its really slick.

Thanks,
Max

date: Fri, 24 Mar 2006 17:19:58 -0500   author:   Max Yaffe

Re: Import from SQL Server using an embedded formula   

You cannot "mix" excel formulas and SQL. You either calculate witrhin
your SQL statement, or you calculate with formulas that you fill down
beside the query in your sheet.

 Here is the correct SQL

SELECT 1 AS Quantity, ExportPrice,Quantity* ExportPrice AS Amount FROM
tblPriceList 

DM Unseen

date: 27 Mar 2006 00:04:10 -0800   author:   DM Unseen

Re: Import from SQL Server using an embedded formula   

Hi,

> SELECT 1 AS Quantity, ExportPrice, '=RC[-2]*RC[-1]' AS Amount FROM
> tblPriceList

You cannot return formulas from MS Query like this. If you need to, I 
suggest you have a look at my product, ExcelSQL. It allows you to return 
anything (including formulas, cell comments and linefeeds) from SQL queries. 
For more information, see http://www.excelsql.com

--

Mikko Noromaa (mikkon@excelsql.com)
- SQL in Excel, check out ExcelSQL! - see http://www.excelsql.com -

"Max Yaffe"  wrote in message 
news:jnr822dbbhkd7nko47299nnp1pap44j594@4ax.com...
> I'm trying out importing data into excel from an SQL server database.
> So far it's great but there is one thing I can't do - calculating an
> embedded formula.
>
> The query looks something like:
>
> SELECT 1 AS Quantity, ExportPrice, '=RC[-2]*RC[-1]' AS Amount FROM
> tblPriceList
>
> Refreshing puts the correct values into the worksheet.  However, the
> 3rd column shows "=RC[-2]*RC[-1]"  until I explicitly recalculate
> (using Edit & Enter).  Then the correct numeric value shows up.
>
> When I refresh the data, the formula reappears in the cell.
>
> Is there any way to automatically cause the calculation to happen
> after refreshing the data?
>
> Otherwise, its really slick.
>
> Thanks,
> Max

date: Mon, 27 Mar 2006 13:46:12 +0300   author:   Mikko Noromaa am