Results 1 to 5 of 5

Thread: Excel + Database + Automated

  1. #1
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    5 times in 5 posts

    Excel + Database + Automated

    Okay, so here's the deal.

    At work, I send out reports on a weekly basis which I currently have to manually do. What I want to do is make them completely automated. I've thought about writing software to do it, but the report needs to be sent out as an excel spreadsheet, but what I also knew was that excel can in fact interact with databases.

    I attempted to do so but the majority of my results need to be returned as amounts as opposed to actual values. I'm basically counting the amount of certain queries, shoving them in the spreadsheet, then updating the graphs within the spreadsheet.

    I could use a cronjob to execute them on a weekly basis, my problem is using a COUNT operation in the queries, and adding new columns into the excel spreadsheet, then updating the graphs and e-mailing them out to the respective people.

    From the sounds of it, it would actually be better to write an app to do this, but I was just wondering if anyone knew if excel could actually import results from a COUNT operation? And if I could set it to do so on a weekly basis?


  2. #2
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Questioning it all
    48 times in 43 posts
    What database is it?, if its MS SQL reporting services does this so easy its untrue.

    -- Hexus Meets Rock! --

  3. #3
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    5 times in 5 posts
    Well it uses ODBC so I'm assuming it's an Oracle DB but it's remote, I don't control it, and it's absolutely massive.

  4. #4
    Senior Member
    Join Date
    Aug 2005
    35 times in 22 posts
    • toolsong's system
      • Motherboard:
      • ASUS P7P55D EVO
      • CPU:
      • i7 860 @ 3.8 GHz
      • Memory:
      • 2 x 4GB Corsair Vengeance
      • Storage:
      • SSDs
      • Graphics card(s):
      • GTX970
      • PSU:
      • Seasonic X650
      • Case:
      • Antec P180
      • Operating System:
      • Win7 x64
      • Monitor(s):
      • Dell U2515H
      • Internet:
      • Fibre @ 100/40
    This is all fairly easy to do with ODBC and VBA in Excel.

    You should download the MDAC SDK from MSDN "mdac28sdk.msi", as that is where you will find the help files with VBA examples that contain everything you need for the database side.

    Whatever you need to do in Excel can be found by recording a macro doing what you need to manually and then looking at what objects, methods and properties are referenced in the recording and reading their help

    Here's a sample connecting to an Oracle DB and fetching a SQL into a recordset.

    Dim dbConnection As New ADODB.Connection
    Dim dbRecordset As New ADODB.Recordset
    Dim dbErrors As ADODB.Error
    Dim dbFieldNames As ADODB.Fields
    Dim dataArray() As Variant
    dbConnection.Open "Provider=MSDAORA;Data Source=" & DB & ";User ID=" & UID & ";Password=" & PWD & ";"
    If dbConnection.State = adStateOpen Then
        Application.StatusBar = "Database is executing the query"
        dbRecordset.CursorLocation = adUseServer
        dbRecordset.Open SQLtxt, dbConnection, adOpenForwardOnly, adLockReadOnly, adCmdText
        Set dbFieldNames = dbRecordset.Fields
        If dbRecordset.BOF And dbRecordset.EOF Then ' no rows in recordset

  5. #5
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    5 times in 5 posts
    Yeh, I write tools in C#/C++ at work to work with Oracle databases. I think I may as well write an application to store data to a local DB, because this is all being stored in an excel sheet at the moment whereas it should have a more permanent location. I can then automate it to do it every day as opposed to every week making it much more precise.

    I think that'd be the easier option, then from there I can pull it out of my local DB into the excel spreadsheet, but automate that also.

    I'll look into the macros and VBA coding in excel though as I'm still going to need it by the looks of things

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 22-12-2005, 01:41 AM
  2. V3 Cardbox database app promises power and accessibility
    By Bob Crabtree in forum HEXUS News
    Replies: 3
    Last Post: 05-12-2005, 07:10 PM
  3. Replies: 3
    Last Post: 17-10-2005, 02:31 PM
  4. Replies: 6
    Last Post: 12-08-2005, 08:50 AM
  5. abnormal access database traffic?
    By Stoo in forum Networking and Broadband
    Replies: 8
    Last Post: 06-10-2004, 04:43 PM

Posting Permissions

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