Is there a way of linking my database to a spreadsheet so that information in the spreadsheet is updated when the database is updated. I would like it set up so that statistics and graphs are constantly changing when data is entered into the database.
At the moment i manually do the statistics every time i need them and it takes me ages.
I do not know how familiar you are with VBA in Excel, but I use the ADO object (DAO can be used but I do not kow if it is available in Excel 2k) and retrieve record sets and write it to the predefined area for charting predefined charts every week/month for energy reports etc.
This is a modified extract as an example of defining an retrieving record sets I wrote some while ago.
Dim rs As ADODB.Recordset
Dim con As ADODB.Connection
Dim SQL As String
Dim strCode As String
On Error GoTo ErrorHandler
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
strCode = “AP/01”
SQL = "SELECT * FROM Points WHERE Number Like '%" & strCode & "%';"
The rs and con are module level declarations as they are used in other procedures.
The only other things to do are to set a referance from the VBA Tool menue to the "Microsoft ActiveX Data Object Library" and define a "DSN" file in the control panel 32bit ODBC dialoge box (I think there is a separate location within control panel in Windows 2k and XP) and use this name as the cn.Open argument, or you can define a "DSN less" connection string.
The code could then be run from a buttin on the Worksheet to upate all the required data (I hate cutting and pasting!).
If this approach appeals and you want any help just post a note.
I am sure others may well suggest using the "Get External Data" facility in Excel but I never did get the hang of that !!
I posed a similar question awhile ago, but got no responses, so I am glad you have responded to this one.
I would appreciate a detailed - and "friendly" - guide to what you propose (I am not used to VBA in Excel, just some basic stuff in Access). At the moment, I have a form with buttons that output Access queries to Excel. It works using the DoCmd.OutputTo [acOutputQuery] which outputs the query results to an Excel spreadsheet while also opening the application.
But, this gives limited functionality - I cannot specify the worksheet or workbook by name (which would allow me to have another spreadsheet to chart the imported results dynamically).
Sorry about the delay in relying but been a bit busy, probably because I'm on holiday untill Monday. Anyway, I realy did not know where to start given your limited VBA in Excel but I have knocked together a demo sheet for you try. This should work on Office 97and office 2k as they both have the Nothwinds.mdb DB in the same place (assuming standard installation on C: drive.
Its not very complex but does indicate DSN-less string connection for the ADO recordset and a simple SQL select query and how to write the recordset returned to a spresdsheet. I have included comment that should help(!). This example writes to the active sheet but you can write to other sheets (even hidden sheets) ie
With Sheets("Target Sheet Name")
You can also use the object browser for info/objects/properties/methods for Exel and ADO. Don't forget to set a referance to the ADO libary in any W/books you create (look at the Tool>Referances of the attached example in the VB window.
There are many other thing/objects that are useful when you get the hang of returning record sets, which gives you great flexibility and possibilites.
Good Luck !!
ps you can define the query in the Access DB if it is complex and use that in the SQL in stead of a table/join statment ie
"SELECT * from qryName WHERE fieldName = '" & strVariable & "'"
also ADO use a % sign as a wild card where DAO uses a * in LIKE filters