Hi,
I know this might look like a lot, but I don't think it is...I've tried to explain what I've done so far, and then asked for help on how to make it easier. Also have a question below on how to get back and forth between files, ones that I'm analyzing, and a main program that I'm pasting data into from files that match my criteria....Thanks
I have a VBA program in a file that performs an analysis on data in other files. Right now I have a msgbox pop up that gives me the files that match my criteria.
Right now I have to manually open the files, ie. the program starts, asks me what files to open, I open the folder where the files are, then click on the first file, click Ctrl-A and then the program runs through the files. When a file matches my criteria, right now a msgbox pops up with the information I need. I have to do this 26 times for the 26 files that contain files I want to analyze. Sometimes there are 150-200 matches.
First, What would be the easiest way in VBA-ese to:
1)open the folder through VBA (without me having to find it)
2)open each sub-folder, then open each file in the subfolder and run my program (only looking to have one file open at any one time) PS-I don't have to save the file once I've done the analysis
3)To make it easier, I could rename each subfolder, now ending with a letter of the alphabet, to folders ending with a number, so to open a new folder, all I'd have to do is append a variable I'm using onto the same folder (C:/myprograms/option(variable)), containing the files and add 1 to the count variable after it's gone through all the files in the subfolder.
My second major question is, instead of getting the msgbox popups, what I'd like to do is take that info, and paste it into the file that I have the macro in, on a new worksheet that is made each day, with the date as the sheetname.
SHYRATH offered this bit of code..
lr = Worksheets("Sheet3").Range("A65536").End(xlUp).Row + 1
With Worksheets("Sheet3")
.Cells(lr, 1).Value = msgboxvariable
End With
Thanks again Shyrath....
My question is: What would be the wording of code needed to make, let's call it
"C:/myprograms/mycriteria" the active workbook and "3/15/09" the active worksheet
1)will the above code get me back to the file that I just got the information from?
2)If so, will it continue to run the program so that the rest of the files are opened and analyzed in that folder and in the rest of the folders?
3) If not, would I just use the same code used to open the "...mycriteria" file? One of the pieces of information I am pasting is the last part of the name of the file, so I could just addend that onto the end the code, ie . "C:/myprograms/option(variable)/" & "filename"
Kenman
I know this might look like a lot, but I don't think it is...I've tried to explain what I've done so far, and then asked for help on how to make it easier. Also have a question below on how to get back and forth between files, ones that I'm analyzing, and a main program that I'm pasting data into from files that match my criteria....Thanks
I have a VBA program in a file that performs an analysis on data in other files. Right now I have a msgbox pop up that gives me the files that match my criteria.
Right now I have to manually open the files, ie. the program starts, asks me what files to open, I open the folder where the files are, then click on the first file, click Ctrl-A and then the program runs through the files. When a file matches my criteria, right now a msgbox pops up with the information I need. I have to do this 26 times for the 26 files that contain files I want to analyze. Sometimes there are 150-200 matches.
First, What would be the easiest way in VBA-ese to:
1)open the folder through VBA (without me having to find it)
2)open each sub-folder, then open each file in the subfolder and run my program (only looking to have one file open at any one time) PS-I don't have to save the file once I've done the analysis
3)To make it easier, I could rename each subfolder, now ending with a letter of the alphabet, to folders ending with a number, so to open a new folder, all I'd have to do is append a variable I'm using onto the same folder (C:/myprograms/option(variable)), containing the files and add 1 to the count variable after it's gone through all the files in the subfolder.
My second major question is, instead of getting the msgbox popups, what I'd like to do is take that info, and paste it into the file that I have the macro in, on a new worksheet that is made each day, with the date as the sheetname.
SHYRATH offered this bit of code..
lr = Worksheets("Sheet3").Range("A65536").End(xlUp).Row + 1
With Worksheets("Sheet3")
.Cells(lr, 1).Value = msgboxvariable
End With
Thanks again Shyrath....
My question is: What would be the wording of code needed to make, let's call it
"C:/myprograms/mycriteria" the active workbook and "3/15/09" the active worksheet
1)will the above code get me back to the file that I just got the information from?
2)If so, will it continue to run the program so that the rest of the files are opened and analyzed in that folder and in the rest of the folders?
3) If not, would I just use the same code used to open the "...mycriteria" file? One of the pieces of information I am pasting is the last part of the name of the file, so I could just addend that onto the end the code, ie . "C:/myprograms/option(variable)/" & "filename"
Kenman
Last edited:
