List Box's and Databases

[ukleaf]

Hi all,

I was wondering if it's possible in Excel to create an Access like database?

Basically I have list of documents I would like to have displayed in a List Box,

DOC1
DOC2
DOC3 etc…

What I then would like to accomplish is to once the user clicks or highlights, say DOC2 it would then display a list of related documents in the second List Box…

I know how to get the list to show in the List Box, what I don't know is how to then associate each line in the List Box with associated text that would be visible in the secondary box. I hope I haven't confused anyone, I'm quite new to Excel...

Thanks for reading this.

UKLeaf

 

[SkipVought]

UKLeaf,

Sure. I'm working on a project that's very similar.

On a separate sheet, load your Document List WITH A COLUMN HEADING in Row 1

On a second separate sheet, load the related documents in a table with headings something like this...
[tt]
Doc
RelatedDoc
[/tt]
So each Doc has one or more related docs.

On a third separate sheet, format a query using Data/Get External Query... where you will select Excel Files as the datablase type and YOUR WORKBOOK as the database and the SECOND sheet as the table. For the time being, return ALL the data to the sheet.

Turn on the macro recorder and EDIT the query you just inserted in the sheet. Turn off the macro recorder and post your code here and I'll show you how to use the results of Listbox1 in your query.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[ukleaf]

Thanks Skip,

I'll give that a go now and let you know what happens...

I think I'll have another question if this works ok.

Many thanks.

UKLeaf

 

[ukleaf]

Hi again,

Is this the code you mentioned?

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=G:\Global\ExcelZipDetails\ZipperInfo.xls;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

What I did is this, created a new worksheet called "ZipperName". This sheet has the names of the "Super" docs, i.e. the names of the main documents. I then created another work sheet called "RelatedZippers". This sheet has the headings of the main zipper, say ADMIN. Then listed below is all the related docs in ADMIN... is that what you ment?

I then created the third work sheet called Quiry, this is the one I imported the data from "RelatedZippers" sheet.

Hope this helps,

Thanks very much.

 

[SkipVought]

Well that's the Connect String for your query -- only a portion of the code that was generated. I'm particularly interested in the SQL that was generated. That's what we need.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[ukleaf]

Sorry Skip,

How would I get the SQL code for you? I tried opening the macro recorder but I may have done it at the wrong time.

Thanks Skip.