Create Folders VBA
Dec 6, 2007I m having difficulty combining code to do this; create folder in network drive from excel list
T:Bob
Then subfolder T:BobFinancials
Then subfolder T:BobSecurity
ADVERTISEMENT
Making Macro That Will Create New Folders And Then Save In Those Folders?
May 21, 2014I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.
As an brief example.
Cell "B3" on the master(worksheet) contains a job name I-40 bridge
So initially I would like the Folder name and file name to be I-40 Bridge
However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.
I am currently working with Excel 2007 Pro edition on windows 8. I
I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.
Public Sub SaveAsA1()
ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub
Loop Through Folders And Create Folders?
Mar 31, 2014I want to open C:Projects
and for each Project in Projects, create a new folder with the actual month (e.g. "03" or "12")
Im running the Makro from a file where I'd like to put the month in a cell -> so the code refers to cell("A1") wich has value 01 and creates a folder "01" in every projectfolder in C:Projects
Create Folders With Dates
Mar 2, 2008I have a silly little sheet that i am trying to create to help me create a lot of folders on my pc. The sheet will basically make a consecutive group of folders that list out a 7 day period and a week number.
The problem i am having is trying to paste the special values of the dates in a group of cells, and then using those values to create my multiple sets of folders. I do not know how to get around it using the 5-digit date codes when it creates the folders.
Create Folders & Name By Cell List
May 25, 2006way to create a group of folders in a specific location using a list of cells from an XLS file as the folder names.
For example, it would take whatever text is in Cell A1 and create a new folder in a specified location. It would take the text in Cell A2 and create a new folder in the same specified location. Etc.
Create New Folders In Variable Location
Oct 17, 2006I would like to create new empty folders from the list in column B.
The number of folders will vary depending on how many entry in column B.
I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings".
The following code may be able to be modified.
Sub CreateFolders()
Dim MyFile As String
Dim sDir As String
Dim rng As Range
Set rng = Sheets("Matdata").Range("B2")
While rng.Value <> ""
MyFile = rng.Text
sDir = "CurDirShop_Drawings" & MyFile
''above is where I am having trouble...don't know the correct syntax''
MkDir sDir
Set rng = rng.Offset(1)
Wend
End Sub
I have attached a sample workbook.
I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.
Create Folders & Copy Files To It
Jun 26, 2007I 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)
Create Macro To Rename Worksheets In Other Folders
Dec 23, 2008I have created an appointment schedule spreadsheet. Once I get the spreadsheet running smoothly, I would like to create a worksheet for every day of the year that we are open.
I have decided to have one main folder with 26 subfolders in it. In each of those 26 subfolders, there will be one workbook with 12 worksheets in it. That will be two weeks worth of appointments as we are open Mon-Sat. Of course I want to name the worksheet tabs at the bottom of the workbook according to the appropriate calendar date. Then there is also a cell at the top of each page that also has the date, the same as the date on the tab. Just wondering if there is a simple way to create a macro to rename all these worksheet tabs, or if I have to physically open up each workbook, and rename all the worksheet tabs according to the calendar date. Then once the worksheet tab is named, can you make it automatically put the same date into Cell A1?
Create 26 Folders (depends On Financial Year)
Apr 16, 2009I want to create 26 - 27 folders (depends on financial year)
Basically in the current folder I want the folders to be created in the following format FE 22-04-09 for each fortnight in the financial year.
Search Folders And Create Hyperlinks To Files
Feb 18, 2009I 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 can’t get it to match the link to the file name.
Also it breaks if it can not find the file.
Here is the code...
Create Folders Named As Cell Text
Jul 15, 2006I have attached a workbook that creates new folders based on text in a cell.
As you can see when the button is pressed it runs the macro which when it is done calls the next & so on.
What I would like to know is there a way of acheiving the same results with a single macro.
Eg column A contains 4 cells with text. The macro when run will check for cell content then create folder based on that text macro will stop when next cell is empty.
Create Macro That Will List All Folders / Subfolders And Files In Order In Excel?
Feb 8, 2013My 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.
Use Excel VBA To Copy Multiple Files From Different Source Folders To Different Folders?
Jun 12, 2014I 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.
Arrange Or Group Folders Based On Assigned Icons To Folders Using VBA
Dec 20, 2011I 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?
Search Folders And Sub Folders For Worksheet And Return Range?
Jul 8, 2013macro and I think this has me on my way; however, I do not understand the code and I am having difficulty modifying it. It returns the path in cell $A1 of my workbook, but I get reference errors in $B1:$L1.
I would like for the code to start entering the values in row 2 of my current worksheet. Row 1 I want to make a header row. The first column I like having the file path. Can the path be changed to a hyperlink? I would then like for it to return the values in $AP1:$BC1 to $B1:$O1 of my current workbook.
Consolidate Data From Multiple Files Within Folders & Sub Folders
Aug 16, 2009I 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
Opening All Folders
Jun 20, 2008I have about 20 plus workbooks in a folder. Each has 52 sheets, Fridays date, that I want to copy all the data from each sheet.
Is there a way to open all workbooks in a folder and copy all data from on all the various sheets into a single workbook?
I'm trying to gather the info and make a small data base from the various books. I'd like not to have to call each workbook by name as the file names change or get added to.
Listing Folders With VBA
Oct 10, 2008I have found a lot of posts which show me how to list a directory tree in an Excel file, usually using a macro. I'm just uninitiated with VBA / VBS and simply don't have the time to figure it out now (it really isn't part of my job description... I'm rather doing a favor here).
I need to be able to re-generate an excel-based list of clients at will. The client names are subdirectories of different case types. There aren't tons of case types, right now maybe 20 but I need to be able to add case types without breaking the script.
case_typeslast,_first_name........
Run Macro In Sub-folders
Dec 25, 2010I have created a macro named "SummaryCalculation" and I want to run this macro to more than 100 excel files which are saved in subfolders in a main folder. Each subfolders have atleast one excel file. I want the macro to be run with in these subfolders not the files are in main folder.
View 3 Replies View RelatedFiles Within Sub Folders
May 29, 2007I 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........................
List Folders To File
Aug 11, 2005Does anyone have a macro that would list all the folders (with path) and
subfolders to an excel sheet but not the files?
Select Folders In Excel Vba
Jun 26, 2014I want to open 2 or more folders in excel vba how its possible.
View 4 Replies View RelatedCreating Folders From Spreadsheet?
Jan 3, 2009I need some help, I know very little about macros but is it possible to create folders/directories to a certain path using the text in an excel spreadsheet cell? For example, if cell a2 has a street address and cell b2 has a city, can it automatically create a directory in the path of my choice using that data? If yes, can it go down the the rows and create directories until it is done?
Please help as I have hundreds of directories to create.
Open Workbooks In Two Folders
Jun 24, 2009I have a script below that opens all the workbooks in a folder based on a msgbox. The script works as is. However what I would like to do is remove the msgbox and have the script go back and forth between the folders.
Example it would open one workbook in the (Large Area)folder call my other script (Hazleton_Data_Conversion) Return Open a workbook in (Varsity) Folder Call the
my (Hazleton_Data_Conversion)and repeat the process till all the workbooks in both folders have been processed.
Like I said this script works with the msgbox I just like to change to without msgbox and to flip back and forth.
Multi-Level Folders In VBA?
Apr 19, 2012I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in
C: est
C: est3
It does not go deeper. i.e it wont look in folder that is for example
C: estanotherlevel
C: est3anotherlevelmylevel3
How to modify my function so that it goes to the deepest folder. is there a way to do it?
Function GetFile(directory As String, filename As String)
Dim FSO As Object
Dim fldr As Object
Dim subfldr As Object
Dim file As Object
Dim fullname As String
[code].....
Deleting Folders Within A Macro?
Aug 23, 2012So I have a macro that creates a monthly set of reports, but some of them only relate to period 5 onwards. I wanted the macro to check whether a folder exists for periods 1 to 4, and if so, delete the folder and all the files inside it. I've used the following...
Code:
If Period < 5 Then
If Dir("I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast") "" Then Kill "I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast[code]....
but the macro just skips straight through the secondary IF statements without stopping.
I know this works on deleting files, and presumed it did too for folders, but it doesn;t.
Add Folders To All Directories Within Another Directory?
Nov 29, 2012I am trying to automate some of the work I used to do as I have moved to another position, specifically trying to automate file management. I am using this code to create new files for each client
Code:
Public Sub CheckDir(fold1 As String)
If Dir(fold1, vbDirectory) = "" Then
MkDir fold1
Else
End If
End Sub
which works great, but you still have to do one client at a time. Basically I'm using this code to create new client files, but each year we will need to add a directory and sub directories for every client in a certain directory. What I would like to do is code a macro that would add say a "2013" folder with all appropriate sub directories to every client folder within the folder that contains all of the client folders The folder hierarchy should look like this
Code:
'F:Clients
'F:ClientsClientName
'F:ClientsClientName2013
'F:ClientsClientName2013RandomOtherFolders
I assume it would use a For/Each, but I'm not sure how to go about it.
VBA Saving PDF Files Into Folders
Sep 29, 2013I 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]...
Finding Files In Folders
Jun 12, 2008I 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.
Saving To/creating Folders In VBA
Jun 3, 2006I'm trying to save a spreadsheet using VBA which will also check that the directory exists and create it if not, all based on cell values in my spreadsheet. When I run my code it comes up with Run Time Error 76: Path not found.
Dim fname As String, dname As String, dname2 As String
fname = ActiveSheet.Range("E2").Value
dname = ActiveSheet.Range("G2").Value
dname2 = ActiveSheet.Range("H2").Value
Windows("Register Template.xls").Activate
ChDir ("H:")
If Len(Dir(dname2, vbDirectory)) = 0 Then
MkDir dname2
End If
ChDir dname2
ActiveWorkbook.SaveAs Filename:=fname