I read online that ADO is unable to write to a closed spreadsheet.
I don't know if this is true or not, but if so, I'm considering an
alternative method.

I figure I can use VBA to open, update, and close a spreadsheet,
while turning off screen updating. The update is a quick operation
that only writes a number into one specific cell. This will work okay.

But....suppose the target spreadsheet is open by another user.
Would it be a good idea put my VBA script on a short timer that
constantly checks if the spreadsheet is open by someone else? Then,
once the spreadsheet is closed, my script can do its own update
operation, and then close the spreadsheet for other users.

This is basically a situation where multiple users will be attempting to
write to a single spreadsheet in a first-come, first-served basis. I'd
appreciate any tips on how to make this function smoothly.
Thanx guys & gals!!!

I read online that ADO is unable to write to a closed spreadsheet.
I don't know if this is true or not, but if so, I'm considering an
alternative method.

I'm pretty sure I gave you this link before, but it demos read/write
with closed workbooks!

http://www.appspro.com/conference/Da...rogramming.zip


I figure I can use VBA to open, update, and close a spreadsheet,
while turning off screen updating. The update is a quick operation
that only writes a number into one specific cell. This will work
okay.

But....suppose the target spreadsheet is open by another user.
Would it be a good idea put my VBA script on a short timer that
constantly checks if the spreadsheet is open by someone else? Then,
once the spreadsheet is closed, my script can do its own update
operation, and then close the spreadsheet for other users.

This is basically a situation where multiple users will be attempting
to
write to a single spreadsheet in a first-come, first-served basis.
I'd
appreciate any tips on how to make this function smoothly.
Thanx guys & gals!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

I'm pretty sure I gave you this link before, but it demos read/write with
closed workbooks!


Ah, I forgot about that ZIP file.

I found an example in the "ADO Sample Code.xls" file that shows how
to use the "INSERT INTO" command. This command appends
a new row to the end of a spreadsheet.

Do you know if there is an ADO function that allows me to
modify an arbitrary cell of my own choosing? For example, what if
I want to change the contents of cell A1 using ADO??

"GS" wrote:

I'm pretty sure I gave you this link before, but it demos
read/write with closed workbooks!


Ah, I forgot about that ZIP file.

I found an example in the "ADO Sample Code.xls" file that shows how
to use the "INSERT INTO" command. This command appends
a new row to the end of a spreadsheet.

Do you know if there is an ADO function that allows me to
modify an arbitrary cell of my own choosing? For example, what if
I want to change the contents of cell A1 using ADO??

You treat a worksheet same as a database table. Each row is a record
(starting with row2), each col is a datafield (starting with col1). A1
would be the first fieldname (heading) in your data table (worksheet).
Each record *must have a unique ID* for referencing a specific record.
Thus you would read/write individual fields of a specific record (or
records).

Otherwise, if your worksheet isn't properly contructed as a data table
should be then you have to use range names or addresses to read/write
specific values.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Typo...

Otherwise, if your worksheet isn't properly constructed as a data
table should be then you have to use range names or addresses to
read/write specific values.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

You treat a worksheet same as a database table. Each row is a record
(starting with row2), each col is a datafield (starting with col1). A1
would be the first fieldname (heading) in your data table (worksheet).
Each record *must have a unique ID* for referencing a specific record.
Thus you would read/write individual fields of a specific record (or
records).

Otherwise, if your worksheet isn't properly contructed as a data table
should be then you have to use range names or addresses to read/write
specific values.


The target spreadsheet does look like a table of data, with rows and
columns. How do I assign a unique ID to cell A1 in the first row or
first record? Do you have a small example of ADO code that shows how to
change cell A1 in the first row of a closed spreadsheet?

I have been looking at the example ZIP file that you provided, but so
far I'm only successful at reading from a closed workbook or appending
a new row at the bottom of a closed workbook.

"GS" wrote:

You treat a worksheet same as a database table. Each row is a
record (starting with row2), each col is a datafield (starting with
col1). A1 would be the first fieldname (heading) in your data table
(worksheet). Each record *must have a unique ID* for referencing a
specific record. Thus you would read/write individual fields of a
specific record (or records).

Otherwise, if your worksheet isn't properly contructed as a data
table should be then you have to use range names or addresses to
read/write specific values.


The target spreadsheet does look like a table of data, with rows and
columns. How do I assign a unique ID to cell A1 in the first row or
first record? Do you have a small example of ADO code that shows how
to
change cell A1 in the first row of a closed spreadsheet?

I have been looking at the example ZIP file that you provided, but so
far I'm only successful at reading from a closed workbook or
appending
a new row at the bottom of a closed workbook.

You work with the data same as you would a database table, where the
worksheet IS a database table. Thus the same process for updating the
file after making changes to the data recordset would be the same. I'm
sure the AppsPro sample demonstrates this so I'd have to look through
it. I believe, though, the code syntax examples would be in the
database section since the workbook section only shows how to work with
closed Excel files. Data manipulation is the same as well as updating
the file with changes to the recordset.

In your case you could use the record index to locate a specific record
(row) to edit/modify since the 'unique ID' concept doesn't really serve
much purpose here, but you should specify a fieldname where its *value*
will be unique for the target record being modified. That means A1 is
the 1st field in the recordset, so ref its position in the Fields
collection like this...

rsData.Fields(0).Name = "NewValue"

...perhaps? Note that fields have 'Name's and records have 'value's for
specific fields! Not sure if you can write the Name property and so
perhaps the Value property is what should be used. (My use of ADO is
quite limited and so I rely on the ADO2.6 Programmer's Reference by
David Sussman for help when needed!)


Otherwise, I store data in txt/dat/inf files and used standard VB file
I/O and arrays for processing. This is how I ref individual records in
an array...

A 0-based array contains the headings (fieldnames);

A 1-based array contains records only because fieldnames have their
own array!

The column indexes respectively match in all so finding/accessing
fields isn't a problem.

...and so you should just use the regular ADO syntax to UPDATE which
recordset *values* for the *fields* you want to edit.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion