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

Problem with importing excel sheet into access table

P: 40

I have and excel sheet with fields: Name | Surname | Dept. When I execute the following code, it imports 16 empty rows + filled ones
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport = 0, acSpreadsheetTypeExcel9 = 8, _
  2.                               "importowaneDane", sciezkaPliku, True
In the excel sheet the only difference between those 16 empty rows and others is formating (different font and size). Any idea what can be wrong?
I also tried to execute this:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("DELETE FROM importowaneDane " & _
  2.                        "WHERE Dept = ''")
but it does nothing. The 'Null' value also doesn't work
Sep 6 '07 #1
Share this Question
Share on Google+
2 Replies

P: 47
I'm surpised this code worked at all since
acImport is a constant with the value 0 and acSpreadsheetTypeExcel9 is a constant with the value 8 so your code should read

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,  "importowaneDane", sciezkaPliku, True

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet 0, 8, "importowaneDane", sciezkaPliku,True
If you are getting additional fields in your import it is probably because you have additional column headings in your spreadsheet. This command will import every column that has heading in Row 1 even if the rest of the column is empty.
With regard to your second query the correct syntax is

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("DELETE FROM importowaneDane WHERE Dept is Null")
Sep 6 '07 #2

P: 40
Columns are imported properly, the rows are not.
But thanks "is Null" works.
Sep 6 '07 #3

Post your reply

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