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

Docmd.Transferspreadsheet

P: 23
I am trying to impot a excel file and replace with existing table in database.
Every time it import a extra automated field(F1),so it gives the error message:
"Field'F1' doesnot exost in destination table'table name'"
How can i impot my excel file into existing table,whose field names are same?And without autonum field?
The code i craft is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdImport_click()
  2. Dim msg As String
  3. Dim button As Variant
  4. Dim title As String
  5. Dim response As Variant
  6.  
  7. msg = "Is the updated file Task Import placed in File path with 'file name.xls'?"
  8. button = vbYesNo + vbDefaultButton2
  9. title = "File Location Checkpoint"
  10.  
  11. response = MsgBox(msg, button, title)
  12. If response = vbYes Then
  13. 'Delete old records from tbltask
  14.    DoCmd.SetWarnings False
  15.    DoCmd.RunSQL "Delete [dbaseTable name].* from [dbaseTable name]"
  16.    DoCmd.SetWarnings True
  17. 'Import new records from Excel file into tblTask
  18.    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "dbaseTable name", "File path with 'file name.xls'"
  19.    DoCmd.OpenForm "Form1"
  20. End If
  21. End sub
  22.  
Thankful for helping always!
Mar 29 '12 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,308
If the data in the spreadsheet doesn't match the layout of the table, then it's not going to work. Probably obvious, but you seemm to need to be told that so I have done so.

Either make sure the data layout is the same, or else import the data into a separate table (that does match the data obviously) and follow that with an Append query to move the matching part of the data across to your originally-intended table.
Mar 29 '12 #2

P: 23
yes the layout of both excel file and access table are same.
But when it impoting ,it automatically create a autonumber field,which is not matched.
So should i create a table in database with F1 field?
It again shows the error msg" F2...in destinationn table"
or any way is there to replace a existing file with imported file.
Mar 29 '12 #3

NeoPa
Expert Mod 15k+
P: 31,308
You say they're the same, yet Access says they're different. Whom do I believe?

Why not explain the exact layout for each and we'll see which of you is correct, and how to move on from there.
Mar 29 '12 #4

P: 23
The existing table in data base has 4 field:
1A
2B
3C
4D
(This is the impoted excel file by using File->Import)
In this file i want to replace all my imported data of new excel file.
The new excel file has also same field name,same sequence,same data type.
only the thing is that,while it importing the new excel file,it join a autonumber field,with default naming"F1".
Which means now the layout of new excel file is different.
how can i got the solution of this autonumber field?
Mar 29 '12 #5

NeoPa
Expert Mod 15k+
P: 31,308
That doesn't make sense. How can there be a new field in those circumstances? This is still a contradiction. Please explain the situation clearly and illustrate the data (as your explanation is so unreliable I need something to give me a clue as to what you really mean). I can't help with this as it stands.
Mar 29 '12 #6

P: 23
The existed table has 4 fiels:
Name(text)'Two space before this string.
Fname(text)
Lname(text)
Email(text)
Excel file try to import has 4 column;(All in Sheet1)
Name(text)'In A1 ,Two space before this string.
Fname(text)'In B1
Lname(text)'In C1
Email(text)'In D1
executing my 1st posted code,the error msg comes"F1.."
Next i tried by changing in my code:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8 "dbaseTable name", "File path with 'file name.xls',"A1:D1000""
now error msg is:
"Field' Name' doesnot exist..."
Mar 29 '12 #7

NeoPa
Expert Mod 15k+
P: 31,308
Still no example data, and I have no idea what you mean when you say :
"Name(text)'Two space before this string."

The explanation is more detailed certainly, but I see nothing here to explain why that behaviour might occur. ... which leads us neatly back to the missing example data which was requested, but still not included in your post.
Mar 30 '12 #8

Post your reply

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