Excel + Database + Automated

[Kezzer]

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?

Cheers

[TiG]

What database is it?, if its MS SQL reporting services does this so easy its untrue.

TiG

[Kezzer]

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.

[toolsong]

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

[Kezzer]

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