Data from Excel to Access (Access97/PeopleSoft)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote