Excel database - Reference material 2

[ChrisBurch]

Hi All,

Can anyone point me at any usable reference material for Excel Databases.

Thanks, Chris

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[xlbo]

Yup - don't use excel as a database - it's a spreadsheet
Unless you have small amounts of data, seriously consider using access as a backend and excel as a front end

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ChrisBurch]

I use Access for where I need RDBs, but in this case it's only small numbers of records that I want to add to existing spreadsheets. Once I've got the database working properly, with all the relevant formulae in place, then my users do all the normal 'spreadsheety' things that they normally do.

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[dkriese]

IF you download from Microsoft Template Wizard with Data Traking it will create an excel database of your data entry from a template to the excel database, which is a workbook with the same name as your template with the word database added, and a worksheet named Table1

 

[Dreamboat]

I dunno about the template wizard and am not opining either way.

However, I've worked with a gazillion users that created their Excel file and found all its faults AFTER they designed everything around it.

Best thing you can do for yourself and your users is set up the worksheets the way Excel works with them best. See my mini-dissertation here:

http://www.theofficeexperts.com/excel.htm#FileLayoutForDatabases

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[DaleWatson123321]

Chris,

I don't know of any references. However, I do have some example files I can email you - as a zip file.

Also, if anyone else is interested, please feel free to ask for the files.

These files are a "mix" of different types of applications involving the use of both database functions (e.g. DSUM, DCOUNTA) and data-manipulation using VBA - i.e. extracting selective data based on the criteria you specify. Included are comments regarding setting up the criteria, creating range names, etc.

These are primarily simple files - intended not to overwhelm those searching for the "basics" in the use of spreadsheet-based databases. And there are a couple of files that are a little "more advanced" - to provide examples of what can be accomplished.

Lastly, I feel a need to comment on the "suitability" of using "spreadsheet-based" databases versus the use of Access. I naturally agree that where an application is such that it "truly" requires the functionality of a relational and multi-user database, then other databases should be used.

However, relational databases are not the "be all and end all". I've encountered MANY users of such RDBs who have a problem in generating the type of "professional" reports that have become relatively easy to generate in the spreadsheet world.

There appears to be a "growing recognition" that software such as Excel can and should be used to generate (preferably automatically using VBA) the "professional" looking reports for the "font-end" recipients - which might include general users, management and the public. In such situations, it can be a case of providing these end-users with a "template" that has already been "automated". Then, periodically they can import "data-dumps" from the mainframe-based databases. The automation can include allowance for the end-users to generate either pre-set reports, and/or generate "custom" reports based on the end-user's choice of criteria for the selection of records from the data-dump.

It would be appropriate if Microsoft would "also" recognize this "growing recognition". They could help IMMENSELY by including in their NEXT release of Excel, four data-manipulation functions they neglected to include based on the terrific example of Lotus 123 Release 5. Specifically, these include: Data-Find, Data-Modify, Data-Delete and Data-Append.

The inclusion of these four data-manipulation functions will DEFINITELY be VERY much appreciated by ALL Excel users. This, combined with the obvious "missing" HELP and TUTORIAL information on this database functionality, will go a LONG way in convincing Excel users to upgrade to the version of Excel containing these added components.

In many cases, the "squeaky" wheel gets the grease. So I'm (still) hopeful that Tek-Tips members will contribute as best you can to get Microsoft's attention. Anyone have any "connections" ???

As mentioned, email me if you'd like the set of Excel database example files (contained in a zip file).

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

Dale: I'll tell Bill when he comes over for our soiree on Saturday. Okay by you?

ROFL!!

Wish #2: Access reports could be saved as true Word documents instead of RTF.



Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[SkipVought]

I guess I'll weigh in on this 1 2.

If you are attempting to UPDATE/INSERT into an Excel table, I'll advise NO, NO, NO!!!

If you have some small tables as you described, here is my technique (I use "tbl" as a suffix to each table name, ie tblCustomers)...

1. Name the sheet as your table name

2. run this procedure in the Workbook_SheetChange event (ONE PLACE ONLY!)

Sub ResizeTable()
    Dim sSheetName As String, sAddress As String
    On Error Resume Next
    If Left(ActiveSheet.Name, 3) <> &quot;tbl&quot; Then Exit Sub
    Application.DisplayAlerts = False
    With Cells(1, 1).CurrentRegion
        sAddress = .Address
        .CreateNames _
            Top:=True, _
            Left:=False, _
            Bottom:=False, _
            Right:=False
    End With
    sSheetName = ActiveSheet.Name
    ActiveWorkbook.Names.Add _
        Name:=sSheetName, _
        RefersTo:=&quot;='&quot; & sSheetName & &quot;'!&quot; & sAddress
    Application.DisplayAlerts = True
End Sub

This names each column range and also names the table automatically with the sheet name. This makes your SQL easier to write and very consistant I think.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ChrisBurch]

I just realised that there had been a flurry of activity on this thread, a month after I thought it had died a natural death. Thanks to everone that threw their 2c in. I did manage to solve my problem, and found the Database functions to be excellent(horses for courses).

Dale, if you're are still willing to send those examples, I would love to look at them.

Thankss, Chris
chrisb@abmflexibles.aust.com

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[ChrisBurch]

I'm back here because Dale sent a plethora of sample files, and I've now got most of the way through reviewing them.

Firstly, a HUGE!!! thank you to Dale for supplying them. As an educational tool they are brilliant, and the amount of effort Dale has put in commenting everything he did in them is also beyond the pale (remember Cute Quiz?).

Secondly, if anyone interested in some very smart filtering in excel databases, then maybe if you ask Dale very nicely he'll send you what he sent me.

Thirdly, my uses are starting to get some real benefits from me using excel database functionality.....but it's only the tip of the iceburg!!

Finally, my comments are not meant to single out Dale as being the only/main help in this forum. There are a lot of other peiople out there (eg. skip) who are also doing magical things for us all. Thanks to them too!!

ps. Why aren't you using Dynamic named ranges Dale????

Chris

It worked yesterday.
It doesn't work today.
That's Windows!