473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with importing excel sheet into access table

40
Hello,

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
2 1645
barry07
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
OR

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
saddist
40
Columns are imported properly, the rows are not.
But thanks "is Null" works.
Sep 6 '07 #3

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

Similar topics

1
by: G | last post by:
I have an excel document that has a field called Account Number. I have an equivalent field in an Access database. When i import the field info from the excel sheet to access, only some of the...
9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
2
by: Mr. Bungle | last post by:
When importing excel from access I am fully aware that one can import directly into a table. Can you get as specific via code or something to import data from an excel sheet to a FORM (Not Table)...
7
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the...
5
by: Gary Cobden | last post by:
I have a problem with the following code, which leaves an instance of Excel visible in Task Manager. By a process of elimination I have got it down to the fact that something in the...
9
by: jillandgordon | last post by:
I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I get to the lst step and am told that it was not imported due to an error. ...
3
by: Conrad F | last post by:
Hello All, I know how to import a specific named excel sheet into a datagrid using ADO.NET by setting up a JET connection and then SELECTing data from the sheet. However, for a real world...
4
by: Harshe | last post by:
hello all, I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with. In a workbook at the start of the year (january) i will have 4...
2
by: lrheeza | last post by:
Hello everyone, I am a newbie at MS Access and I need help!!! I am importing an excel file using Import functionality in MS Access, all the fields are required but there are instances in the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.