Update Closed Workbook w/ ADO

[AL]

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.

 

[onedaywhen]

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$D499]


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]

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$D499]


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" <[email protected]> wrote in

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.

.

 

[Andy Wiggins]

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/zipfiles/UsingSqlRequest.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"

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$D499]


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" <[email protected]> wrote in

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.

.

 

[AL]

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/zipfiles/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"

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$D499]


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.

message news:<d0b301c3ef6d$1a951f00 [email protected]>...

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

.