Editing a Linked Spreadsheet

[Guest]

I'm trying to add a column and update values in that column in a linked spreadsheet from another table already in Access
I want to read through each using a key and update a new value where the keys match
I'm here at the planning stage - can I even update a linked spreadsheet with a new column - or do I need to bring it in a
a table and append and then output back out as excel

Or maybe I should write the code in VBA behind the spreadsheet and update from another spreadhseet? Hmmmm
Help

Thanks.

 

[Marshall Barton]

I'm trying to add a column and update values in that column in a linked spreadsheet from another table already in Access.
I want to read through each using a key and update a new value where the keys match.
I'm here at the planning stage - can I even update a linked spreadsheet with a new column - or do I need to bring it in as
a table and append and then output back out as excel?

Or maybe I should write the code in VBA behind the spreadsheet and update from another spreadhseet? Hmmmm.
Help.

You can use OpenDatabase on a workbook file.

Set dbXcl = OpenDatabase("path\file.xls", _
dbDriverNoPrompt, False, "Excel 8.0")


Once you've established the database object, then you can
use DAO on any sheet (add a $ to the sheet name) or named
range as if it were a tabledef in an Access mdb file. I
haven't done this in a long time, but I believe you can use
the CreateField method or execute an Alter Table query to
add a column. You can then use an Update query to set the
values.or, if you prefer, open a recordset and do whatever
to the rows in the range.