By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,312 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,312 IT Pros & Developers. It's quick & easy.

Change Excel Cell Values using a Function

P: 2
I'm looking to load data in an excel sheet from a database. I would like to do this via a function, so that when I recalc the sheet, the data is loaded. The function is something like:

=loaddata(product, startdate as date, enddate as date) in cell A1. Data for the product between startdate and enda date to be loaded in cells A2 and following.
I do not want to call the data from an array which could directly read the return array from loaddata() as I want this function to execute only if the date changes.

I have seen this done. Bloomberg, a financial news and data provider has functions that return data to cells other than the one that calls the function.

Can you help?
Jul 28 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 635
@harius
Hi

This is the way I would tackle it
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.     If Target.Address = Range("StartDate").Address Or Target.Address = Range("EndDate").Address Then
  5.         If IsDate(Target) Then LoadData Sheets("Sheet2").Range("A2")
  6.     End If
  7. End Sub
  8.  
  9. Sub LoadData(ByVal DataRange As Range)
  10.     Dim cn  As New ADODB.Connection
  11.     Dim rs As New ADODB.Recordset
  12.     Dim sql As String
  13.  
  14.     cn.Open "file name=H:\My Data Sources\CES Information DB DEV.udl"
  15.  
  16.     sql = "SELECT * FROM tblEmployees WHERE StartDate " & _
  17.             "BETWEEN #" & Format(Range("StartDate"), "mm/dd/yy") & "# " & _
  18.             "AND #" & Format(Range("EndDate"), "mm/dd/yy") & "#"
  19.  
  20.     rs.Open sql, cn, adOpenStatic, adLockReadOnly
  21.  
  22.     DataRange.CopyFromRecordset rs
  23.  
  24.     rs.Close
  25.     cn.Close
  26.  
  27.     Set rs = Nothing
  28.     Set cn = Nothing
  29.  
  30. End Sub

As you can see this uses the worksheet change event. I have also defined the startdate and enddate cells with a range name. The LoadData sub only fires if one of the date cells is changed (and changed to a date!).

You can then copy the data to any sheet in the wokbook if you want (I used Sheet 2 as it would have overwritten my dates otherwise).

Hope this is of some help.

If it is and you have any queries please ask.


MTB

ps. I have used the for function because I am in the UK but US date formats are required for sql strings!!
Jul 28 '09 #2

P: 2
Thanks for your post.

I understand it addresses the problem I posted. However, it is only part of my problem.
Here is the full problem:

I have a series of products I need to load data for. I have the product Ids for these in a row, say B1, C1, etc... K1.
A1 has today date (end date of the data series) and A2 the start date, say 1/1/2009.
When today() changes, I would like all the data series to recompute on recalc.

I am currently using a loaddata() function taking B2:B10000 as input, with the drawbacks I listed in my first post.

I could of course play with your solution and make it work. However a function based solution would be more nimble. I am specifically looking for such a solution as I have seen it done. Bloomberg has a function for historical data series of securities that is entered on a single cell in the form =BDP(ticker, fields, startdate, enddate) and that returns an array of nbfields columns and nbdates rows. No idea how it's done though...


Regards,

Hari
Jul 28 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.