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

View 3 Replies


ADVERTISEMENT

Fill Array To Execute Loop?

Apr 25, 2014

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

[Code]....

View 1 Replies View Related

Loop Through A Folder And Save In Different Folder

Apr 26, 2014

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.

View 5 Replies View Related

Sum Rows Recursively - Based On Another Column

Aug 14, 2012

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..

View 2 Replies View Related

Move Files To Folder By VBA

Aug 6, 2012

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

[Code]....

View 4 Replies View Related

Move Files From One Folder To Another

Oct 29, 2012

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).

View 1 Replies View Related

Excel 2007 :: Conditionally Format Cell To Have The Fill Color Of Different Cell

May 25, 2011

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.

View 4 Replies View Related

Move File To Folder Based On Name

Sep 10, 2008

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?

All of the excel sheets are named like this:

Bldg _ Date

1130_2008-January
1200_2008-March
1300_2008-January

I need the Bldg # to be a folder.

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.

Main Macro (calls out Macro2 & Macro3)

View 14 Replies View Related

Move Files From One Folder To Another Up To Maximum

Feb 7, 2008

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)

View 2 Replies View Related

Files To Move To Folder Created By Another Macro?

Aug 11, 2014

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.

View 4 Replies View Related

Move Files To Another Folder Delete Original

Sep 8, 2009

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.

View 8 Replies View Related

Look In Specified Folder For All Files, Move Them To Main Workbook

Jul 21, 2008

I have this code so far using Macro Recorder but i wanted to change a few parts which i am not too sure how to do:

Its basically supposed to look in specified folder for all files, move them to Main Workbook & rename them slightly

Sub getcsv()
Workbooks.Open Filename:= _
"C:Documents and SettingsSlimDesktopPositionsChartsAUDCADm1440.csv"
Sheets("AUDCADm1440").Select
Sheets("AUDCADm1440").Move After:=Workbooks("Position Risk Calc v9.8.xls"). _
Sheets(23)
End Sub............................

View 9 Replies View Related

Get Data From Changing Filenames & Move Files To Another Folder

Nov 22, 2007

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

View 7 Replies View Related

Loop Through Files In A Folder

Mar 11, 2014

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.

View 5 Replies View Related

Excel 2010 :: Copy File / Rename And Move To New Folder

Jan 15, 2013

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

[code]....

View 5 Replies View Related

Loop Through Folder And Add Cells From Each File?

Mar 20, 2014

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).

[Code] .......

View 6 Replies View Related

Loop Throough All Files In Folder

Apr 28, 2009

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.

View 3 Replies View Related

Macro Run As A Loop On All Files In A Given Folder

Mar 22, 2008

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...........................

View 10 Replies View Related

Loop Through Folder, Run Macro On All Files

Mar 5, 2009

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

strDocPath = "Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:"
strCurrentFile = Dir(strDocPath & MacID("TEXT"))

View 9 Replies View Related

Loop Through Mapped Network Folder

May 27, 2008

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

View 2 Replies View Related

How To Move To The Next Row In A Do Loop

Nov 10, 2006

In a macro I made, I have a do...while loop:

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?

View 9 Replies View Related

Save Outlook Attachment And Move Email To A Folder Based And Sender

Feb 23, 2014

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.

[Code] .....

View 2 Replies View Related

Loop Through All Files Rename And Save In Another Folder

Feb 27, 2013

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

[Code] .....

View 8 Replies View Related

Excel 2011 :: Loop Through All CSV Files In A Folder

Nov 23, 2011

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?

View 6 Replies View Related

Loop Through List And Delete Files From Certain Folder

Jul 2, 2014

Say I have a list in column A

Image1.jpg
Image2.jpg
Image3.jpg
Image4.jpg
Image5.jpg

In VBA, how can I loop through the list and delete files from a certain folder. The list will vary in length from time to time.

View 9 Replies View Related

Loop Not Working - Move Onto Next Row

Mar 28, 2012

What i am trying to do:

*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

The code I am using:

Private Sub UserForm_activate()
'Unprotect Workbook
ThisWorkbook.Worksheets("Cover Page").Unprotect Password:="password"
ThisWorkbook.Worksheets("D - Documentation").Unprotect Password:="password"
ThisWorkbook.Worksheets("E - Electrical").Unprotect Password:="password"
ThisWorkbook.Worksheets("F - Process Flow & P&ID").Unprotect Password:="password"

[Code] .......

View 1 Replies View Related

Run The 'loop Through A Folder' Code On Multiple Workbooks I Receive

Jun 5, 2008

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.

[url]

View 14 Replies View Related

Worksheet Names :: Loop Through All The Workbooks In A Network Folder

Oct 6, 2008

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).

View 6 Replies View Related

Excel 2010 :: Loop Through To Check For All Filenames In Folder And If Then?

Dec 22, 2013

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.

View 1 Replies View Related

Convert Horizontal Data To Vertical - Loop Through Files In Folder

Apr 9, 2014

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:

<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
1
2
3
4
5
6

This is the code I have so far:

Sub Consolidate()

Dim a As Workbook
Dim b As Workbook
Dim x, y(), i&, j&, k&
Dim myPath As String
Dim filename As String
Dim wb As Workbook

[Code] .........

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved