TransferSpreadsheet: why do 'sheets' in the workbook 'misbehave'?

[julietbrown]

I'm experimenting with getting Access to push stuff in and out of Excel using the following code behind two buttons ...

Private Sub ExportContacts_Click()

    'transfer the table CONTACT to the spreadsheet ContactSpSheet

    DoCmd.TransferSpreadsheet acExport, , "CONTACT", _
            "C:\Users\Owner\Documents\ContactSpSheet"

End Sub

Private Sub ImportContacts_Click()

    'transfer the data in spreadsheet ContactSpSheet to the table
    'CONTACT_copy  ... (Don't mess up the 'real' CONTACT table!!)

    DoCmd.TransferSpreadsheet acImport, , "CONTACT_copy", _
                "C:\Users\Owner\Documents\ContactSpSheet", True
 
End Sub

After a lot of messing about with 'Error 2391" (!) this code 'works' in both directions. But, Export absolutely insists on opening a new sheet in the workbook, despite sheet1 being empty, and putting the data in there! As a result, when I click the Import button I get 'Error 2391, Field F1 does not exist in the destination table'. (Of course it doesn't, there isn't an 'F1' name in the first sheet of the workbook, it's in sheet2!)

To get around that error, between the Export and the Import I have to ...
... open the spreadsheet
... delete the first (empty) sheet
... so that, the data exported is one the first sheet of the workbook
... save and close the spreadsheet
THEN the Import works.

This is a bit dotty, isn't it? How can I tell the Export to use 'Sheet 1' of the workbook?

[Delerna]

Why not just link sheet1 into the database as a table?

Now both access and Xcel can add/remove/alter data on sheet1
and be instantly available to both.
I suppose A good name for the linked worksheet would be "Contact_Co py"

What I mean is
Open the database and
change data on the linked sheet either by opening the link like you would open any table or using a query to append/update/delete records.
Close the database and open the spreadsheet and there the changes are
Now change the data in sheet1 using excel
close the spreadsheet open access and open the linked worksheet and there your changes are.

In your scenario I guess

The export button would run 2 queries
1 to delete the current records from the linked worksheet
1 to insert the records from the contact table into the linked worksheet

the import button
Well, there is no longer any need to import as the data is already there

[nico5038]

In the Range parameter you can use the name of the worksheet (and even cells) to direct the data to a specific sheet.

Nic;o)

[julietbrown]

That all sounds really good! I'm a bit pathetic and seem fixated on going on using bits of Access I 'sort of know already' ... need to widen the horizon. I will try out what you suggest tomorrow.

Juliet