Access to scan daily folder/compile into report

[ccoberly]

Hello,

I am relatively new the Access world with little to no experience and I’m not even sure if Access would be best suited for what I need.

I have been in charge of governance at my work for the past 5 months grading each individual’s incident ticket making sure all the times have been followed. We use BMC Remedy as the incident tracking software and each day I am required to look at each ticket and grade it following an Operating Level Agreement.

Currently I use a scorecard developed using Excel that will automatically calculate the score for each employee on the ticket. This might seem like an easy option except at the end of the month all the data needs to be compiled into a report for the directors and managers.

My problem is that I can have anywhere from 2,000 – 3,000 individual Excel scorecards that need to be opened, scores recorded onto a single spreadsheet and averages calculated for 42 teams and 270+ employees usually with only a week’s time to complete.

I’m trying to keep this simple and anything to make my job easier would help. One thing I’ve thought of would be an Access DB that would scan the months folder with scorecards, pick up the employees name, group, score from each scorecard and at least output it to a table.

Does this sound possible or reasonable and if not, what would be a good alternative?

[June7]

Yes, expect it could be programmed. Depends on the filetype data is in. You want to extract from Excel spreadsheet? Is the data always in the same cell location?

The VBA code could be in Excel and push data to Access or in Access and pull data from Excel.

Review this article for starters http://www.ehow.com/how_8156781_use-...el-access.html

[rpeare]

June's given you a good start, I'm curious though, if you're going to go so far as to pull information from spreadsheets why not just create the full application in access to start with? If you've got 270 people filling out a single spreadsheet for each pay period (for example) you will be overwhelmed with excel files in very short order. If, however you build the data entry into access and have a single person doing data entry you might economize your time a bit. Even if you have one person per 'team' doing the data entry Access could likely still handle it.

[ccoberly]

Yes, expect it could be programmed. Depends on the filetype data is in. You want to extract from Excel spreadsheet? Is the data always in the same cell location?

The VBA code could be in Excel and push data to Access or in Access and pull data from Excel.

Review this article for starters http://www.ehow.com/how_8156781_use-...el-access.html

Thanks for the link, I tried it and it seems to be something that would help.

Correct, I'd like to be able to extract a range of cells "A25 - E29" from 1000's of Excel spreadsheets and output them to a table for sorting. The Excel files are in different folders for each workday of the month "10/03/2011, 10/04/2011, 10/05/2011", each containing 100's of Excel spreadsheets. Each Excel spreadsheet's name is unique to the corrisponding incident number "INC000000227707, INC000000227706, INC000000227698" but the data always remains in the same "A25 - E29" cell location.

One thing I'm not sure about is when an Agents name, Department is selected at the top of the scorecard, it is then replicated below. Also there are drop downs for "TIA, TIR, TSU, TCN" with YES/NO options and depending on different YES/NO options selected, it will calculate a score from a formula on a different sheet in the same spreadsheet. I'm just not sure if Access would be able to pull the score, Agent name and Department since it is pulled from a differnt location.

[ccoberly]

June's given you a good start, I'm curious though, if you're going to go so far as to pull information from spreadsheets why not just create the full application in access to start with? If you've got 270 people filling out a single spreadsheet for each pay period (for example) you will be overwhelmed with excel files in very short order. If, however you build the data entry into access and have a single person doing data entry you might economize your time a bit. Even if you have one person per 'team' doing the data entry Access could likely still handle it.

The link June provided me was the best information I've seen about what I'm trying to create and I've been playing with the code here and there.

When you say to create a full application in Access do you mean use Access as a single application rather than a single spreadsheet for each incident? I'm the only one that works on goverance and everything is left up to me so anything that is created, I'll be the only person using it.

I know the manager of the Web & DB department was working on a fully automated Access program that was connected to the BMC Remedy DB. It would connect to the DB and pull the all the information needed to score incidents/create reports. Unfortunately he doesn't have a lot of time to spend fixing the program and when I tried it, it didn't work too well and wasn't designed very well.

The problem with an application like that is it would need to pull 10's of time/date stamps and 10's of work log entries "including attachments" for each incident and if it's not displayed where you could go back and forth easily and quickly it would end up slowing me down.

[rpeare]

Do you know what the back end database for your 'BMC Remedy' software is? if it's any sort of ODBC compliant database you can link it into access and do whatever you want with it. If it's a proprietary back end you still may be able to connect to them but you'd likely need some help from the software vendor to do so.

The data is in your BMC software, it's being exported to files, then you're trying to parse the files, which is extremely inefficient (if I understand your process correctly) I think if you manage to link to the database files directly you'll have a lot more luck depending on the complexity of the source tables but the software should have come with a data dictionary of some sort so it shouldn't be that bad.

[ccoberly]

Do you know what the back end database for your 'BMC Remedy' software is? if it's any sort of ODBC compliant database you can link it into access and do whatever you want with it. If it's a proprietary back end you still may be able to connect to them but you'd likely need some help from the software vendor to do so.

The data is in your BMC software, it's being exported to files, then you're trying to parse the files, which is extremely inefficient (if I understand your process correctly) I think if you manage to link to the database files directly you'll have a lot more luck depending on the complexity of the source tables but the software should have come with a data dictionary of some sort so it shouldn't be that bad.

I believe the back end of BMC Remedy uses SQL 2000, yes I know it’s older than dirt but anything newer wouldn’t be compatible with the version of Remedy the company uses.

I have messed around with the Remedy DB and connecting it to Access or even Excel isn’t that hard. I know how I’m currently grading scorecards isn’t the best but anything to help speed up the process would cut a few days off my time of creating a month end reports.

The link that June provided seems like it would be my best option “For Right Now” until something better can be developed. In a simple way, the code from the link does what I need but I could the code be modified to do the following?

1. Scan a multiple folders within a folder *If not, I can always move all the excel files into one folder*
2. When scanning for files use a wildcard like INC000000******* so that I wouldn’t have to specify an exact file name.
3. When scanning a single Excel file skip cells that do not contain text or number and return nothing.

I really do appreciate all the help

[rpeare]

The first two items can be handled with filesystemobject commands:

http://www.4guysfromrolla.com/webtech/090599-1.shtml

Here's a simple starting point.

The last part may be tricky depending on how access interprets blank fields in excel. In excel I think there are no truly null fields, they are stored as empty strings ("") so you'd likely have to test for a zero length string and/or a null value (len(string)).

[June7]

Check out the IsEmpty function. Also http://ewbi.blogs.com/develops/2006/...ine_if_a_.html

[ccoberly]

I think I found something that might actually work. It's using Excel but I didn't care what app I use and just modifying the code to change the directory and which cells to pick it up it was able to scan a folder and output all the information I needed.

After I run it I'd need to clean up the output a little but it's the best I've seen so far.

Merging a Range from All Workbooks in a Folder
http://msdn.microsoft.com/en-us/libr...omAllWorkbooks