By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,571 Members | 3,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,571 IT Pros & Developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub ExportContacts_Click()
  2.  
  3.     'transfer the table CONTACT to the spreadsheet ContactSpSheet
  4.  
  5.     DoCmd.TransferSpreadsheet acExport, , "CONTACT", _
  6.             "C:\Users\Owner\Documents\ContactSpSheet"
  7.  
  8. End Sub
  9.  
  10. Private Sub ImportContacts_Click()
  11.  
  12.     'transfer the data in spreadsheet ContactSpSheet to the table
  13.     'CONTACT_copy  ... (Don't mess up the 'real' CONTACT table!!)
  14.  
  15.     DoCmd.TransferSpreadsheet acImport, , "CONTACT_copy", _
  16.                 "C:\Users\Owner\Documents\ContactSpSheet", True
  17.  
  18. End Sub
  19.  
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?
Jan 20 '10 #1

✓ answered by 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_Copy"

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

Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
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_Copy"

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
Jan 21 '10 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 22 '10 #3

P: 99
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
Jan 24 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.