March 6, 2009 at 12:02 pm
Right click on the toolbar and select Customize.
On the Commands tab, select Data from the Categories list. You should then be able to find 'Refresh All' in the Commands list.
Drag it and drop it on your toolbar where you'd like it.
This is for Excel 2003, not sure about other versions.
This change will be specific to the computer, not to the spreadsheet.
March 6, 2009 at 3:36 pm
Thanks, it got this working...
April 5, 2009 at 10:31 am
I've used the DQY queries / SQL parameterized procedures for a while with SAP among other things (because people always want to see the data in Excel). It's super fast, simple, and clean!
Once the DQY has brought back data to Excel can additional commands in the DQY file execute Excel commands (for instance to create graphs of the data)?
Thanks.
April 6, 2009 at 6:55 am
The .dqy file is very limited. The query text itself cannot even have a line break.
However I use named ranges to create graphs that change with the result of the dynamic query. Use the OFFSET and COUNT functionS to define columns (and number of rows) within the result set (which is automagically a named range in Excel). Then use the SERIES function (what you see in the formula bar when you click on a series in a graph) to define the series using the named ranges rather than relative references.
October 8, 2009 at 10:16 pm
I can't believe I've never noticed that 'Import External Data' before. Would probably end up being quicker than writing an Ad Hoc SQL query in some cases (where the repeatable ones or those with only one or two variables I do have set up using VBA).
Thanks!
October 9, 2009 at 3:18 am
Nice article as it focus on a common need:
"Basically people are comfortable with Excel that is why they use it."
Sometimes you only what figures or graphs to be presented without the need to do more
with the data. Plain Reports.
Sometimes you (the user) have the need to massage the data. The question is how to provide the
user with a tool to do that (and also to select the data). The spreadsheet like Excel has now for decades been an accepted tool to fulfill that need. Is it true that all analysing tools either OLAP like
Cognos or other tools claim they have an interface to Excel?
With tested!! stored procedures or views where the user can select data with parameters and paste
the result into one or more Excelsheets is a solution I have good experience with. I use VBA , Remote OLE DB and ADO which might be off line for members of this forum? Also I use the same approach to let the user enter data from Excel into the database. If interested I can write more about my solution.
//Gosta
October 9, 2009 at 3:26 am
I've been using Excel as the presentation layer of my reporting for ages now, I just found it to be quick and versatile, plus people can play around with the data as much as they want.
As regards parameterised queries, I always use an SP and then manually set the parameters in the Before and After Refresh methods of the WorkSheet
i.e in the "ThisWorkbook" code:
dim withevents qry as Querytables
Private Sub Workbook_Open()
Set qry = Sheet1.QueryTables(1)
End Sub
Private Sub qry_BeforeRefresh(Cancel As Boolean)
'get your parameters here e.g
x = Sheet1.Cells(1,2).Value
y=Sheet1.Cells(1,3).Value
qry.CommandText="ReportServer..MyProc @x=" & X " ,@y=" & y
End Sub
Private Sub qry_AfterRefresh(ByVal Success As Boolean)
'put formatting and post-processing code here
End Sub
I find this gives you total control over how the query refreshes and where the params come from - for example you can display a custom dialog box when the user clicks "Refresh" whereby the parameters for the query might be selected from a list generated by another query. Also allows you to properly save the formats of the sheet (code required to do this) and so on
Plus is allows me in the formatting and processing code to do things you can't easily do when you're using an auto-formatted report, like maybe doing summing or averaging by some weird method which your users insist you do. Sure, you have to manually handle any grouping code, but it's not that hard once you get into it.
October 9, 2009 at 5:38 am
By using {Call [Proc Name] (?)} you can still use the native parameter handling without VBA code.
October 9, 2009 at 7:27 am
does anybody know if excel can work with views? Or will sps be the only recourse in that situation?
October 9, 2009 at 7:32 am
Yes, but without parameters. A View works just like a table. so the SQL could be as simple as SELECT * FROM [ViewName] in the MS query, or simpler still just select the View name from the pick list.
October 9, 2009 at 7:54 am
...and that's where VBA helps you out because you can dynamically construct a WHERE clause for your Select from the view, with "Parameters"
October 9, 2009 at 8:15 am
I have a spreadsheet, created via .Net automation, and populated from a sproc, with one column left blank (the sproc supplies the column header). Now I need to continuously update that column with the result set of another sproc, using as input parameters the values in 3 columns returned by the first sproc. That is, the values in A2, B2 and C2 will be used as parameters to populate F2, and A3, B3 and C3 will be used to call the same sproc to populate F3, etc...
The spreadsheet is re-populated via the first sproc once each morning, but the 2nd needs to be run every x minutes. How can I accomplish this? Thank you for all suggestions and explanations, in advance.
October 9, 2009 at 8:24 am
Indeed, I guess I just prefer to keep VBA out of the picture for security compliance where possible. You can wrap the view with a patameterized proc and call the proc from excel using parameters without VBA
October 9, 2009 at 8:27 am
LadyReader
Is it OK if I do it in VBA for you? I am bussy at the moment so please let me know if my help
is accepted.
//Gosta
October 9, 2009 at 8:28 am
Maybe better to do the work on the SQL side? If not maybe you could establish a zone on the sheet that combines the results of the ,Net automation one and the more frequent query, but I don't think an call to external data can update an area that is feed by another external query.
Viewing 15 posts - 76 through 90 (of 120 total)
You must be logged in to reply to this topic. Login to reply