Posts by tnrhodges

    Hello all,

    I am working on a code to find cycle times from three levels of folders and I have everything working but now I am trying to make things look better. I am trying to copy my results in a summary worksheet and use a template that is already open but I keep getting the run time error. I am not very experienced but I was curious if I am just labeling something wrong or missing something simple. The code is below

    error happens on line Sheets("Summary").Range("A1:A" & LRowS).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Summary").Range("B2"), Unique:=True 'Copy the unique PkgID's to column B (removes duplicates accross sheets

    Re: Looking for advice on processing a simple code through an inception of folders!

    This gets all the file names that have been modified in the last two weeks. I need to find a way to remove the files we don't need, ones that have S1,S2,S3,U1,R1 in the machine number placing

    Re: Looking for advice on processing a simple code through an inception of folders!

    The Headers are as follows: The imported D column is Product Number, The Imported L column is Max Cycle Time, The imported M column is Average Cycle Time, and the imported N column Is Min Cycle Time.

    The end goal is to have 11 workbooks titled SA1, SA2, SA3... SA9, SAA, SAB which represent the lanes (column B in my guide), and then each workbook have worksheets for each machine titled A1,I1,M1,M2,M3,M4,M5,P1, and Q1 (column C in my guide), then each worksheet have the 2 weeks worth of the imported data on them. They can all just fill into the next row after the previous import. Make sense? Here is an example workbook [ATTACH=CONFIG]69164[/ATTACH]

    Re: Looking for advice on processing a simple code through an inception of folders!

    Well I have other things to do for work so Im kind of in a time crunch when it comes to learning. I also learn better by seeing code and then following through it, not really to the point yet of developing my own code for something that cant be recorded. If you had time to do that it would be awesome and very helpful! If not I understand.

    Re: Looking for advice on processing a simple code through an inception of folders!

    Ok processing time is something to consider so it seems like it would be quicker to loop through the folders and only open files that have a certain combination of digits in the name.

    And I apologize again because I don't understand what you mean by "variables from my spreadsheet" The spreadsheets I am getting the data from doesn't have any variable names, I just know what each column is. Or are you talking about variables that are in the spreadsheet name?

    Re: Looking for advice on processing a simple code through an inception of folders!

    Also I thought of something else that may make retrieving the files either with maybe wildcard indicators or something.

    The excel file names format are for example P21A11605111. P21A11605111. The violet 1 indicates a row number, we only want files that contain 1-9, A and B in this position. The Green A1 is the machine on the row so we only want files that contain A1,I1,M1,M2,M3,M4,M5,P1,Q1. The Red 160511 represents the date so in this example 2016/May/11 and we only want the dates from the current day to the previous 2 weeks. Let me know if this helps you understand and if there is a way I can use this to help me retrieve the data.

    Re: Looking for advice on processing a simple code through an inception of folders!

    I had coded with matlab, R, a little bit of basic in some classes during college so that is about all the experience I have. I just now started playing around with Macros because that is what we use were I work now and pretty much I have used the recorder on some things and googled so when it comes to making something from scratch I don't really know where to start.

    The problem is that I can not organize the data any differently. That example screenshot is just a sample of what comes off the production line and is updated at the end of every shift which changes on production. So even though that one has 9 rows, some may have 1, some may have 50 but I am only concerned about the highlighted columns.

    May 11 was just when I first made that guide but Im just now getting to working on it this week. The two week period will be from the time that I run the code to two weeks before not May 11. So as of working today it would need to work from May 17 to May 3. The end goal I will most likely make an input box to put in the range we are interested in but right now I am just trying to get the code basics down.

    But I do imagine my first step is to extract the file paths of the excel files I want to process but Im just not sure how to do that since Im only interested in specific subfolders and within those subfolders only the 2 weeks of files.

    Re: Looking for advice on processing a simple code through an inception of folders!

    M40wen thanks for replying however I believe I am a little confused due to inexperience. I tried running each of the codes and I do not get an error but nothing happens. Is this code supposed to be outputting the destination in my current workbook? I get nothing in it or my immediate window. Also, is it just checking for today cause that could be a problem since they have not been uploaded yet.

    Hello all! This is my first post but I have looked at the forums before for answers to some other questions so I thank you all for that already. I have made some codes for doing simple things like importing CSV into excel and things like that but now I need some advice on where to get started on this new project, or if it seems to complicated to do. The part of this project that I need help with is how to get my data from all the different folders, once I know how to get my data imported into new workbooks I can figure out the rest. I have attached a file that shows the complexity of retrieving the data that because it is almost too hard to explain via text! What I want to do is import columns D, L, M, and N from each excel file within a two week period which are the highlighted cells in the snipped image.

    The top left directory is the file that contains all the workbooks. Then the first layer is 1 - 9 and A and B. Each of these layers have folders within them such as A1, I1, M1.. and so on like the ones listed and within those are all the excel files that I want to process just the ones made within the past two weeks. I want to develop a workbook for each of the first layers and name them SA1 - SA9 and SAA and SAB, within these workbooks I want a worksheet for each of the subfolders such as A1, I1, M1.. and so on and on each of those sheets will have the combined columns of D, L, M, and N from the two weeks worth of sheets. So is this as complicated as it sounds or is it manageable?

    One issue is that there are other folders in the 1 - 9, A, B besides A1, I1 and so on so I cant just process every folder. Just the named ones I am interested in. Also, the last 2 weeks of data is not always the same amount of sheets, so I just need to process the sheets from "today - 14 days"

    There are some things that I need to do at the end but I believe I can do that, I just don't know how to navigate this tree of folders. Any advice will help!

    Excel to help visualize process