none
Importing Formulas into MS Excel from a DataSource Query RRS feed

  • Question

  • I have a SQL stored procedure which provides all data I need and also includes formulas which I would like to pass to MS Excel. It works great, pasting the output of my stored procedure with references in the column like "=A2+B2" correctly converts during the paste operation into a formula which correctly adds Column A and Column B together.

    I wanted to create a refreshable MS Excel Report which gets its data from a stored procedure. I followed the contents of this article which worked very nicely:

    https://victoriayudin.com/2014/11/24/create-a-refreshable-excel-report-based-on-a-sql-server-stored-procedure/

    However, this creates a Table view of the data - which is fine, except the formulas now do not work. I tried the alternative naming convention for creating my formulas, e.g. =Col1+Col2 = result. It does not work with that type of formula in the stored procedure.

    If however, I highlight the formula =Col1+Col2 and press F2 and then enter, it converts the formula to the calculated value which is great, however, I will have to do this for every single row in the spreadsheet. Also, when I refresh the data in the spreadsheet I need to repeat the F2 + Enter in order to recalculate the values.

    How can I utilize the hybrid benefits of an MS Excel Dynamic, Refreshable report with formulas that will automatically calculate when I open or refresh without requiring further effort.

    It seems as though this would be a very simple thing to do.

    Thank you,


    John

    Thursday, September 8, 2016 11:29 PM

All replies

  • if your export is going to an excel query table- same process for connecting a pivot table to sql server (data - from other sources then follow steps) and you insert formula to the right it will update when table is refreshed.

    Q- why not do the calculations in the sql server query- much quicker for bigger data sets.

    D

    Friday, September 9, 2016 11:34 AM
  • Yes, if I create a new column it will.

    Some columns have to be in the middle.

    Purpose is so that data can be changed, what if scenario's can be tested to see impacts to the bottom lines. This cannot be done on the fly on SQL Server like it can be done in a spreadsheet.


    John

    Friday, September 9, 2016 3:05 PM
  • John

    You can still insert a column in the middle of the "table query" that is getting it's info from a sql database- This column can have whatever excel formula you want.- 

    I don;t do this the way that Victoria suggests- I would typically create a query using SSMS then get the table query to run it (when you set up the table query- change table to SQL then past the query code just below (code is probably the exact same as you generated the other way

    You actually can connect dynamically back to a sql server query from excel but it is a bit more involved- Sql Server crunches the data better- excel displays it better.

    Friday, September 9, 2016 3:46 PM
  • Thank you, I am not certain I know or understand what you mean. Is there perhaps a how to on this somewhere ? What would be the best resource for me to read up on with this ?

    I think there is a great deal of usefulness that can be made combining the hybrid nature of an Excel worksheet with data being pulled from a spreadsheet. Definitely open to hear more clarity on this topic if at all possible.

    I like utilization of the stored procedures the most.

    One aspect we are reviewing is how to enter data into a spreadsheet and have it actually anchored to a specific record. I'm not certain how that can be done, but that would be extraordinarily helpful.

    The issue I see is the reference point in MS Excel changing as the underlying SQL Query dataset changes. It would be useful to have some sort of binding from an input column no matter when the data refreshes elsewhere in the spreadsheet and not lose the entered data in the spreadsheet.

    Thank you and best regards,


    John

    Friday, September 16, 2016 12:59 AM
  • Hello,

    Sorry for the delay.

    Did your issue resolved?

    If you have resolved your issue, I would appreciate that if you could post your solution here.

     

    In my opinion, we could use Worksheet.Calculate Method (Excel) to automatically calculate all the formula in worksheet.

    E.g. Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate

    Work around by automatically re-entering the formulas, we could use Application.SendKeys Method (Excel)

    E.g.

    Sub cal()

    For i = 1 To ActiveSheet.UsedRange.Rows.Count

    ActiveSheet.Range("C1").Select

    SendKeys ("{F2}")

    SendKeys ("{ENTER}")

    Next

    End Sub

    Regards,

    Celeste

    Monday, September 26, 2016 4:41 AM
    Moderator