Jeff_Fuzzy
New Member
- Joined
- Jan 9, 2023
- Messages
- 2
- Office Version
- 2013
- Platform
- Windows
Hello and good evening team! I came to you because I need assistance. I've read a few posts on this forum and I will be as detailed as possible with my ask for help.
I know very basic VBA, however when it comes to creating a script that will scan through each file in a folder, identify a line in which specific words are located, then pull that line inside of an excel file, finish scanning that file for any other words, then close that file and open the next, it was over my head! and that is basically what I am looking to create. I did see a few posts from Comfy in which a user was looking to do something similar but it was WAY advanced. I love to learn so if you put in notations on what some things do I would love it because I will be responsible for up keeping this report.
1. I work for a facility that has a call light system - our clients ring a call light, it get answered, then we close out the call light once we answer the call. This facility was never able to create a report before, but just this last month was able to pull some strings and create a report. However it is in a text file, and it records LITERALLY EVERYTHING. (for example, 11/1/2022 there was something like 2000 lines in the file, but only something like 150 calls in total.) These files are placed into a folder - by floor, and named - by their date. I will provide screenshots, with some colorful hand written notes. the VBA would need to scan through these files and look for specific lines that will tell excel "this was the start of a call, this is when we answered it, this is when we closed it".
2. The data in these files is quite horrible to read, there is no delimiter except " " on 1-2 items, and a couple of :'s mixed in. I am still going through an manually trying to figure out the lines of code myself, but I have it widdled down to 3 common sightings. When the client initially calls it says "New Call: ROUTINE for RM #" on this same line there is a timestamp to the far left. When we answer it says "Answer Call: ROUTINE for RM #" and when we cancel the call/answer it "Cancel Call: ROUTINE for RM #"
3. I would need to filter through this data and categorize it such as ROOM 801: Initially Called at 12:01AM | Answered at 12:02AM | Cancelled at 12:02 AM. Now realistically I am not sure how possible this is because all of this data is stored on a different lines. So what would likely be better would be to Save JUST the lines that have the Strings:
This file has 1900 lines, and I have 60 days of these files, for 2 floors, totaling 120 files, and I know there has got to be a way to have excel scan each of these files and store the information into a single excel sheet, and perhaps separate the entries by the days they were taken (the file's name is the date)
4. What the end game will be is to generate how long it takes our team to answer the calls, and then cancel the calls. I can't even wrap my head around the first 3 steps, let alone this one. So I am hoping any of you would be able to guide me through some of these initial stages to get me started then I will, hopefully, be able to come up with something here.
Please let me know what information you need from me and I will do my best to assist, I will email you a couple of these files so you can see what I'm dealing with. I have MS Excel 2013 at my work computer, and I believe windows 10.
I know very basic VBA, however when it comes to creating a script that will scan through each file in a folder, identify a line in which specific words are located, then pull that line inside of an excel file, finish scanning that file for any other words, then close that file and open the next, it was over my head! and that is basically what I am looking to create. I did see a few posts from Comfy in which a user was looking to do something similar but it was WAY advanced. I love to learn so if you put in notations on what some things do I would love it because I will be responsible for up keeping this report.
1. I work for a facility that has a call light system - our clients ring a call light, it get answered, then we close out the call light once we answer the call. This facility was never able to create a report before, but just this last month was able to pull some strings and create a report. However it is in a text file, and it records LITERALLY EVERYTHING. (for example, 11/1/2022 there was something like 2000 lines in the file, but only something like 150 calls in total.) These files are placed into a folder - by floor, and named - by their date. I will provide screenshots, with some colorful hand written notes. the VBA would need to scan through these files and look for specific lines that will tell excel "this was the start of a call, this is when we answered it, this is when we closed it".
2. The data in these files is quite horrible to read, there is no delimiter except " " on 1-2 items, and a couple of :'s mixed in. I am still going through an manually trying to figure out the lines of code myself, but I have it widdled down to 3 common sightings. When the client initially calls it says "New Call: ROUTINE for RM #" on this same line there is a timestamp to the far left. When we answer it says "Answer Call: ROUTINE for RM #" and when we cancel the call/answer it "Cancel Call: ROUTINE for RM #"
3. I would need to filter through this data and categorize it such as ROOM 801: Initially Called at 12:01AM | Answered at 12:02AM | Cancelled at 12:02 AM. Now realistically I am not sure how possible this is because all of this data is stored on a different lines. So what would likely be better would be to Save JUST the lines that have the Strings:
- "New Call: ROUTINE for RM 801" into Initial CALL at 12:01AM ROOM 801
- "Answer Call: ROUTINE for RM 801" into Answered CALL at 12:02AM ROOM 801
- "Cancel Call: ROUTINE for RM 801" into Cancelled CALL at 12:02AM ROOM 801
This file has 1900 lines, and I have 60 days of these files, for 2 floors, totaling 120 files, and I know there has got to be a way to have excel scan each of these files and store the information into a single excel sheet, and perhaps separate the entries by the days they were taken (the file's name is the date)
4. What the end game will be is to generate how long it takes our team to answer the calls, and then cancel the calls. I can't even wrap my head around the first 3 steps, let alone this one. So I am hoping any of you would be able to guide me through some of these initial stages to get me started then I will, hopefully, be able to come up with something here.
Please let me know what information you need from me and I will do my best to assist, I will email you a couple of these files so you can see what I'm dealing with. I have MS Excel 2013 at my work computer, and I believe windows 10.
