Hi all,
Very new at using vba and dont have a clue!!
Need a macro/procedure which will search through a list of excel workbooks, extract the required information and provide a graph, bar or line. The information required should be selectable ie. dates, types, times.
Can anyone poiint me in the right direction....??!!
Many thanks
Hi There,
Your question seems to be very high level.
Can you pleae post example files and be a bit more specific in what you are trying to achieve. I might be able to help you out then.
J.
Hey,
I work for the railway in the uk and am creating a system which logs every time a circuit breaker trips....there are over 3000 breakers and I have created a seperate workbook for each. This is because infromation such as time, date, type of fault..etc is inputted through a userform into each appropriate workbook. To understand what is going on and to see any trends, I need to create graphs. Therefore, I need a macro to search through the workbooks and extract the correct inormatin fo charting through a userform(which I have allready done).
Many thanks, would appreciate any help!!
Hiya,
That sounds like a challenge :D
I am wondering whether it is really handy to use excel for the type of solution you want to setup.
but...okay lets give it a try.
I have the following questions for you:
- These 3000 workbooks, are these all located in the same folder on your computer?
or do you use a a clear folder structure to store all these files?- Do you use any structured naming convention for your files?
- Do your 3000 registration files all have 1 sheet with data in it?
- How many rows of data do you have per file on average over the year?
- Can you post an example file?
- Do I understand it correctly that you will use a user form to enable user to set report parameters?
- based on these parameters you will retrieve the info for the reports?
Based on your answers I can give you some ideas what to do.
Last edited by jevni1974; 02-01-2008 at 11:43 AM.
J.
Hi jevni,
I agree that excel is probably not the best way forward but the reason im using it is because everyone in the company has easy access to it.
Answers to yur questions:
1.) Yes, they will be located in the same folder just to make life easier!
2.) All the files are named after the circuit breaker id's ie.L101. There is no structured naming system.
3.)Yes, just one sheet. The data is inputted through a userform which I have all ready created and it works.
4.) Rows of data could vary from 0 to 150. It is difficult to give an average.
5.) I had trouble of attaching an example file but it just consists of 8 columns tiltled ECR, Substation/Tp Hut, Circuit breaker ID, Fault reason notes, time, date, logged by. Hope this helps.
6,7.) Yes, a userform will be used to determine the parameters. For example: The range of data determined by dates or times.
Thanks again for your interest and help, I really appreciate it.
Hiya,
I just had a very quick look at your post before going to sleep.
can you please zip the excel file and post the zip file?
I will come back to your input on short notice.
Thanks
J.
Hey,
Ive attached an example of what one of the 3000 workbooks would looklike..
Many Thanks.
Hiya,
So if I understand you correctly each circuit breaker id has its own excel file.
If I look at the example file the circuit breaker id column does contain different values. In a normal situation this column will only contain the circuit breaker id which is also mentioned in the xls file name, right?
Please find attached an example how to deal with the import of data from 3000 spreadsheets.
This has been my approach:The code is all in Module1
- Create a reporting workbook where all data matching the criteria is gathered in a sheet that has the same layout as each individual circuit breaker log file
- Create a macro in this workbook (ALT F11 to show the visual basic editor) that collects the data of all the individual circuit breaker log files.
- Add some named ranges (Insert > Names > Define) to facilitate the import
- Add a button to the data collection sheet that runs the macro.
You need to add the bit to check whether a row of data matches your selection criteria.
If you need some cool dashboars, then have a look here:
http://www.exceluser.com/catalog/pnp01/land1.htm
I have created some cool dashboards for some customers of mine based on the info on this site.
If you need more help, just let me know
J.
Hey,
Thanks for your efforts. However, as I am such a beginner at this I am having trouble running the macro. How do I define what information I want imported?? And everytime I try to run the macro I get a message which says
'Path not found
Occuring in:Module 1,function getalldata'
Is there any code I need to change for the macro to work??
Many thanks.
Hiran
Oops...
The reason why you get the error message is that the subfolder 'admin' does not exist in the folder where you put the Reporting.xls file
If you look at the macro code, the path where it expects the files to be is defined by:
So for this test, it takes the full path of the Reporting.xls workbook and adds the sub folder Admin to it, where it looks for the individual circuit breaker id files.Code:strPath = ActiveWorkbook.Path & "\Admin"
This means that if you have put the Reporting.xls workbook in e.g. c:\temp, the structure should look like :If you walk through the code (hopefully you can read it...) you can see that I added an evaluation function, called 'doCopyCurrentRow' that determines wheter to copy a specific row or not.
- c:\temp\Reporting.xls
- c:\temp\admin\L*.xls
Note though, that at this moment, all data will be imported, as I don't know how your selection criteria screen will look like.
Once you have that setup, I can help you making the sure that data is getting imported based on the selected criteria.
Hope this helps
J.
Hey J,
Hope you are well..!
Still having trouble running the macro you sent me...It keeps showing an error, any suggestions??!
Also, I have included an example of how I want the information extracted and displayed. If you open the userform, I want to be able to produce a graph from thbe criteria on it. Select a date range and plot the amount of trippings in this range either by single circuit breaker or by ECR(a selection of circuit breakers belonging to the selected ECR). This is calling up the information from each of the workbooks. There is an example of how I would like the graph to like and also possibly how the raw data might be manipulated to produce it.
Thanks again for your help! It is much appreciated!
Hiran.
Hiya,
Can you please indicate the exact error message you are getting?
Regarding the report, did you already extend the workbook I created for you with a userform on which the selections can be made?
Once you have that I can show you how to retrieve data from the individual excel files based on that form.
To be able to produce the report of which you posted the example, you have to create a pivot table chart based on the data that will be put in the Base Data sheet of the workbook I created for you.
J.
J.
Hey J,
The error message is: 'Path not found
Occuring in:Module 1,function getalldata'
This is what happens when you press the 'get data' button.
Do i need to create any sort of new file or or something to get it work?? All of the files are in the same location.
The userform example I attached was to just show you what data extraction options I needed, it doesn't have a macro written for it yet to interact with the base data....wasn't sure how to do it??
Yes, this information extracted would be used to produce graphs as shown in the examples.
Thanks for you help..!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks