October 9, 2009 at 8:30 am
Gosta,
Thank you for your offer. Any method is acceptable, as long as it works. We are not talking about a huge amount of data, 1 date to be returned for each of maybe 100 rows.
Thank you.
October 9, 2009 at 8:42 am
sgleeson (10/9/2009)
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
Well played sir, well played...
I agree 100% that your method is the simplest way of doing it, but I think VBA has its place - case in point, I reckon it will be necessary for LadyReader's problem - then again, I would genuinely love to see a solution that didn't require any code on the sheet.
As regards getting the sheet to execute a refresh at a specific time, I would do this
-Add a Module to the Workbook.
-Define a procedure that refreshes the queries.
-In the WorkBook_Open() procedure, add in
Application.OnTime Now+TimeValue("00:00:05"), "nameofyourprocinmodule" (to execute every 5 seconds)
then in the module procedure, after your work is finished, re-set Application.OnTime in the same way.
October 9, 2009 at 8:46 am
Fascinating thread -- I do have one note of caution
If you plan to use the ODBC connection for anything else, you should know that this approach will introduce minor corruption in that connection.
The problem is that when you start at the beginning of the wizard -- selecting your owner and database, that information gets stored as default in the user registry. If you go to the ODBC adminstration you will not see the local user modifications, but if you use the ODBC from another program then you will connect with the new default owner/db. MOst programs try and account for that and they do a 97% job. SO the other program will work 97% of the time, and exhibit sporadic odd problems.
There are 2 work-arounds.
1) Use an ODBC created solely for Excel. THis has an additional advantage in that you can repoint the ODBC without change if anything in Excel
2) Accept defaults in the wizard, picking a random table from master to start, then remove that table in Query Analyzer and either add your tables their or simply reference them in the SQL pane.
(also -- while I have used the underlying technique you mention in excel 2003 for years with no issues, I find that excel 2007 has a strong tendency to corrupt the excel files -- strong enough that I always maintain a formal backup).
October 9, 2009 at 8:49 am
@LadyReader - will the values in the first 3 columns be changed by the user? I presume then you know what they say about assumptions...
October 9, 2009 at 8:50 am
I'm pretty sure you could avoid the VBA altogether if you replace the Stored Procedure with a parameterized table-valued User Defined Function.
October 9, 2009 at 8:58 am
Stephen,
Mostly no - that is, except for the initially blank column, all the other columns are populated by the first sproc, and are not changed by the user. The exception (there's always an exception, right?) is that the user may want to add their own entire rows. I would be happy to initially ignore this complication and just get my first coding challenge solved.
So, how do I approach this?
Thanks again.
October 9, 2009 at 9:08 am
And well played also. Not one to give up easily however! You can set refresh every x minutes in Data Range properties (agreed can't do seconds). If it is a fixed or formula determinable parameter, and the refresh is in minues, then I think it could be done without VBA, but I am not surei uderstand the requirement other then the periodic refresh.
October 9, 2009 at 9:17 am
LadyReader (10/9/2009)
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.
Suggest creating a proc that selects the data that populates ABC colums and then joins to get the results required in Column F and that is then Data External into a new sheet with data range property set to refresh every x minutes.
October 9, 2009 at 9:24 am
sgleeson,
How do I ensure that the rows retrieved with the 2nd sproc match up with the rows retrieved from the first sproc? Even if I assume the user won't add any new rows, I still feel I need to ensure that the date returned is the correct date for that particular row. Can one attach a qry to a particular row or cell, isntead of to the spreadsheet itself, like with a function, where, for example, every cell in a column can run the same function but use as input data from its own row?
October 9, 2009 at 9:31 am
Ok, although I would begin with a caveat that A)this maybe could be done on the server side mostly by storing the results in a table before putting them in the Excel sheet, whereby you could then just issue one query every x seconds to get your updates values, and B)that without doing this you are issuing a lot of queries to the server (one per row to be specific) that could cause performance issues and finally C) that there is probably a much better solution to be had from someone with more experience!
First off, you'll need to modify the template file you are using to create the initial sheet to have a reference to an ADO library (ActiveX Data Objects).
Also add a module to the Template like so (apologies for lack of formatting and also the // comments needs to be replaced with an apostrophe for VBA, had to put in // or else the code would not display right here. Also, you must replace all of the existing apostrohpes with double quotes for VBA.
Public Sub DoQuery (sheet as WorkSheet)
dim r as integer
r = 2
do
Param1 = sheet.cells(r,1).value //value from column A
Param2 = sheet.cells(r,1).value //B
Param3 = sheet.cells(r,3).value //C
query = '[Name of your Stored Procedure] @Param1=" & Param1 & ",@Param2=" & Param2 & ",@Param3=" @Param3' //note you have to make sure to quote string, format dates, etc yourself here.
dim conn as new ADODB.Connection
conn.ConnectionString='server=[your server name];initial catalog=[name of database];integrated security=sspi;' //or uid=xxx, password=yyyy if thats the way it's setup, I recommend you use integrated authentication if possible though.
dim comm as new ADODB.Command
set comm.ActiveConnection = conn
comm.CommandText=query
conn.Open
dim rs as adodb.connection
set rs = comm.Execute
result = rs('[name of field returned by sproc]')
sheet.cells(r,6).Value - result //column F
rs.Close
set rs = nothing
comm.Close
set comm=nothing
conn.Close
set conn = nothing
r=r+1
while sheet.cells(r,1).value <>'' //or whatever you want to signify as a 'Stop' value; either a blank column A or something else.
end sub
public sub TimerProc()
//disable the timer until we are done.
Application.OnTime Now+timeValue('00:01:00'),'TimerProc',false
//run the update
DoQuery Sheet1 //or whatever sheet you use
/re-set the timer
Application.OnTime Now+timeValue('00:01:00'),'TimerProc',true
end sub
finally in 'ThisWorkBook' object add code for WorkBook.Open()
private sub WorkBook_Open()
Application.OnTime Now+timeValue('00:01:00'),'TimerProc',true //every 1 hour
end sub
I have left out all error handling code and whatnot, also you will probably want to test this..cos I didn't, I'm running out the door in a few minutes to get nicely toasted for the weekend 🙂
anyway maybe it will keep you going until someone gives you the "real" solution!
October 9, 2009 at 9:37 am
I use Excel to give users access to data all the time without having to use VB code. Instead of using Stored Procedures you can use Views and then Excel will let you use all kinds of parameters in the query, the only difference is instead of doing an exec stored procedure you have to do a select * from your view or identify the fields that you want like you would with a sql query but it works great and the users love it.
October 9, 2009 at 9:38 am
Thanks, Stephen. I will try that as soon as I can. I appreciate the time you spent thinking about this!
October 9, 2009 at 11:33 am
Stephen great job.
I saw your example of code which is an approach i like and have one comment:
You open and close the ADODB.connection inside the loop which will cause extra overhead.
Also I think you forgott the loop in loop wihile
My suggestion is included, however not tested as written.
LadyReader
I am sorry I am bussy I cannot do the whole solution for you at this moment but hopefully
you have something to work on.
//Gosta
___
dim r as integer
dim conn as new ADODB.Connection
dim rs as adodb.connection
dim comm as new ADODB.Command
conn.ConnectionString='server=[your server name];initial catalog=[name of database];integrated security=sspi;' //or uid=xxx, password=yyyy if thats the way it's setup, I recommend you use integrated authentication if possible though.
set comm.ActiveConnection = conn
conn.Open
r = 2
do
Param1 = sheet.cells(r,1).value //value from column A
Param2 = sheet.cells(r,1).value //B
Param3 = sheet.cells(r,3).value //C
query = '[Name of your Stored Procedure] @Param1=" & Param1 & ",@Param2=" & Param2 & ",@Param3=" @Param3' //note you have to make sure to quote string, format dates, etc yourself here.
comm.CommandText=query
set rs = comm.Execute
result = rs('[name of field returned by sproc]')
sheet.cells(r,6).Value - result //column F
rs.Close
r=r+1
loop while sheet.cells(r,1).value <>'' //or whatever you want to signify as a 'Stop' value; either a blank column A or something else.
set rs = nothing
comm.Close
set comm=nothing
conn.Close
set conn = nothing
end sub
public sub TimerProc()
//disable the timer until we are done.
Application.OnTime Now+timeValue('00:01:00'),'TimerProc',false
//run the update
DoQuery Sheet1 //or whatever sheet you use
/re-set the timer
Application.OnTime Now+timeValue('00:01:00'),'TimerProc',true
end sub
______
October 9, 2009 at 2:04 pm
Gosta, thank you for your comments.
I will try Stephen's solution with your variation as soon as I can. I'm in the middle of something else right now, but first thing on Monday...
Thanks to both of you!
October 9, 2009 at 2:20 pm
Can you post what you declared the variables as?
Thanks,
Ron
Viewing 15 posts - 91 through 105 (of 120 total)
You must be logged in to reply to this topic. Login to reply