Page 1 of 4 123 ... LastLast
Results 1 to 15 of 48
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing different excel sheet data into Access (Access 2000)

    Hi All,
    I'm now quite familiar, thanks to Patts great help, with importing a complex excel sheet into a database.
    Now my final challenge has sprung up, what does one do when you have to import several different formats of an excel sheet
    into the same table?. As in, the person who designed the excel sheet, put the data starting in column A and others would start in column B.
    Some even start on column C. Some field names take up an extra cell or two.
    This has thrown me into a logistical nightmare. I thought of repeating the approach i do for one excel sheet, by modifying it and creating a seperate vba code for it, but where does it end?.
    While I cannot expect 100% successful importation, I would love it if I could create a code to identify the excel sheets that are "out of range" and output me
    a list of the failed imported excel sheets. As well as import all differing excel sheets from the same folder into the same table (tblSheets).
    I've placed a zipped up simple access db and 5 different excel samples, sample1.xls is the one I made the code work for, and works fine.[img]/forums/images/smilies/smile.gif[/img]
    To any Access guru out there, your enlightenent would be so much welcomed and appreciated. I know some might find downloading the entire db a putoff,
    it's just my last resort, I wouldn't usually do it otherwise.
    Thankyou very much in advance
    Mark
    ps The database is very easy and simple, the only thing that needs a change is the tblcontrol for the directory path of the excel files. And press the command button on frmImport to import the excel sheets,,in this case, only sample1.xls will work.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    >>Some field names take up an extra cell or two. <<
    What do you mean by this?

    Does all this mean that you have sheets that differ by the fact that col A and col B may be blank, except for the above question?

    All you can do is to offer a range of sheets that may be imported, and force that upon your users. In fact why don't you force your users to the format of the one that works. They can delete columns, can't they?

    I can import the sheet into a table and analyse that table and generate an APPEND query "on the fly" to do what you want, but what happens with those people that do their own thing and the sheet slips through and you update your tblSheets table with the wrong data.

    I would then highly recommend that users use the same format sheet so you don't end up with a nightmare.

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    Hi Patt,
    That problem is being resolved in which the user will stick to one format. However,for the time being, the current pmls will have to be imported.
    What I meant by an extra cell or two, is that if u compare sample 1 and 4. You'll notice for the const Part title,MODULE 18 OVERFLOW DRAIN LOCATION
    the cell goes from C to G. Unlike Sample 1 in which it cell starts at C and ends in column K.
    So I guess its best if I use these 5 samples as the criterieas..
    >but what happens with those people that do their own thing and the sheet slips through and you update your tblSheets table with the wrong data.
    Thats fine, as long as the file names of unsuccessful imported ones (as in the pmls it would have skipped because it did not meet the criteria/range) is appended to some sort of list. So the user can go off and manually import them.

  4. #4
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    >Does all this mean that you have sheets that differ by the fact that col A and col B may be blank, except for the above question?
    Yep, thats right. But even if the above cannot be resolved, the issue of col A and col B is critical and needs to be imported. As long as the filename for the above can be appended to a list of unsuccessful imports, then it would help the user to import them manually.

    >In fact why don't you force your users to the format of the one that works. They can delete columns, can't they?
    No they can't, due to the fact the pmls will number in the hundreds to thousdands.

    This is a temp solution until they stick to one format. Which I'm seeking sample 1 to be the standard.
    If I can get all this to work by importing different pmls from the same folder if possible, then a headache would have been defused.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    >>You'll notice for the const Part title,MODULE 18 OVERFLOW DRAIN LOCATION
    the cell goes from C to G. Unlike Sample 1 in which it cell starts at C and ends in column K. <<

    This still only occupies one cell, one is C4 the other is C6. I don't see what the problem is here?

    From what I saw, there is only 3 different formats in the samples you have shown:
    1. Col A may be blank
    2. Cols A & B may be blank
    3. The description can be entered as 1 or 2 columns

    Are these the only differences in all your sheets, not just the 5 samples?

    >Thats fine, as long as the file names of unsuccessful imported ones (as in the pmls it would have skipped because it did not meet the criteria/range) is appended to some sort of list. So the user can go off and manually import them. <<

    I have presumed that there is no error in the handling of these sheets, but in fact it gets the wrong columns into the table tblSheets. In other words there is no error condition.

    BTW, what is a pml?

  6. #6
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    Hi Pat,
    >the cell goes from C to G. Unlike Sample 1 in which it cell starts at C and ends in column K. <<
    >This still only occupies one cell, one is C4 the other is C6. I don't see what the problem is here?
    Ok, you might have a point, didnt look at it this way.

    >Are these the only differences in all your sheets, not just the 5 samples?
    The thing is there could be other differences, but I think these ones would suffice as as its the most common. The rest of the pmls that have errors should be skipped in the importation process, but should be recorded in a list to indicate the unsuccessful ones.

    >I have presumed that there is no error in the handling of these sheets, but in fact it gets the wrong columns into the table tblSheets. In other words there is no error >condition.
    Thats right, no error handling/condition at all. My achiles heel.

    >BTW, what is a pml?
    Parts and Materials List

  7. #7
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    With generating an append query, does this mean it would be best to append to a temp table, before actually moving it
    to the tblsheets?. I'm assuming this is the most viable option. Nevertheless, if I can get the below three to be resolved, then that would
    be a milestone. Or even just the first 2.
    1. Col A may be blank
    2. Cols A & B may be blank
    3. The description can be entered as 1 or 2 columns

    This is quite a predicament, as in I do know what I need to do, but approaching it is the most challenging part. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    You could do this by testing all F1 to be Null and if so add 1 to each Fn you have in your code. Also testing F2 to null and again add 1 to each Fn in the code.
    The description is quite straight forward, it's just a matter of appending the two columns together like:
    Description = (F3 + " ") & F4, keeping in mind the point I made above.

    This is a big job that would take hours to do properly (maybe even a couple of days).
    What you would do is to make a pre-pass through the imported sheet and determine what columns everything was in (eg. strRev, strConstUnit, etc). You would also determine where each of the fields were that you use in your Append query and build this query "on the fly".

    This is not a small task by any means.

  9. #9
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    Thanks Pat, quite understood.
    I'm looking for a shortcut approach, what I'm thinking of doing is to have sample1 as the standard (which is already the case), any other excel sheet that does not match this standard is logged into a list, so it can be viewed and manually imported by data entry. How does one go about the logging part?

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    I went mad and did the whole thing. I was way off in my estimation.

    Have a good look at the Import form and see what I have done, you will notice that I have done error checking as well.
    You had better check it that it outputs the correct data, I checked to a certain extent.

    I have included your amended database.

    Let me know if you don't understand anything.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    Pat, I'm lost for words, this is truly remarkable. And my apologies if this drove u mad, looking at the code, I dont blame you one bit.
    Currently, I tested it on 28 excel sheets, and only 25 were successfully imported. However in the errors table, the unsuccessful ones were not mentioned.
    Other times when testing different pmls, it actually did display the error messages.
    Thus I might do a function that will display the strmatch filenames and an ole object to show all the excel sheets in the folder. Hence the user can match which ones are missing in the table.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    Can you email me one of the error sheets so I can test it on mine and populate the errors table?

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    What I meant was to post the zipped sheet here. Currently I cannot email as my ISP is having problems.

  14. #14
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    Ok, I've zipped up 5, one of them wont work. Sorry i took a while, i had to narrow it down from 28, to see which ones are not working.
    Attached Files Attached Files

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing different excel sheet data into Access (Access 2000)

    All of them work on mine.
    Do you want to check again and send me some more?

Page 1 of 4 123 ... LastLast

Posting Permissions

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