How To Assign Macros On All Files In Sub Folders

Apr 13, 2014

I have my main directory C:Perinatal Within that directory I have folders that contain results for each medical conditions (see attachments)

Within each subdirectory are 6 workbooks with (untidy) data results, all the same format just different numbers for each analysis. I want my MAIN_MACRO to loop through every single .xls file in the C:Perinatal, cleaning up the data of all files, and then saving.

I can use the following code to cycle through each folder one at a time, however, I'd like to loop through every subdirectory of

C:Perinatal instead to speed up the process, rather than having to change the path for each subfolder (e.g. C:PerinatalCANCERALL). Is this possible?

If this isn't possible how can I create a 'MyPaths' list and loop through all those directories.

[Code] ......

Attached Images :

folders.jpg‎
CANCERJPG.jpg‎

View 1 Replies


ADVERTISEMENT

Use Excel VBA To Copy Multiple Files From Different Source Folders To Different Folders?

Jun 12, 2014

I need to back up files, which may be excel or MSword, by copying them from different directories, say C: to the backup directory, say X:. any vba to backup the files? I expect the vba can copy all file listed in column A.

E.g. C: est1File1.xls or C: est2File2.doc, and then pasted to the directory in column B. e.g. X:BackupFolder1 or X:BackupFolder2.

View 1 Replies View Related

Consolidate Data From Multiple Files Within Folders & Sub Folders

Aug 16, 2009

I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.

The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.

I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.

Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).

Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?

Sub Click2()

Application. ScreenUpdating = True
Dim MCDrow As Long
'Dim SubFolders As String
MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row

Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory
'SubFolders = True
Fname = Dir(Fpath & "*.xls")
Do While Fname <> ""

ThisWorkbook.Sheets("Client Data").Unprotect

Workbooks.Open Fpath & Fname
Worksheets("Client Data").Activate
Worksheets("Client Data").Unprotect

View 6 Replies View Related

Copy And Rename Files In VBA By Searching Multiple Folders For Files?

Jan 4, 2014

how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below

Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec

In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)

View 1 Replies View Related

Files Within Sub Folders

May 29, 2007

I am using the code below to list all files within a specific folder however I need to know about ALL the files within the underlying sub folders (of which there are many).

I have tried wildcards when declaring the folder but it does not work.

Sub List_Files()

Dim fsoObj As Object 'Scripting.FileSystemObject
Dim fsoMapp As Object 'Scripting.Folder
Dim fsoFil As Object 'Scripting.File
Dim sFolder As String
Dim i As Long
Dim filtype As String
sFolder = "V:T-ASSETAsset ScotlandNetwork Capacity Section1 Business Plan"

Set fsoObj = CreateObject("Scripting.FileSystemObject")

'Set fsoObj = New Scripting.FileSystemObject........................

View 9 Replies View Related

VBA Saving PDF Files Into Folders

Sep 29, 2013

I have this code that generates excel pages as pdf files, but I can't get it to save it in its individual folders. For example, I have the sheet name as Brian Lin, I want to save the pdf into the folder called Brian Lin, then if I have the sheet name as Lilly Tran, the pdf should save into the folder called Lilly Tran. Here's my code. The code is one step short of what I want it to do, it saves in the folder right before the name folders.

Code:

Sub Macro1()
Dim wsh As Worksheet, vWshs
Dim sFolderName As String, set_Path As String, name As String, hwas As String, sname As String, swsname As String
vWshs = Array("Rates", "Inputs")
set_Path = "C:UsersJonathanDesktopFaFundInvoices" & hwas & ""

[Code]...

View 4 Replies View Related

Finding Files In Folders

Jun 12, 2008

I need to design code that can check to make sure a specific file is found before it executes.

Our files are all going to start with the same words followed by the date

ex. Name 10-2-1998

I want the code to be dynamic and be able to check in the folder that the current excel sheet is in for the file i need and then have it ready for use in my macro.

View 9 Replies View Related

List Files And Folders

Jun 19, 2008

I have a folder that has a bunch of other folders in it, each containing many files. Is there an easy way to get a file directory into excel, so that I now would have a spreadsheet that tells me what all the folders are and what files are in each one?

short example: main folder = mystuff
subfolder1 = my pix, contains pic1 and pic2
subfolder2 = otherstuff, contains otherstuff1 and otherstuff2

Can excel find these things and give me a list indicating that the 'mystuff' folder contains these two folders, and also list the two files under each folder?

View 6 Replies View Related

Moving Files From Folder And Sub-folders

Jan 29, 2014

I need to move all the "Excel" files which are available in Folder and also the Subfolders from this location.

I need all the Excel files.

I found a code which is able to move only from Folder but not from the Subfolder.

HTML Code: 

Sub Move_Certain_Files_To_New_Folder()
'This example move all Excel files from FromPath to ToPath.
'Note: It will create the folder ToPath for you with a date-time stamp
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String
Dim FNames As String

[code].....

View 3 Replies View Related

Loop Through Files In Dated Folders

Nov 30, 2008

each folder is named by date, for example a folder name may be 04-01-2008. Within each folder there is one xls file named daily.xls.
(this never changes)
In date order from the folder name, I need to open each daily.xls run my code, then close daily.xls and then open the next one etc until there are no more.

example path is C:4-01-2008daily.xls

View 9 Replies View Related

Create Folders & Copy Files To It

Jun 26, 2007

I need to create a macro that will allow the user to create a directory based on a template directory, with all directory paths/ names stored in workbook cells:

1) Create a new directory (name/path specified in workbook)
2) Go to a template directory & copy the entire directory, subdirectories & all files
3) Paste them into the new directory
4) Save a copy of the workbook into the specified location from (3)

Example:
X:Templates
-copy all to-
X:Projects(Value specified in workbook cell)

View 3 Replies View Related

Breakdown Of All Folders & Files In Directory

Sep 12, 2007

I am trying to specify all files and folders in a directory and the only help I found was the following thread: Create Index Of Files In Folder That works really well but, what I need is to specify all the folders and all the files under a directory, does any of you awsome hacks have an idea of how I can enhance the macro if the thread posted above?

View 3 Replies View Related

Hyperlink To Files Including Sub Folders

Aug 5, 2008

I am currently using the following code found in this post: Create Index Of Files In Folder

I am not familiar with VB at all. All I want to do is be able to index the sub-directories contained within the main directory. Is there a simple way to edit this code? ...

View 9 Replies View Related

Renaming Files In Infinite Number Of Sub Folders?

Feb 25, 2014

I have written a procedure that renames excel files within a folder based on certain words being in the title. It works for the files in the first lot of subfolders but I am trying to work out how to get it to rename all files in all subfolders regardless of the level.

I've seen similar things done using recursive subs but I have been trying to convert some of the sample codes I've found online to fit my situation and am not having much luck.

how to manipulate my code into something like a recursive procedure or anything else that will do what I want.

Here is my code.

[Code] .....

View 2 Replies View Related

How To Traverse Folders And Files On SharePoint With Excel VBA

Mar 9, 2011

how to traverse all files in SharePoint folders using Excel VBA and have not come across an answer I could use because, by configuration design, I cannot map a SharePoint URL to a drive letter.

Here it is:

Sub SPDir()
Dim wb As Workbook
Dim dummyFile As String
'
' The file specified by dummyFile must reside in SharePoint in order to use SharedWorkspace
' The way the code is set up, the path and filename in dummyFile should NOT substitute %20 instead of spaces
' There is logic later to ignore dummyFile on output
' Substitute your own dummy file name below

[code].....

View 9 Replies View Related

Write Script Which Returns All Files And Folders?

Nov 2, 2012

I need to write a script which returns all files and folders. The script should also work on a Mac pcs. The "Windows" script works without any problems but I have real problems with the Mac script:

Code:
Public Sub List_Files_Mac()
Dim sDir As String
Dim sFile As String

[Code]...

This script doesn't return the folders and long filenames are truncated.

View 4 Replies View Related

Search Folders And Create Hyperlinks To Files

Feb 18, 2009

I have a list of file names sans extension in column A. I want to search a folder I specify and if file is found create a hyperlink to said file either in a new cell or in column A.

The code I have does the search fine, but its hyperlinking is offset and I cant get it to match the link to the file name.

Also it breaks if it can not find the file.

Here is the code...

View 9 Replies View Related

VBA Macro To Loop Through Folders And Select Files With Certain Criteria

Feb 6, 2014

I'm looking to create a macro that does the following:

- Loops through a folder structure
- Opens a specific subfolder within each folder based on name (i.e. "*Financials*")
- Opens a specific file within that subfolder "*Financials*" based on both most recently modified a naming criteria (i.e. "*Model*")
- Performs an action on that file (a macro created elsewhere that I assume I can call here)
- Closes that file, and moves on to the next file matching the criteria above

As a self-diagnosed VBA novice, I'm having trouble adapting code found in various forums for my specific project.

I've found examples that address aspects of my desired macro or do similar things, but I can't figure out how to put them together.

Open most recent file:

[Code] .....

Loop through folders and subfolders and print all files:

[Code] ....

View 1 Replies View Related

Looping Through Files In Multiple Folders Searching For Certain Values

May 1, 2013

I'm trying to code a macro to search through all the files in certain folders to find a value defined by the user. The rows containing that value will then be copied and pasted into a separate workbook. My boss currently planning on storing about 550 different files (90 days worth of data) between these folders, but there is the possibility of years worth of data collection, should he change his mind about the 90 days, so I'd like the code to be efficient if possible. What I've tried to do is search each file for the value, then set a range equal the first row containing that value. If the value is found in that workbook, the code loops through the worksheet, adding all the other rows with that value to the range. Then the file looping exits (the values I'm looking for are only contained in one of the workbooks) and the range is copied and pasted into the master workbook. Each workbook has only one sheet.

I'm currently getting a run-time error "13" Type Mismatch error when it gets to the line where the code is supposed to find the value and initialize the range.

Code:
Private Sub SubmitButton_Click()

Dim mybook As Workbook
Dim masterbook As Workbook
Dim rownunm As Long
Dim pathparts(1 To 5) As String

[Code] ......

View 3 Replies View Related

Using Macro To Print Various Word And Excel Files In Different Folders

Jun 2, 2014

What I want to create is an excel file such as:

A
B
C

1
File
Qty
Yes/No

[Code]......

Where Qty sets how many copies for that particular doc, and Yes/No determines if it needs to be printed at all.

Each file will be in a subfolder relative to the excel file.

So it would work like this (somehow)

If Cell, C2 = 1, then print CurrDirSection1examplefile.xlsx, qty= B2 , defaultprinter
If Cell, C3 = 1, then print CurrDirSection2examplefile.docx, qty= B3 , defaultprinter
If Cell, C4 = 1, then print CurrDirSection2examplefile.docx, qty= B4 , defaultprinter

and so forth.

View 3 Replies View Related

Move Files To Dynamic Folders Based On Cell Value

May 13, 2007

I am not so sure this can be done through a macro, but here is what I want.

I have a folder with say around 1000 excel workbooks. I need a macro to

1. Open each file in the directory specified
2. Read the contents of a particular cell, for ex, A1
3. Create a folder in the same directory with the value of A1
4. And, move the excel workbook itself to that folder.

5. Then it opens the next file
6. If the value of A1 is the same as that in the first workbook, it simply moves it to the already created folder, else it creates another folder with the new value of A1 and moves it there.

Hope I am not too confusing. Searching the forums does not give me an exact picture of how it can be done.

Note: I would also like an option where when I execute the macro, it should pop up with an option to select the folder in which the files are, since the files are not always saved in a static file path.

View 6 Replies View Related

Assign Two Macros To Same Button

May 10, 2014

How can I execute two macros with one button press?

the sub names are CDO_Text and CDO_Mail

View 2 Replies View Related

Opening Excel Files In File Path Which Includes Folders?

Nov 7, 2007

I am trying to open excel files in a file path which includes folders which also have excel folders i wish to open there are quite a few.

At the moment i am working with this code but it fails to open excel files which are within the folders in the specified file path. Its fine for excel workbooks in the folder specified by file path.

This is the code

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook

[Code].....

View 9 Replies View Related

Time For Getting Data - Search For 445 Files In Folders On Specific Path

Jan 1, 2013

I build a VBA that search for 445 files in folders on specific path.

The code opens each file, take each time the same 31 rows, filter them and paste in the main data base file and then close the origin file.

So, for 445 files it takes about 6:30 minutes.

View 4 Replies View Related

Create Macro That Will List All Folders / Subfolders And Files In Order In Excel?

Feb 8, 2013

My problem is to create a macro that will list all folders, subfolders and files in an order in excel. So that I know which folders and files belong to which main folder, more like a hierarchy.

For example,
Sourcefolder
Subfolder 1
File1

I have code that list all files in folders and subfolders, but it does not put them in a hierarchy fashion.

View 8 Replies View Related

Excel 2010 :: Rename Files In Multiple Folders And Copy To New Folder

Sep 22, 2013

For the last year I have been downloading cash register X1 and Z1 files onto an SD card. Each night's files (X1 & Z1) are stored in a new folder with the naming convention "RP + [DATE]", (EX: RP120910, [YYMMDD]). I have 265 folders! I need to go through each folder, select the Z1 file (I don't care about the X1, X2 or Z2 files) and rename each with the following naming convention: "Z1 + folder_name + .xls". For Example: Folder RP120910 contains the Z1 file named "Z1_T1729.ECR". After renaming, the file should be "Z1RP120910.xls". As the VBA code loops through each subfolder, selecting and renaming the Z1 files, I would like the new files to be placed into a new folder named "Z1Files". Note: All subfolders are currently in a directory named "RegisterFiles". I am using Excel 2010.

View 3 Replies View Related

Search Folders & Subfolders For Files By Date Modified & Move/Copy Them

Aug 5, 2009

I'm trying to make a macro that will search a folder and move all files from 2008 into another folder. So far it can do this, but what i need is for it to search subfolders also. At this point it moves all files from the main folder, but nothing from the subfolders.

Option Explicit
' Reference site [url]

Sub Copy_and_Rename_To_New_Folder()

Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim PathExists As Boolean
Dim objFile As File
Dim SrcFolder As String
Dim DestFolder As String
Dim x, Counter As Integer, Overwrite As String, strNewFileName As String
Dim strName As String, strMid As String, strExt As String
Dim LastModYear

View 5 Replies View Related

Assign 2 Macros To 1 Button (or Adding VBA Code To The Existing Macro)

Dec 23, 2009

I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"

I've been trying to figure out ways to edit the VBA in "Module 1" to make this work

Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.

View 6 Replies View Related

Excel 2010 :: How To Assign RELATIVE Macros To Custom Ribbon

Aug 19, 2014

I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.

Long story short, I will be saving several variations of this workbook every so often when I get new data.

The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.

Is it possible to assign relative macros to custom ribbons?

From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?

View 6 Replies View Related

Arrange Or Group Folders Based On Assigned Icons To Folders Using VBA

Dec 20, 2011

I have folders with different assigned folder icons based on the content in the folders.

I would like to arrange these folders in the window based on the assigned folder icons and then by alphabetical order.

How it is to be done. Can we make use of vba having the folders path list in one column of excel sheet?

View 9 Replies View Related







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