Thanks Andy!! SQL.REQUEST did the trick!
-----Original Message-----
Take a look at the SQL.REQUEST function.

You can run an UPDATE query that will update closed
Excel workbooks.

I am working on an example file. In the meantime this
file might be a help
for other aspects of SQL.REQUEST:
http://www.bygsoftware.com/examples/...s/UsingSqlRequ
est.zip
It's in the "Excel with Access Databases" section on
page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from
an MS Access table,
or from an open or closed MS Excel workbook using the
workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the
same workbook.

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Al" wrote in
message
...
Thanks for your response. Its is very much
appreciated.
I'm almost there. What I now need to now is how do I
WRITE the resultant recordset to the closed workbook?
Lets call the recordset i Have rs, and lets call
the closed workbook, newbook.



-----Original Message-----
Absolutely, as long as the workbook is not protected.

The approach is virtually the same. You use the MS
OLEDB
provider for
Jet but specify Excel in the extended properties e.g.

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=C:\db.xls;" & _
"ExtendedProperties='Excel 8.0'"

Data must be laid out in database style i.e. rows of
columns. Column
headers make things easier (especially one row one
column
tables). The
Excel equivalent of a table is either a worksheet
(suffix
with $):

SELECT MyCol FROM [Sheet1$]

a defined Name (no $):

SELECT MyKeyCol FROM [MyRange]

or even a range defined at the worksheet level

SELECT MyKeyCol FROM [Sheet1$MyRange]
- or -
SELECT MyDataCol FROM [Sheet2$D4:D99]


The square brackets are required for an
existing 'table'.

Jet proprietary SQL is used but certain functionality
is
not supported
e.g. DELETE FROM, ALTER TABLE. Inserted rows are
appended
to the
bottom of a range. And you can't UPDATE a cell which
contains a
formula.

--

"AL" wrote in
message news:<d0b301c3ef6d$1a951f00
...
I've been retrieving ACCESS data using ADO and I'm
wondering if theres a way to insert that Data into a
closed workbook. Thanks in advance.
.



.