Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with importing Excel file into Access (2000 (9.0.6926 SP-3))

    Hi there,

    I am having problems importing the attached directly into Access. Each time I try I get the message "The wizard is unable to access information in the file 'I:etc'. Please check that the file exists and is in the correct format." You then click OK to continue.

    I have had problems in the past with Excel files where the version of the file given to me has been 2.1, a version which Access does not support. It's not clear which version this file is, but opening it, saving it as 2000 and re-importing gives the same message. If I copy the data and paste it into a new workbook, there is no problem.

    The attached is an extract from a far larger file I receive from an external source and am not sure how it gets to the Excel stage, i.e. it could be a direct output from an AS400 or RS600 application.

    Has anyone got any ideas as to the format of this file and any fixes aside from the copying and pasting routines I go through now?

    Cheers,

    Niven

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem with importing Excel file into Access (2000 (9.0.6926 SP-3))

    The appears to be in Excel 97-2000 format. Apparently it contains some kind of corruption that bothers Access even if Excel itself doesn't complain about it. I haven't been able to determine what is wrong - I tried several modifications (changing the sheet name, field names, styles, formatting) but that didn't help.
    If you save the Excel file as a .htm file or as a .txt file, Access can import that without problems.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with importing Excel file into Access (2000 (9.0.6926 SP-3))

    Many thanks your reply.

    I'm trying to automate the process of importing the data into Access, but unfortunately as the workbook comes in with multiple tabs, so the text option of opening and saving will be very cumbersome and no better than the copying and pasting method I use now. The HTM file means importing several files and as I'm not sure as to what order the file will turn up in, will be uncertain as to which tab I'm importing. Several of the tabs are in a different format which doesn't help either.

    I think my best option is to go back to the client and ask them if they can supply the data in text or csv format (it's taken them long enough to get them to supply the file in a regular format! Incidentally, last years file has the same import problem, so I believe it must be something to do with how they output the data from their system.). That way I can write some import routines according to those files and avoid the messing about I have to go though now.

    Cheers, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Niven

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem with importing Excel file into Access (2000 (9.0.6926 SP-3))

    > ask them if they can supply the data in text or csv format.

    That sounds like a good idea; there is much less chance that you'll have problems with those formats.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •