Recursively & Conditionally Move/Loop Through Folder Hierarchy To Fill Array
May 7, 2008
i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file)
right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work)
what am i doing wrong? all the things i can find on google show it the way i am doing it.
Sub GetDirList(topfolder As String)
Dim FolderArray() As Variant
Dim FolderCount As Integer
Dim FolderName As String
FolderCount = 0
FolderName = Dir(topfolder, vbDirectory)
' Loop until no more folders are found
Do While FolderName <> ""
If Not FolderName = "." Then
If Not FolderName = ".." Then
FolderCount = FolderCount + 1
Redim Preserve FolderArray(1 To FolderCount)
FolderArray(FolderCount) = FolderName
End If
End If
how to fill array with cells form selections and loop my macro for each cell of that array.
Code: 'this is macro I need to execute for each cell in my selection. Here I threat each cell from selection as solo selection.
Sub SELECTION_CELL_COUNT_SINGLE() Dim z As Integer, q As Integer, X As Boolean, I As Integer, txt As String Dim tmp As String, J As Integer, K As Integer Dim sStrip As String
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
I am trying to work out a formula to populate column D ie to add up the all the orders for that customer for the month ( the period of the data in the spreadsheet ). Just got no idea where to start..
In the code below I would like to add to move these files:
byemployee.csv byposition.csv status report.xls bydepartment.csv
and if the file byband.csv exists, then move also otherwise ignore. I want to move to the folder Uploads which is also found under the variable MyPath
If it cannot be included in this code, then I have no problem with a new one.
Private Sub Exitbtn_Click() Dim MyPath$, MyName$ Application.ScreenUpdating = False Call chooseView.hideSheets 'Save the file to this path and type file name
I have the following two folders located in my D Drive:
1. D:FilesData
2. D:FilesArchives
I have lots of files (.txt) in the first folder (D:FilesData) from which I need to move all the files whose first character is starting with _(underscore) to the second folder (D:FilesArchives).
I have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red blue yellow green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
All I have a macro that goes to a "Main" folder modifies the excel sheet and then saves. Is there a way to move that "Modified" sheet to the correct folder after?
So all of the Excel files are in this folder: C:Documents and Settingsu369875DesktopProject stuffTestin Save_AS
And then at the end of that I need the bldg Number from the front of the excel name. So for the first one : C:Documents and Settingsu369875DesktopProject stuffTestin Save_AS1130
Or I could even put that building # in the excel at lets say (A1) and grab from there if that would work..
Here is the macro I have as of now. It loops through all of the excels in the folder.
I am trying to create a chart that will automatically expand to include additional rows. I have seen posts and examples of the kind of chart I am aiming for, but only showing one series… my chart needs to show several series and be able to add new ones in automatically. For example, I want to make a chart showing the Total Market, and each separate manufacturer, over 13 time periods. If a new manufacturer appears, I would like the chart to show it without having to manually change the source data. (see attached spreadsheet)
I have the following macro set up to create a folder in a directory as per the contents of 2 cells
C2 = Folder name to be created B2= Date for previous working day
[code]....
I then have another macro that moves files into a specified folder within the same location ( I then manually move them into the folder the first macro created)
Is there a way I could get the files to move to the newly created folder without me having to do it manually.
i would like to be able to move files from one folder to another then delete the original files. I dont think im passsing the variables to the functions corectly.
I am trying to develop an excel macro to accomplish the following:
-I have a master workbook for collecting data -One folder will be used to dump excel files into; I don't know the filenames (other than "*.xls"), but I do know from which cells I will need to retreive data (they will be the same for all files)
1. I need to retrieve data from these excel files 2. After getting data from a file, it is then moved to another folder 3. Next the retreive data, then move is performed on the next excel file until there are none left in C:IN
Upon searching this forum, I found a way to open each of these files one by one (without having to know the filenames): Using a script to open unspecified file name
- For me, the obstacle seems to be not knowing how to do what I need to without knowing the filenames
- Again, the issue - retrieve data from these files, then rename/move these files
I would like to create a Macro which does the following task. I have a Workbook Master.xlsx with a worksheet "source". The path of Master.xlsx is C:Test
In the Directory C:Testprojects i have about 50 files which all contain the String "Forecast" in their filename. These Forecast Workbooks need an update in the Worksheet "actuals"
So, i need a Macro that copies WS "source" from WB "Master" to WS actual in all WB's Forecast.
I am trying to copy a file, rename it, and save it to a new folder. I keep getting a "Compile Error ; Syntax Error" at line FileCopy (ImagePath & oldName, NewPath & newName). I am using Excel 2010.
FileCopy (ImagePath & oldName, NewPath & newName)Sub RenameFiles() 'Renames file based on "sheet 1" - Column 1 Old file name - Column 2 New file name Dim oldName As String Dim myfile As String Dim newName As String Dim ImagePath As String Dim NewPath As String
I have a folder that has multiple files that i would like to pull data from. This data is in the same cell in each file so all i would like to do is add the total value up at the end once it finished looping through all the files and display it on my sheet. Some of these files could have 1 worksheet where others might have 3-4. If I only have 1 file in the folder it works perfectly fine but as soon as the second file opens i get a Subscript Out of Range error (See code below for the line this error occurs on).
I did search but I couldn't find what I want. I have a load of .csv files in a folder. I want to loop through all files, run the macro below and close the .csv file without saving. My macro copied data onto another workbook and saves, but I don't know how to loop through all files within a folder.
I need some code that will open each file in the folder, run the macro below and close it again. I don't know how to put code tags in so I have put the code in a .txt.
macro to run as a loop on all files in a given folder but allow for the option to Browse for the folder I want this to run in?
Sub CleanUp() Dim i#, rng As Range With Application .ScreenUpdating = False .DisplayAlerts = False For i = Worksheets.Count To 1 Step -1 If Worksheets.Count = 1 Then GoTo e Set rng = Intersect(Worksheets(i).[11:11,13:13,23:23,25:25], _ Worksheets(i).UsedRange) With rng .Replace What:=Chr(32), Replacement:="", LookAt:=xlPart .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart End With...........................
I am trying to process 60+ data files. I've recorded/written macros that do all of the processing, and now I would like to write a loop that will go through a folder with the data files (.txt, tab delimitted), and run the code on each file. I am using the Mac Version of Excel 2004. I have tried to adapt some code that I found in the forums, but after fixing a few errors, now nothing is happening when I run the code. The code is below. I have it in a module in the VBA editor.
length of the code; I'm assuming the problem is in the very beginning or end (the code I added for the loop), as the code in the middle that does the actual processing works.
Sub ProcessData()
Dim strDocPath As String Dim strCurrentFile As String
I have read your loop instructions under FAQ entitled; Loop through a folder of workbooks. It works very well on a local drive but I need to have it loop through a folder on a network drive. the drive is mapped as h:\computer name2008shareddocs
Do While Not objXLworkBook.ActiveSheet.Cells(nSequence, 1).Value = gcstrEMPTYSTRING
.....bunch of code
Loop
In my file, the second row of data always contains blanks (i.e., I've got a HDR record, blank row and then rows of data). How do I make my code bypass that empty row of data and then continue processing all other records?
Should I put a "move to next record" in my bunch of code, and if so, how do I do that?
I was able to create a macro that saves an email attachment based on the subject and then move it to another folder. I would like changing it to do the same thing only based one the sender’s email. I have it currently reading the save path and subject from the Excel worksheet.
I have excel files in a folder, I need a macro to loop through all files, rename them and save them in another folder. the file's name is available in a cell in each file, I am able to get the file name as text (I had to dig a little to get the text as it was between ""). Well I am stuck right now because the macro is doing what it's supposed to do for one excel file only and does not continue with the rest of the available files for a reason.
VB: Sub RenameAllFiles() Dim Bk As Variant Dim n As Integer Dim NewName As String
I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...
Code: Sub RunCodeOnAllFiles() Dim lCount As Long Dim MyDir as string
[Code]....
For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.
Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?
*i have about 2000 rows. *start from first row *for each row that contains .pdf in one cell, i want another cell in that row to equal .dir *hyperlink cell if cell contains dir *move onto next cell (for all approx 2000 cells)
The problem:
*it is hyperlinking every cell even if the other cell doesn't contain .pdf. *i think it is looking at the first row and if the cell has .pdf it applies it too all cells
What I want to do:
*select entire row of first row (21) *if col 21 = .pdf then col 23 = .dir *of col 23 = dir hyperlink .dir to fname value *move onto next row (22)....do the same....and so on and so on all the way to row 2039
I am trying to run the 'loop through a folder' code on multiple workbooks I receive.
The workbooks I receive are full of drop downs that have associated values of 1-3 on the first sheet. (About 100 in total) This particular workbook has the drop downs on one worksheet and the numeric results on another worksheet 'Results'
The second workbook 'Totals' (very basic) , just referenced each 'Results' worksheet and had equations that averaged all the drop downs cell by cell.
I would love to be able to use the 'loop through a folder' code to open them and then average them on the 'Totals' sheet. The main reason is that I am delegating this to another person and would like to eliminate the risk or human error. ( unless it is my own)
I am a total VBA n00b. Any assistance would be appreciated.
If needed I can upload the code or sheet as an example.
The base folder would always be the same. ie c: estresults*.xls
The naming would be very similar.
This loop code seemed relevant as it did not seem to require any file naming and would run through a folder and process all XLS files.
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).
getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.
I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"
i.e. file 1 - sheet1 looks more or less like this:
1 2 3 4 5 6
same with the other files.
what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector
i.e
1 2 3 4 5 6 from file 1 and then 1 2 3 4 5 6 from file 2
I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:
