Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data from Excel to Access (Access97/PeopleSoft)

    Hello and good week everyone!

    Here is my "other" headache.

    I am consulting for a client and building Reporting System for them.
    Client using People Soft query downloaded to Excel file. Every time whoever runs those queries saves them on Share Drive with whatever names(Dates) in whatever format they wish.

    My Reports are suppose to be run using those Excel files. i am Importing them into Access97 database and creating Reports.

    For now i am using latest files they have but in future when I am gone - they will have to be able to run those Reports themselves. When I got here I had no idea that data for my Reports going to be this type so I am totally unprepared what to do.

    Please, advice

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

    Re: Data from Excel to Access (Access97/PeopleSoft)

    Do your reports still work if you link to the Excel files instead of importing them? If so, you could instruct the users to store the Excel files with a fixed name in a fixed location. The reports in Access would always use the current version.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from Excel to Access (Access97/PeopleSoft)

    I never linked Reports to Excel, I would rather do it from Access as I need to write Queries and add calculation and stuff...
    I can however instruct them to use set location but sometimes those Reports are Summary for few month and file is only weekly, by-weekly etc. So I have to combine them.

    I am trying to think of which way is best, I am open for suggestions.
    Thanks HansV

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

    Re: Data from Excel to Access (Access97/PeopleSoft)

    You don't link a report to an Excel worksheet directly.
    You link an Excel worksheet into Access as a linked table. This behaves just like an imported (or native) table, except that you cannot change the design of a linked table. You can create queries based on a linked table (or on several linked tables, or on a combination of linked and local tables), add calculated fields, etc. Use such a query as record source for a report.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from Excel to Access (Access97/PeopleSoft)

    Imagine there are Excel files saved in directory as
    abc-12012004.xls
    abc_1/12/05.xls
    abc122320004 etc.
    And every pay/period it adds up.

    So I can ask Client to use naming convention, but how am I suppose to control it when I am gone?
    Plus people will come and go and here is no IT people to check on it or fix it if broken.
    Then how am I suppose to find new file if I don't know (it is not set) what date does it have?

    I need to come up with whole System Idea and I can't because all this is a nightmare (datawise...lol)

    Let say I allocated file on Share Drive and told to save Excel files into it. How will I be able to find the newest one and extraction is going to have to be done using Wizard, right? hmmmmmmmmmmmmmmm

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

    Re: Data from Excel to Access (Access97/PeopleSoft)

    That sounds like a nightmare indeed.

    You can use DoCmd.TransferSpreadsheet to import or link Excel worksheets in code, and you could use Scripting.FileSystemObject to find the last modified date of files. To use FileSystemObject, set a reference to Microsoft Scripting Runtime in Tools | References... in the Visual Basic Editor.

    See FileSystemObject Object for more info, and search for FileSystemObject in this forum for examples of its use.

Posting Permissions

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