Hi, Ben.
Can someone please explain how I can
know when the data in the spreadsheets is going to be partially or completely
overwritten, and when it's not?
Sometimes it's best to just run some experiments and keep notes on the
actions and results so they can be referenced later. Here's my experiment
that may help you:
I created a macro that uses the transfer spreadsheet action to create two
worksheets in a brand new Excel file. Then I ran the macro and opened the
Excel file to verify that both worksheets were in there with the records from
the tables in Access.
Then I modified the macro and replaced the names of both of the tables with
two other table names, and then ran the macro again. I opened the Excel file
and verified that I now had four worksheets: the two original and now the
two new worksheets from the two new tables. Then I modified some of the
records in both of the new worksheets and added more records in one of the
new worksheets and then saved the file.
Then I ran the macro again, opened the Excel file and verified that the
worksheets with the modified records had been replaced with the original
values from the tables in Access. I also noticed that the worksheet that I
had manually added records to only replaced my earlier modified records, but
left my added records alone. The two original worksheets were left alone
again, as well.
I didn't specify any range for the transfer spreadsheet actions when I
created the macro, so all of the records in the tables overwrote what was in
each of the worksheets, except for my additional records I mentioned above.
On later tests, I noticed that Access uses different rules for this action
regarding what gets overwritten if I either specify a range to be exported,
or add records to the table to be exported -- which might overwrite my
manually added records in the Excel file -- so this is also something to be
aware of.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.
