Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing from xls (97)

    This is just a general question...

    For our firms compliance monitoring, we will be generating lots of Excel files. somewhere in the region of 40-50 per day. These xls files have to be analysed and counted.
    Originally i was going to do this in Excel. But am wondering if Access would be better for this.

    As i understand excel, each xls file has to be open, if you are to extract data from it.. is this the same with Access..

    So generally, whats access like for grabbing lots of snippets of data and counting/analysing them.. ?


    Steve

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    I can interpret your question in two ways, are you asking if you can do your firm's compliance monitoring with 40 or 50 XL files and have these files linked to Access, or are you asking if all this can be done in Access?

    It is possible to link XL files to Access, but really this is not desirable. Your best bet would be to do the whole project in Access.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing from xls (97)

    If you really mean the tag in your signature, why are you even asking about Access?

    Are you proficient in VBA in Access or Excel? You would need to program Access to open the Excel worksheet and extract data from the appropriate range, either importing it or linking to it. DId you name the ranges or can you reference them with column and row coordinates? You can't just wander through the sheets with Access, you have to tell it specifically what to extract or link.

    Since it's impossible to know what you mean by "snippets" of data or what kind of analysis you need to do on 40-50 sheets a day, there is really no answer to your question.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    Unfortunately the files can only be generated in xls. (they come from a bespoke piece of software, that cannot be changed at this time.)

    I can do the project in either application. Access or Excel. I'm slightly more proficient in Excel than i am in Access. (heh, average at both!)
    but from working on a couple of projects in access, i realised that access was more difficult to set up, but allows much better analysis of the data in the long term.

    It will roughly work like this..

    Each day, around 32-50 xls files will be analysed, This will then generate a daily report (that will need to be saved), then, at the end of the week, those 7 daily files will need to examined to provide a weekly list of compliance percentages....

    What i'm trying to gain, is perhaps an insight into how easy/difficult it is to use access to import excel files. for instance...

    Do the excel files have to be 'open' before Access can examine the information ?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    On second thoughts, looking at your Bio <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I strongly recommend you do it in Excel!!!!! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    Are the excel files single pages, with database type columns and rows, or are they complicated.

    If they are complicated, could they be simplified?

    Could you show a sample?

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    Yes Rupert, they are simple columns and rows. And they will contain a number or a letter or series of letters. Actual content of the boxes does not strictly matter.

    ie: Y Y Y 0.56
    N N N 0.34
    Y Y
    N Y Y 4.56

    In this case all i'm actually going to do, is count the number of entries in each column. ie: 4,3,4,3

    Then, compare these numbers with a table containing the amount of checks that should have been done, and then generating a percentage from this.

    4-5 of these counts amassed together will equal one days percentage figure.
    This percentage figure will be saved for that day
    At the end of the week, a weekly report from this data is generated.

    One way or the other, i'm going to attempt this with access. It has a superiority when it comes to reports etc. <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    But 'how' i attempt it... heh.. thats the cruncher.


    Thanks for the reply btw <img src=/S/bow.gif border=0 alt=bow width=15 height=15>


    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    I don't know if this will be much use to you as I see you're using Access 97 but I have a book titled programming Microsoft Access 2000 by Rick Dobson.

    Reading from the index under Excel>>>>>
    Accessing data in Excel
    Integrating Access 2000 with Excel
    Dynamically creating Access tables based on Excel worksheets
    Running Excel procedures from an Access procedure
    Values from Excel worksheets

    There is also a complete copy of the book on an accompanying CD, which you can cut and paste code from.

    I just noticed the code samples shown are for Excel 2000 as well as Access 2000 although I think you can link to Excel 97 just as easily as you can Excel 2000.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    >>>>>>count the number of entries in each column. ie: 4,3,4,3<<<<<<<<
    I can

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    Will all the spreadsheets be the same layout, exactly the same number of columns? I presume the number of rows can vary? Is the check table you mentioned static, or is one produced every day? Does the bespoke software produce an Excel spreadsheet with a name in the format of the date -- 01, date -- 02 etc. What I'm getting at is could you anticipate and reproduce the spreadsheets name in VBA?

    What is the minimum and maximum number of rows you can expect in spreadsheet?

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    The number of rows and columns will vary...

    The name of the saved xls file will be like whatever is needed, in that the saved xls file is saved from the bespoke software (but manually)

    So for day 1 (out of 365 days in the year), and for area 1 the saved format of one of the xls files will be :-

    Data300-01-05

    being day300 area 1 - part 5

    each day is going to be split into about 10 areas.. and each area will comprise ~around 4-5 xls files. (but could be as low as 2-3)

    maximum number of rows and columns per excel file, should be only around 3-4 columns, but as many as 48 rows..

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    oh, the number of parts, and the number of columns if fixed for each area.

    so if i had a two columns that read like this:-
    Day 300-Area-1-Excel file 1

    YN
    YY
    <blank>Y
    YN
    <blank><blank>
    YY

    Then the two numbers are 4 (column 1) and 5 (column 2)

    Then i compare this with the required amount needed in the column (which is generated from user input, based on time, and a unique amount of checks required for THAT particular column.. (this will be held in a sepaate table) ie: column one here, has 4 entries. The user enters an amount of time as 6 hours. The amount of required checks is say 2 per hour.(as designated by the separate table) therefore the required number of checks for this column is 12
    but only 4 were entered (blanks do not count)

    therefore compliance = 4/12 = 33%



    Steve

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    The first thing that is bothering me is that you appear to have a variable number of columns. If the maximum number of columns is four and you identify the columns as column one, column two, column three and column four, if two of the columns were missing in a spreadsheet say column one and column two, would column three and column four be in the same place? (Would they occupy the spreadsheet columns C and D or would they be moved over an occupy columns A and [img]/forums/images/smilies/cool.gif[/img]?

    You also mentioned this:

    >>>>>The user enters an amount of time as 6 hours.<<<<<

    Does this mean that the "hours" can vary for each spreadsheet? If it does is there any other info that varys per spreadsheet?

  14. #14
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from xls (97)

    Yes , the number of colums in a set varies , and the number of hours is variable.. heh

    Maybe Excel would be better <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    I will attempt it in access tho, *just* to see if i can get somewhere. Access would be far better for analysing the data i'm sure.

    Steve

Posting Permissions

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