Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004

    Unanswered: Linking my database to a spreadsheet

    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.


  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1

    Linking Spreadsheet to Database

    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

    Sub GetProfileData()
    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 & "%';"

    con.Open “DSN File Name”

    rs.Open SQL, con, adOpenStatic, adLockOptimistic

    Range("A1") = "Site:"
    Range("B1") = rs.Fields("Name")
    Range("E1") = "Account Code:"
    Range("F1") = rs.Fields("Number")



    Exit Sub

    MsgBox "Error Number " & Err.Number & " has occured" & vbCr & vbCr & _
    Err.Description, vbCritical, "Programme Error"

    End Sub

    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 !!

    Hope this gives you some ideas.


  3. #3
    Join Date
    Mar 2004

    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).

    Please do demonstrate how these can be done.

    Thank you.

  4. #4
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1


    Hi Jablonski

    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")



    end with

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts