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

tnrhodges · May 16th 2016

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!

Thanks!
Ryan
Excel to help visualize process forum.ozgrid.com/index.php?attachment/69137/

M40wen · May 17th 2016

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

Hi,

So you need to break down into the different folders, and check that the folders/files are there to begin with. Personally I would set up your worksheet that the folder/file structure is stipulated and then just test accordingly. You could alternatively write the code to loop through every file, or even loop if the file name contains certain elements.

need to check that each folder exists in the structure, and that the file within that folder can be found, then go from there.

Code

Dim NWB as workbook
Dim Defaultfolder as string
Dim MyMonth as date


DefaultFolder = "C:\Users\tnrhodges\Desktop\2\
MyMonth = Today


Use the workbook and build accordingly to the structure
DefaultFolder = "C:\Users\tnrhodges\Desktop\2" & thisworkbook.sheets(1).cells(2,1).value & ""






'check folder exists, if not create it
On Error Resume Next
FolderPath = DefaultFolder 
If Dir(FolderPath, vbDirectory) <> vbNullString Then
     'file found
Nwb = Mid(IFwbName, InStrRev(FolderPath, "") + 1)
Else
MkDir DefaultFolder 
End If
On Error GoTo 0'now check for month subfolder
On Error Resume Next
FolderPath = DefaultFolder & ThisWorkbook.Sheets("Sheet1").Cells(Row, Column).Value & "" & MyMonth & ""                                <-- Change variables to what you want
If Dir(FolderPath, vbDirectory) <> vbNullString Then
     'file found
Nwb = Mid(IFwbName, InStrRev(FolderPath, "") + 1)
Else
Msgbox "I cannot find file!"
End If
On Error GoTo 0

Display More

MkDir DefaultFolder sets up a folder for you but you might want a message box to alert you (hence why I wrote both ways). Hope that gets you started

tnrhodges · May 17th 2016

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.

M40wen · May 17th 2016

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

Hi,

It was merely a starting point for the approach. You need to change the variables to what you have ie thisworkbook.sheets(1).cells(2,1).value to what it actually is. Its hard to help when I don't know your level of expertise. This is all possible, but what appears that you asking for is abit of work to do.

As you have stipulated that you only want specific files, id organise your data differently - currently you have the WKST as a merged cell with 9 values in it. Be easier that each cell has its own value.

Also requirements aren't clear to me - in your example, it says the most recent file is 11th May, does that mean you need to search for the last file each time? And then is the prior two weeks from that date, or the date you are running the code?

tnrhodges · May 17th 2016

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.

tnrhodges · May 17th 2016

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.

M40wen · May 17th 2016

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

Yes, you need to:-

1. Check each folder exists using the worksheet as reference
2. Check all files exists using the worksheet as reference
3. Open each file
4. Check the open file has the worksheets you want
5. Copy the range data from the open sheet to where you want it (therefore you need to find out the range - im assuming that it will always be the same columns, but the last row will be different)
6. Loop through all the sheets
7. Close the workbook you are importing from
8. Open next workbook and follow step 3 to 7 again until all files have been imported

Does that help?

tnrhodges · May 17th 2016

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

Did you see my post #6 about the wildcards? Is there a way to use those?

M40wen · May 17th 2016

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

Crossed!

Yes there is, but you need to decide whether to loop through a folder and only open files of a certain name or put all the files in your workbook and loop through all of them

Code

Sub OpenWBWildCard()
 Dim WB As Workbook
 Dim myPath
 Dim myFile
 myPath = ThisWorkbook.Path & "\"
 myFile = Dir(myPath & "*Stickthenamehere.xls*") ' -------------> You Could have the variables from your spreadsheet [/B]
Do While myFile <> ""
 Application.ScreenUpdating = False
 Set WB = Workbooks.Open(myPath & myFile)
 If MsgBox("this workbook ?" & vbCr & WB.Name, vbYesNo) = vbYes ThenApplication.ScreenUpdating = True
 GoTo mynext
 Else
 WB.Close
 Application.ScreenUpdating = True
 End If
 myFile = Dir()
 Loop
 Exit Sub
 mynext:
MsgBox "Okay all, now close " & WB.Name
 WB.Close
 End Sub

Display More

tnrhodges · May 17th 2016

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?

M40wen · May 17th 2016

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

Sorry for not being clear.... The file path will have a fixed element to it, then each folder/path/file you will need to assign variables to "build" up the full filepath and name.

And like I say, why get the code to potentially look through every file, get the name, and then do something on the basis of that name (open it or move on), if you already know in advance what spreadsheet to open ie you could be looping through 100 files to only open 1. Better to see if that file exists, if yes open it, if no handle the exception.

I learnt from this page http://www.rondebruin.nl/win/s9/win003.htm

tnrhodges · May 17th 2016

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

Ill check out the page, thanks for trying to help though! May just need to post in the hire help or make a new thread on this specific process.

M40wen · May 17th 2016

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

I can do it, but like I say its possibly a bit of work and I thought you wanted to learn how to do it rather than me just give the answer!

tnrhodges · May 17th 2016

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.

M40wen · May 18th 2016

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

I know that feeling - worse when you have deadlines and you don't know how to code something! Can I just ask what the column headers you are wanting to import are called (I assume they are all named the same) ? And the sheet you are importing too, is it just those column you are wanting to import and I should append the next file/sheet to the next blank row?

tnrhodges · May 18th 2016

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]

tnrhodges · May 18th 2016

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

Code

Sub FilePathsDateRange()
Dim FSO As Object
Dim fPath As String
Dim MyString As String
Dim Lvl1, myFile, SubFld, mySubFolder
 
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Lvl1 = FSO.GetFolder("C:\Users\tnrhodges\Desktop\2\")
If Err.Number <> 0 Then
    Exit Sub
End If
LastRw = Range("A" & Rows.Count).End(xlUp).Row + 1
For Each Lvl2 In Lvl1.subfolders
    For Each Lvl3 In Lvl2.subfolders
        Set myFolder = FSO.GetFolder(Lvl3).Files
        For Each myFile In myFolder
            If DateValue(myFile.DateLastModified) <= Date And DateValue(myFile.DateLastModified) >= Date - 14 Then
                Range("A" & LastRw).Value = myFile.Path
                Range("B" & LastRw).Value = myFile.DateLastModified
                LastRw = LastRw + 1
            End If
        Next
    Next
Next
    
End Sub

Display More

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

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

Participate now!

Share

Users Viewing This Thread