Update Closed Workbook w/ ADO
 

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.

Update Closed Workbook w/ ADO
 

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

Update Closed Workbook w/ ADO
 

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

Update Closed Workbook w/ ADO
 

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

Update Closed Workbook w/ ADO
 

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



.