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(produ ct, 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?
2 4043 @harius
Hi
This is the way I would tackle it - Option Explicit
-
-
Private Sub Worksheet_Change(ByVal Target As Range)
-
If Target.Address = Range("StartDate").Address Or Target.Address = Range("EndDate").Address Then
-
If IsDate(Target) Then LoadData Sheets("Sheet2").Range("A2")
-
End If
-
End Sub
-
-
Sub LoadData(ByVal DataRange As Range)
-
Dim cn As New ADODB.Connection
-
Dim rs As New ADODB.Recordset
-
Dim sql As String
-
-
cn.Open "file name=H:\My Data Sources\CES Information DB DEV.udl"
-
-
sql = "SELECT * FROM tblEmployees WHERE StartDate " & _
-
"BETWEEN #" & Format(Range("StartDate"), "mm/dd/yy") & "# " & _
-
"AND #" & Format(Range("EndDate"), "mm/dd/yy") & "#"
-
-
rs.Open sql, cn, adOpenStatic, adLockReadOnly
-
-
DataRange.CopyFromRecordset rs
-
-
rs.Close
-
cn.Close
-
-
Set rs = Nothing
-
Set cn = Nothing
-
-
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!!
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Allison Bailey |
last post by:
Hi Folks,
I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....
I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.
I'm not really sure how to get started with this process.
I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
|
by: Bertrand |
last post by:
Hello,
I am trying to archieve the following:
- copy an excel file present on the server
- insert values into named ranges of the copy
I am using Excel97 on my PC, the server does not have
Excel installed on it so I can't create the object
Excel.Application
|
by: Paul |
last post by:
I was wondering if anyone has had an issue where using vba code to
read an excel file and import the data into an access table some
records are not imported from the excel file. It seems looking at the
data in the excel file that if the first character in the excel file
cell is numeric it will read and write only numeric values only. If I
sort the coloumn in the excel file and the first character in the cell
read is alphanumeric then only...
|
by: Jerry |
last post by:
I am new to C# and have been trying to figure out how to access a range
passed to C#. I have tried everything I can find and have been unable to get
it to work. Here is a test sample Ive been playing with and It returns an
error. What is wrong with this function?
public int CalcArray(ref int CalcType,ref int Lines,Excel.Range rngFind)
{
double test;
int result;
|
by: Kevin Humphreys |
last post by:
Hi There,
I am trying to export a recordset to an excel file using the Content Type
below in the header.
Response.ContentType = "application/vnd.ms-excel"
Which works fine however the problem is that when I have a number in a
column with a leading zero the zero gets dropped.
| |
by: Elena |
last post by:
I need the VB.NET code to change the header/footer of an Excel spread sheet,
ASAP.
I am doing it through a VB application. I can change the ranges/cell values
using code, but I do not know how to change things outside of the cell
ranges. I will try and post in an Excel group, but i do not need to change
it in a worksheet, I need to change it through a VB application. For
example, I am using this code to change values in certain cells:
...
|
by: Frank M. Walter |
last post by:
Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T()
I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC
|
by: Ch Pravin |
last post by:
Hi All:
I am having the following xml which i need to convert to excel using xslt.
Please help me out.
Afghanistan.xml
<?xml version="1.0" encoding="utf-16"?>
<Languages BuildVersion="1,5,0815,0 " CountryName="Afghanistan" >
<Language locale="English">
|
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am trying to export to Excel using a command in an Access Form.
RowID strFY AccountID CostElementWBS
1 2008 1 7
2 2008 1 7
I want to...
|
by: hemantbasva |
last post by:
Note We need to have a template on server for generating report in multiple sheet
as we do not had msoffice on server
moreover this require a batch job to delete excel file created by the method....
it creates 6 sheets
# region Namespaces
using System;
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |