I am trying to figure how to use ADO to retrieve/store data into closed
workbook to get hopefully significant performance enhancement what I
currently do (open and close 100 workbooks)

I looked at Ron's code for retrieving data

http://www.rondebruin.nl/ado.htm

(Thanks Ron).

My question: is it possible to find out what the last row and column is
in the closed workbook.?

My other rookie question is:

Can I store a formula into a cell of a closed workbook using ADO. I am
assuming that if this is possible then the value will only be determined
when the workkbook is opened, or is there another way.

THank you for any suggestions

kurb

kurb wrote ...

I am trying to figure how to use ADO to retrieve/store data into closed
workbook to get hopefully significant performance enhancement what I
currently do (open and close 100 workbooks)
My question: is it possible to find out what the last row and column is
in the closed workbook.?

Using ADO on Excel data:
- You can find out the name of the last column.
- You can find the last value in a column.
- You can count the number of columns and the number of rows.
- If you know where the table begins on the sheet (e.g. cell A1), you
can use the row count and column count to determine the address of the
last cell in the last column.
- By trial and error, you could find out the address of the top left
cell of the table but you would probably lose the performance
advantage.

Can I store a formula into a cell of a closed workbook using ADO. I am
assuming that if this is possible then the value will only be determined
when the workkbook is opened

If you tried to insert a formulas into a cell it would be seen as
plain text, even when it was opened in the Excel UI. You'd need to
're-enter' the cell contents in some way e.g. click into the formula
bar and click out again.

Jamie.

--

Hi Kurb

Open the files and do the things you want
http://www.rondebruin.nl/copy3.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"kurb" wrote in message ...


I am trying to figure how to use ADO to retrieve/store data into closed workbook to get hopefully significant performance
enhancement what I currently do (open and close 100 workbooks)

I looked at Ron's code for retrieving data

http://www.rondebruin.nl/ado.htm

(Thanks Ron).

My question: is it possible to find out what the last row and column is in the closed workbook.?

My other rookie question is:

Can I store a formula into a cell of a closed workbook using ADO. I am assuming that if this is possible then the value will only
be determined when the workkbook is opened, or is there another way.

THank you for any suggestions

kurb

Thank you very much.

I always know where the table beigins so thats ok.

I could not find any method for recordset (in the book that I have)
related to row count and column count
Appreciate a little more clarification please.

On the formula issue, I think that I have to do the calculations on a
local worksheet and then store with ADO, though I will lose on performance.

Thanks
Kurb

Jamie Collins wrote:

kurb wrote ...



I am trying to figure how to use ADO to retrieve/store data into closed
workbook to get hopefully significant performance enhancement what I
currently do (open and close 100 workbooks)
My question: is it possible to find out what the last row and column is
in the closed workbook.?



Using ADO on Excel data:
- You can find out the name of the last column.
- You can find the last value in a column.
- You can count the number of columns and the number of rows.
- If you know where the table begins on the sheet (e.g. cell A1), you
can use the row count and column count to determine the address of the
last cell in the last column.
- By trial and error, you could find out the address of the top left
cell of the table but you would probably lose the performance
advantage.



Can I store a formula into a cell of a closed workbook using ADO. I am
assuming that if this is possible then the value will only be determined
when the workkbook is opened



If you tried to insert a formulas into a cell it would be seen as
plain text, even when it was opened in the Excel UI. You'd need to
're-enter' the cell contents in some way e.g. click into the formula
bar and click out again.

Jamie.

--

Thanks for the response, appreciate it.

I may have got this wrong, but does this work for closed workbooks.?

I was looking for an ADO solution, not only for performance gain, but
also because of setting up a simple relational database later.

But if there are other ways of manipulating closed workbooks (eg
inserting rows, formulas) I would be interested in following up on that.

Thanks
Kurb




Ron de Bruin wrote:

Hi Kurb

Open the files and do the things you want
http://www.rondebruin.nl/copy3.htm

If you want to add formulas in the workbooks go for the Open method
It is also fast.

More ADO examples you can find here
http://www.erlandsendata.no/english/...php?t=envbadac

--
Regards Ron de Bruin
http://www.rondebruin.nl


"kurb" wrote in message ...
Thanks for the response, appreciate it.

I may have got this wrong, but does this work for closed workbooks.?

I was looking for an ADO solution, not only for performance gain, but also because of setting up a simple relational database
later.

But if there are other ways of manipulating closed workbooks (eg inserting rows, formulas) I would be interested in following up
on that.

Thanks
Kurb




Ron de Bruin wrote:

Hi Kurb

Open the files and do the things you want
http://www.rondebruin.nl/copy3.htm

kurb wrote ...

I always know where the table beigins so thats ok.

I could not find any method for recordset (in the book that I have)
related to row count and column count
Appreciate a little more clarification please.

Money where mouth is:

Sub test()

Const FULL_FILENAME As String = "" & _
"C:\Tempo\db.xls"

Dim strLastColName As String
strLastColName = LastColumnName( _
FULL_FILENAME, "Sheet1$")

MsgBox strLastColName

Dim vntLastColLastval As Variant
vntLastColLastval = LastValueInColumn( _
FULL_FILENAME, "Sheet1$", _
strLastColName)

If IsNull(vntLastColLastval) Then
MsgBox "(Value is null)"
Else
MsgBox CStr(vntLastColLastval)
End If

Dim lngCols As Long
lngCols = ColumnCount( _
FULL_FILENAME, "Sheet1$")
MsgBox CStr(lngCols)

Dim lngRows As Long
lngRows = RowCount( _
FULL_FILENAME, "Sheet1$")
MsgBox CStr(lngRows)

End Sub

Public Function LastColumnName( _
ByVal FullFilename As String, _
ByVal TableName As String _
) As String

Dim Con As Object
Dim rs As Object
Dim strCon As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(4, _
Array(Empty, Empty, TableName, Empty))

End With


With rs

.ActiveConnection = Nothing
Con.Close

.Sort = "ORDINAL_POSITION DESC"
LastColumnName = _
.Fields("COLUMN_NAME").Value

End With

End Function

Public Function LastValueInColumn( _
ByVal FullFilename As String, _
ByVal TableName As String, _
ByVal ColumnName As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const SQL As String = "" & _
"SELECT <COL_NAME FROM [<TABLE_NAME];"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Build sql
strSql1 = SQL
strSql1 = Replace(strSql1, "<TABLE_NAME", TableName)
strSql1 = Replace(strSql1, "<COL_NAME", ColumnName)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open
Set rs = .Execute(strSql1)

End With

With rs

.ActiveConnection = Nothing
Con.Close

.MoveLast

LastValueInColumn = _
.Fields(0).Value

End With

End Function

Public Function ColumnCount( _
ByVal FullFilename As String, _
ByVal TableName As String _
) As Long

Dim Con As Object
Dim rs As Object
Dim strCon As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(4, _
Array(Empty, Empty, TableName, Empty))

End With

With rs

.ActiveConnection = Nothing
Con.Close

ColumnCount = _
.RecordCount

End With

End Function


Public Function RowCount( _
ByVal FullFilename As String, _
ByVal TableName As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const SQL As String = "" & _
"SELECT COUNT(*) FROM [<TABLE_NAME];"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Build sql
strSql1 = SQL
strSql1 = Replace(strSql1, "<TABLE_NAME", TableName)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open
Set rs = .Execute(strSql1)

End With

With rs

.ActiveConnection = Nothing
Con.Close

.MoveLast

RowCount = _
.Fields(0).Value

End With

End Function


Jamie.

--

kurb wrote ...

I ran into two problems with modifications I made

1. The FULL_NAME I used came back with an error that it was too long,
so I am assuming that the maximum lenght for this must be set somewhere.

2. I tried to use a Sheetname instead of "Sheet1$" for TABLE-NAME, but
it wouldn't work

The best approach is to find out what Jet thinks the sheet/table name
is and try using that name e.g. this code fetches all the tables'
(worksheets and valid book-level and sheet-level defined Names) names
for a workbook:

Sub Test2
Const FULL_FILENAME As String = "" & _
"C:\Tempo\db.xls"
Dim vntArray As Variant
vntArray = ExcelTableNames(FULL_FILENAME)
MsgBox Join(vntArray, vbCrLf)
End Sub

Public Function ExcelTableNames( _
ByVal FullFilename As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim lngRows As Long
Dim lngCounter As Long
Dim strTablesNames() As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(20)

End With


With rs

.ActiveConnection = Nothing
Con.Close

lngRows = .RecordCount
ReDim strTablesNames(lngRows - 1)
For lngCounter = 0 To lngRows - 1
strTablesNames(lngCounter) = !TABLE_NAME
.MoveNext
Next

End With

ExcelTableNames = strTablesNames

End Function


Jamie

--

Just side-tracking a bit...
Did I read that as of ADO 2.8 Jet is no longer included?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jamie Collins" wrote in message
om...
kurb wrote ...

I ran into two problems with modifications I made

1. The FULL_NAME I used came back with an error that it was too long,
so I am assuming that the maximum lenght for this must be set somewhere.

2. I tried to use a Sheetname instead of "Sheet1$" for TABLE-NAME, but
it wouldn't work

The best approach is to find out what Jet thinks the sheet/table name
is and try using that name e.g. this code fetches all the tables'
(worksheets and valid book-level and sheet-level defined Names) names
for a workbook:

Sub Test2
Const FULL_FILENAME As String = "" & _
"C:\Tempo\db.xls"
Dim vntArray As Variant
vntArray = ExcelTableNames(FULL_FILENAME)
MsgBox Join(vntArray, vbCrLf)
End Sub

Public Function ExcelTableNames( _
ByVal FullFilename As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim lngRows As Long
Dim lngCounter As Long
Dim strTablesNames() As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(20)

End With


With rs

.ActiveConnection = Nothing
Con.Close

lngRows = .RecordCount
ReDim strTablesNames(lngRows - 1)
For lngCounter = 0 To lngRows - 1
strTablesNames(lngCounter) = !TABLE_NAME
.MoveNext
Next

End With

ExcelTableNames = strTablesNames

End Function


Jamie

--

Thank you Jamie - I 'll give it a spin.

A change in question:
I am rethinking my overall appraoch to solving my problem. The problem
being all of my data is stored in hundreds of bulky workbooks, and I
need to establish relationships between various colums in these
workbooks as well as execute formulas involving columns from various
workbooks.

I can use Excel for defining the worksheet templates and the equations
for calculating the column data, but instead of doing all of this in the
remote individual workbooks, I am thinking of doing this locally in
Excel and storing it in the closed Excel workbooks using ADO. I
currently bring all the data into a huge array which is indexed to the
scattered workbook/worksheet/column filing system.

I'm wondering whetgher it would make more sense to store all of this in
Access database, for the added flexibility of defining relationships and
sorting data based on these relationships. Instead of having a huge
array in Excel ( not a problem at this stage), presumably I can just as
easily call upon those colums in the Access database , manipulate them
in Excel, and update the database accordingly. I will also have to draw
the data from Access to display/chart them in Excel

Can this be done within Excel using ADO. If I creat the basic tables
from within Access, can I modify Table attributes, Table contents, and
make queries from within Excel

I wonder if there are any thoughts regarding the approach
Thank you for any suggestions

Kurb
My question is

Jamie Collins wrote:

kurb wrote ...



I ran into two problems with modifications I made

1. The FULL_NAME I used came back with an error that it was too long,
so I am assuming that the maximum lenght for this must be set somewhere.

2. I tried to use a Sheetname instead of "Sheet1$" for TABLE-NAME, but
it wouldn't work



The best approach is to find out what Jet thinks the sheet/table name
is and try using that name e.g. this code fetches all the tables'
(worksheets and valid book-level and sheet-level defined Names) names
for a workbook:


--

"Rob van Gelder" wrote ...

Did I read that as of ADO 2.8 Jet is no longer included?

MDAC 2.8 Overview: Deprecated Components
http://msdn.microsoft.com/library/de...components.asp

"The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this
component."

Take that last statement with a pinch of salt because MS have
maintained security releases for Jet 4.0 e.g.

How to obtain the latest service pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/default...b;en-us;239114

"The Microsoft Jet Security Bulletin MS04-014: Vulnerability in the
Microsoft Jet Database Engine could permit code execution contains the
following files. These files were updated on April 13, 2004"

And the word from the MS Access NDAs suggests MS are currently working
on Jet, although further security updates are far more likely than bug
fixes or new features.

Is Jet 4.0 available as a download from MS? I've never fully
understood whether Jet 4.0 itself is a prerequisite for downloading
the latest Jet 4.0 service pack.

Jamie.

--

kurb wrote...

I am rethinking my overall appraoch to solving my problem.
I'm wondering whetgher it would make more sense to store all of this in
Access database

Yes, this sounds like the way to go.

for the added flexibility of defining relationships and
sorting data based on these relationships.

Plus you get strong data typing, default values, constraints, data
validation, indexes, etc.

I will also have to draw
the data from Access to display/chart them in Excel

Can this be done within Excel using ADO.

Indeed it can, using a recordset based on a SELECT query.

If I creat the basic tables
from within Access, can I modify Table attributes, Table contents, and
make queries from within Excel

Table contents: of course, using SQL DML e.g. UPDATE, INSERT INTO,
DELETE. Alternatively, by using an updateable recordset.

Table attributes and queries: yes, you can do this from Excel using
ADO (with restrictions e.g. connection requires permissions, all other
connections closed, etc). It is not usual for a client application to
modify table/column definitions or create stored procedures/queries on
the fly but it can be done: say you have a database admin type program
in Excel. You can use ADOX objects (Tables, Columns, etc) but I prefer
SQL DDL e.g. ALTER TABLE, CREATE PROCEDURE, etc.

Jamie.

--

Jamie, thank you so much for your support

I want to get the big picture right.

Currently I have a hierarchical grouping of files, workbooks,
worksheets, columns and a mirror Array which is indexed to this
structure. I can easily call a specific column belonging to a pecific
worksheet belonging to a spefic workbook....using the structure
spArray(fileName, wbName,shName).Table(rwNumber,colNumber) = cell value,
and I can easily sweep through the database using this approach. I can
also easily copy the entire table in the worksheet into the array with
Table= Range.Value. In this arrangement then I can also have the same
shName if they belong to a different wbName etc

If I do all of this in an Access database can I manintain the same
structure. I'll have hundreds of TableName(s) (assuming equivalent of
shName(s)). Can I structure as follows Shelf (Book1,Book2,....Bookn),
Book1(Table1,Table2...Tablem), Table1(field1,field2...). where Book2
may have the same TableName (with different contents) as one in Book1.

Something like DBengine.Workspaces(x).Databases(y) where x ponts to
"fileName" and y to "wbName" from above?? And if so this means I have my
database spread over several .mdb files. Does this make sense, and can I
then create relationships with the user interface across .mdb files??

Thanks for any suggestions

Kurb


Jamie Collins wrote:

kurb wrote...



I am rethinking my overall appraoch to solving my problem.
I'm wondering whetgher it would make more sense to store all of this in
Access database



Yes, this sounds like the way to go.



for the added flexibility of defining relationships and
sorting data based on these relationships.



Plus you get strong data typing, default values, constraints, data
validation, indexes, etc.



I will also have to draw
the data from Access to display/chart them in Excel

Can this be done within Excel using ADO.



Indeed it can, using a recordset based on a SELECT query.



If I creat the basic tables
from within Access, can I modify Table attributes, Table contents, and
make queries from within Excel



Table contents: of course, using SQL DML e.g. UPDATE, INSERT INTO,
DELETE. Alternatively, by using an updateable recordset.

Table attributes and queries: yes, you can do this from Excel using
ADO (with restrictions e.g. connection requires permissions, all other
connections closed, etc). It is not usual for a client application to
modify table/column definitions or create stored procedures/queries on
the fly but it can be done: say you have a database admin type program
in Excel. You can use ADOX objects (Tables, Columns, etc) but I prefer
SQL DDL e.g. ALTER TABLE, CREATE PROCEDURE, etc.

Jamie.

--

Thanks for that info

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jamie Collins" wrote in message
om...
"Rob van Gelder" wrote ...

Did I read that as of ADO 2.8 Jet is no longer included?

MDAC 2.8 Overview: Deprecated Components
http://msdn.microsoft.com/library/de...components.asp

"The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this
component."

Take that last statement with a pinch of salt because MS have
maintained security releases for Jet 4.0 e.g.

How to obtain the latest service pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/default...b;en-us;239114

"The Microsoft Jet Security Bulletin MS04-014: Vulnerability in the
Microsoft Jet Database Engine could permit code execution contains the
following files. These files were updated on April 13, 2004"

And the word from the MS Access NDAs suggests MS are currently working
on Jet, although further security updates are far more likely than bug
fixes or new features.

Is Jet 4.0 available as a download from MS? I've never fully
understood whether Jet 4.0 itself is a prerequisite for downloading
the latest Jet 4.0 service pack.

Jamie.

--

kurb wrote ...

I want to get the big picture right.

Currently I have a hierarchical grouping of files, workbooks,
worksheets, columns and a mirror Array which is indexed to this
structure. I can easily call a specific column belonging to a pecific
worksheet belonging to a spefic workbook....using the structure
spArray(fileName, wbName,shName).Table(rwNumber,colNumber) = cell value,
and I can easily sweep through the database using this approach. I can
also easily copy the entire table in the worksheet into the array with
Table= Range.Value. In this arrangement then I can also have the same
shName if they belong to a different wbName etc

If I do all of this in an Access database can I manintain the same
structure. I'll have hundreds of TableName(s) (assuming equivalent of
shName(s)). Can I structure as follows Shelf (Book1,Book2,....Bookn),
Book1(Table1,Table2...Tablem), Table1(field1,field2...). where Book2
may have the same TableName (with different contents) as one in Book1.

Something like DBengine.Workspaces(x).Databases(y) where x ponts to
"fileName" and y to "wbName" from above?? And if so this means I have my
database spread over several .mdb files. Does this make sense, and can I
then create relationships with the user interface across .mdb files??

Yes, you could model this structure in multiple .mdb files, say with a
'master' .mdb with tables linked to the others. Or you could do joins
between .mdbs on the fly using Jet's pass through technology. I don't
think you'd be able to write meaningful SQL queries e.g. determine
which .mdb you need using pure SQL, so you'd still have to use
procedural e.g. VBA code. There would be some advantages but it
wouldn't justify a re-write if you plan to maintain the same
structure.

I think to get the big picture right you need to make some big
changes.

I'm not sure how 'file' differs from 'workbook'. so I'll read as
'folder'. Your structure seems to be:

Folder
Workbook
Worksheet
Row

You mention 'shelf' and 'book' so let's say we are modelling a
traditional library of paper copy books. You seem to be proposing a
hierarchical structure like the following (think of an expanded tree
view):

floor
room
shelf
book
floor
room
shelf
book
floor
room
shelf
book

But what if this library had another top level, say it had multiple
buildings? How would you add another physical level to your structu
partition you hard drive, have multiple machines on a network <g?

I think you have the wrong model. The preferred model is the
relational model, hence relational database.

I see an entity 'book' having an attribute 'location'. It could be as
simple as that: the location data item could contain metadata about
the floor/room/shelf e.g. first character represents the floor, next
two characters the room code etc. The other elements could be entities
in their own rights and the book location could be a compound of the
ID for each. It depends on the bounds of what you are modelling. For
example, how dynamic is you filing system: do you file in order of
date received, never throw out any books so they always remain in the
same location (and need to use the index to find the locations of all
the books about Excel)? or do you file all the computer application
books together and have to shift the palaeontology section to a new
room because the librarian just ordered every book John Walkenbach has
written?

OK, so I've extrapolated a lot, went off on a tangent even. But you
see how data modelling involves starting from scratch and looking at
things as entities as attributes. You probably proposed the structure
you did because you *don't* want to have to start again!

Excuse me for being blunt he I think you had to write your spArray
routine (and your routines to operate on the resulting array) because
your structure is wrong. If you had the correct structure you could
use SQL code 'out of the box', even if the data was in Excel. SQL was
invented to retrieve data and has been an indubitable success for
twenty or so years. It is extremely unlikely you have invented a
better system <g. Maybe you used multiple worksheets/workbooks
because you had too much data: again, alarm bells should go off that
you are perhaps using the wrong tool for the job.

There is a sliver lining: because of your rigid structure your data
will be easy to convert when you've come up with a new structure,
which is what I think you need to do if you want change. But it may be
best to stick with the current system, assuming it is working, albeit
a bit slow (processors are getting faster <g). I may be wrong but I'm
guessing those arrays aren't exactly a joy to work with...

Jamie.

--

Thanks (again) for your thoughts regarding the big picture for
integrating my Excel appliction with Access using ADO. Let me explain my
application to give you a better idea of what I am trying to do.

1. The data is stored across multiple folders/workbooks/worksheets.
(Yes I couldn't do it any other way with Excel due to performance
limitation). All the rows are date stamped and the sequential order of
the rows are ciritical. All the rows across every worksheet (belonging
to specified set of workbooks/folders) are date synchronized, ie every
row1 has the same date. Every day I get a new row of data for aall the
worksheets. Except for the date all the other cell values are numbers
that are drawn from an external source or statistically derived in Excel

2. Each Excel colum has five headers (Floor.Room.Shelf.Book.. concept).
These headers are meaningful to the User as she specifies them and uses
them in equations to derive more new statistics down new columns. The
data for the column headers she specifies are dropped onto the worksheet
from spArray. The statistics are calculated by Excel on the worksheet.
I now want to do some of the calculations in VBA for special equations.
Say the User inputs something like X(5) + Y(1). Here I need to
associate the symbol X with the five part column header (so I can use it
to identify the column in the). The number refers to the offset from the
current row .

3. Once I have the base data I want to do a lot of queries. Something
like. Sort the values of paramter X (from Start date to Stop date) to
into ten bins (eg histogram) and store them For all the values of X
that fall in Bin1 find the corresponding values (by date) for parameters
Y and Z. Now lets sort the valus we just found for Y into ten bins. Or,
look at data every 5th day beginning from Start date. Then plot and
chart the results.

I have to retain the Floor.Room.Shelf...with date sequenced rows
structure only from the Users perspective.The User specifies exisiting
collumns (for which data must be extracted from the database), defines
new columns headings and equations to create the new data. The User may
review the results that fall on these worksheets and store them in the
database if desires for later review.

I can see from what you're telling me that all three areas can benefit
from integrating with Access. So the big question: what should the
Access archtecture look like. My first response was to map existing
Excel worksheet into an Access Table. Since the sequencing is critical
to the application and since every day I get a new row of data if lump
the data from different workbooks onto one Table I will not be able to
maintain sequence integrity for all my calculations, and also for
plotting time series data. Also the User relates to each Worksheet for
analysis so a unique worksheet/tablename relationship would be convenient.

Does it make sense and can I implement a hierarchical arrangement of
tables in Access?

Really appreciate any thoughts or suggestions.
Thank you
Kurb


Jamie Collins wrote:

kurb wrote ...



I want to get the big picture right.

Currently I have a hierarchical grouping of files, workbooks,
worksheets, columns and a mirror Array which is indexed to this
structure. I can easily call a specific column belonging to a pecific
worksheet belonging to a spefic workbook....using the structure
spArray(fileName, wbName,shName).Table(rwNumber,colNumber) = cell value,
and I can easily sweep through the database using this approach. I can
also easily copy the entire table in the worksheet into the array with
Table= Range.Value. In this arrangement then I can also have the same
shName if they belong to a different wbName etc

If I do all of this in an Access database can I manintain the same
structure. I'll have hundreds of TableName(s) (assuming equivalent of
shName(s)). Can I structure as follows Shelf (Book1,Book2,....Bookn),
Book1(Table1,Table2...Tablem), Table1(field1,field2...). where Book2
may have the same TableName (with different contents) as one in Book1.

Something like DBengine.Workspaces(x).Databases(y) where x ponts to
"fileName" and y to "wbName" from above?? And if so this means I have my
database spread over several .mdb files. Does this make sense, and can I
then create relationships with the user interface across .mdb files??



Yes, you could model this structure in multiple .mdb files, say with a
'master' .mdb with tables linked to the others. Or you could do joins
between .mdbs on the fly using Jet's pass through technology. I don't
think you'd be able to write meaningful SQL queries e.g. determine
which .mdb you need using pure SQL, so you'd still have to use
procedural e.g. VBA code. There would be some advantages but it
wouldn't justify a re-write if you plan to maintain the same
structure.

I think to get the big picture right you need to make some big
changes.

I'm not sure how 'file' differs from 'workbook'. so I'll read as
'folder'. Your structure seems to be:



Folder


Workbook
Worksheet
Row

You mention 'shelf' and 'book' so let's say we are modelling a
traditional library of paper copy books. You seem to be proposing a
hierarchical structure like the following (think of an expanded tree
view):



floor


room
shelf
book


floor


room
shelf
book


floor


room
shelf
book

But what if this library had another top level, say it had multiple
buildings? How would you add another physical level to your structu
partition you hard drive, have multiple machines on a network <g?

I think you have the wrong model. The preferred model is the
relational model, hence relational database.

I see an entity 'book' having an attribute 'location'. It could be as
simple as that: the location data item could contain metadata about
the floor/room/shelf e.g. first character represents the floor, next
two characters the room code etc. The other elements could be entities
in their own rights and the book location could be a compound of the
ID for each. It depends on the bounds of what you are modelling. For
example, how dynamic is you filing system: do you file in order of
date received, never throw out any books so they always remain in the
same location (and need to use the index to find the locations of all
the books about Excel)? or do you file all the computer application
books together and have to shift the palaeontology section to a new
room because the librarian just ordered every book John Walkenbach has
written?

OK, so I've extrapolated a lot, went off on a tangent even. But you
see how data modelling involves starting from scratch and looking at
things as entities as attributes. You probably proposed the structure
you did because you *don't* want to have to start again!

Excuse me for being blunt he I think you had to write your spArray
routine (and your routines to operate on the resulting array) because
your structure is wrong. If you had the correct structure you could
use SQL code 'out of the box', even if the data was in Excel. SQL was
invented to retrieve data and has been an indubitable success for
twenty or so years. It is extremely unlikely you have invented a
better system <g. Maybe you used multiple worksheets/workbooks
because you had too much data: again, alarm bells should go off that
you are perhaps using the wrong tool for the job.

There is a sliver lining: because of your rigid structure your data
will be easy to convert when you've come up with a new structure,
which is what I think you need to do if you want change. But it may be
best to stick with the current system, assuming it is working, albeit
a bit slow (processors are getting faster <g). I may be wrong but I'm
guessing those arrays aren't exactly a joy to work with...

Jamie.

--

OK, I think I went off in the wrong direction. Your data sounds far
more abstract than I appreciated and may not actually be relational.
OLAP and multidimensional databases could be a better model for your
circumstances.

I'll bow out gracefully and leave you with a link and a 'good luck':

Programming OLAP Databases from Microsoft Access Using DSO:
http://msdn.microsoft.com/office/und..._accessdso.asp

Jamie

--