Looping File Movement With Multiple Folders
May 15, 2014
I have the following code that transfers all the Excel files in Folder 1 to Folder 2:
Code:
Sub Move_NRAuto()
Dim fso As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String
[Code]...
I'm trying to amend this code so that instead of hard coding "Folder1" into the code, I can loop this process through a number of folders (Folder 1, Folder 3, Folder 4, etc) and move all of the files in each of those folders into "Folder2".
Is this possible?
View 4 Replies
ADVERTISEMENT
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
Oct 27, 2009
I have a series of files in *.ew7 format (i know its a random program)
I have some code which i can run which will simply open the relevant file and save it as xls.
The files are saved in multiple folders in format "yymm" eg: 0910 - oct 09.
inside each folder there is a ew7 file for each day in rthe format: "01ddmmyy" eg 01271009 = 27th oct 2009.
I have folders going back as far as may 2002 (in folder 0205) and i need a way to convert these all into xls format and save them in the same location.
View 13 Replies
View Related
Oct 11, 2013
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code:
Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
View 3 Replies
View Related
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
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
Mar 27, 2008
Is there a way to make it so when i hit enter my cursor moves 7 cells instead of 1?
View 9 Replies
View Related
Aug 11, 2005
Does anyone have a macro that would list all the folders (with path) and
subfolders to an excel sheet but not the files?
View 14 Replies
View Related
Jun 5, 2009
is it possible to type a document name in a cell,then run a code that searches a root dir and sub folders, find the existing document and open it?? for example
cell: "D20"
filename: DYAZES-001
root dir: "I:IsolationDataBaseIsolationProcedures
subfolders: DryMillA, DryMillB, Despatch, Maintenance
View 5 Replies
View Related
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
Nov 13, 2012
I'm actually using a macro that saves all the sheets from a workbook in new separate files, and save them in a specified folder.
Code:
Sub Estrazione_Schede()
Dim n As Long
Dim myNome As String
Dim myPath As String
myPath = "D:path"
[Code] ......
What I'm trying to do is that, instead of saving all the sheets in one folder, the macro would save them in different folders, depending on specific values in a specific cell of each sheet. I tried to modify the macro like this, but it doesn't seem to work properly:
Code:
Dim n As Long
Dim myNome As String
Dim myPathOne As String
Dim myPathTwo As String
myPathOne = "D:pathOne"
[Code] .....
With this code the macro ends up to check the condition only in the first sheet and then it copies all the sheet in the same folder.
View 2 Replies
View Related
Jun 28, 2014
Code that loops through all folders in a directory, to check each workbook in each of those folders. If any workbook name in any of these folders matches a predefined string, then open this workbook for some other codes to run.
View 2 Replies
View Related
Jul 8, 2013
macro 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.
View 4 Replies
View Related
May 21, 2014
I 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
View 4 Replies
View Related
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
Dec 5, 2011
I've made a macro that would create some folders on a shared drive.
there is an issue if the path i want to create is too long.
I get this error.
Uploaded with ImageShack.us
is there anything we can do to pass this or any solution for it?
View 5 Replies
View Related
Mar 8, 2007
I have a VB codes to process the data from a file that located in the folder from directory. This VB will copy the data from the file and paste it into new workbook by clicking the command button. I want to modify this code in order to make VB can process the data from multiple file from multiple folders. Previously, I just write the code for one folder only. So now, I want to modifiy the codes in order to expand more folders. All the results will be pasted into the same workbook.
Private Sub cmdStart_Click()
Application. ScreenUpdating = False
Set outbk = Workbooks.Add
Workbooks.Open "C:" & TextBox1.Value & "" & TextBox2.Value
Sheets("bin_trip").Select
With ActiveSheet
View 9 Replies
View Related
Mar 5, 2013
How can i loop through each excel file in a directory.
View 1 Replies
View Related
Mar 31, 2014
I 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
View 3 Replies
View Related
Feb 5, 2010
I was searching through the internet to look for some help with regards to consolidating data from multiple workbooks from different directories into a single worksheet called Summary.
I would like to copy the entire worksheet from different workbooks in different directories into this workbook I like to call Summary. To manually do it I have to open the workbook I want to copy the worksheet from. Right click on the tab and select Move or Copy, select the sheet I want to copy and select the workbook I want to copy this sheet to. Repeat this process for all the workbooks.
The next step is to copy and paste from these sheets into a main summary sheet. The data range from these sheets are starting from row 13 onwards and they may be of different size between Column A to Column W.
For example the workbooks are coming from :
S:Area 1West.xls from worksheet Andy
S:Area 1East.xls from worksheet Bill
S:Area 1North.xls from worksheet John
S:Area 1North.xls from worksheet Albert etc...
View 9 Replies
View Related
Sep 15, 2008
I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.
the folders are set up as follows,
there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.
for example a1 - k1
the code i am using transfers the file names but comes up with #REF! instead of transfering the data
Here is the
View 9 Replies
View Related
Feb 4, 2014
I have a database that needs breaking down in order to fulfill a request.
I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.
Code:
Dim row As Long
Dim refname
row = 3
Do While Cells(row).Value ""
[Code] .....
View 3 Replies
View Related
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
Dec 3, 2009
I have a directory with over 100 .txt files. I want to open each file, parse the data and copy the data into a new file which has numerous formulas and functions to perform on the data. I have the code for doing all the work, but I have to open each file individually and run the macro. I would like to loop through all the files in the directory to perform the same fuctions. I need the looping code for getting the files from the directory.
View 4 Replies
View Related
Jun 13, 2014
I would like to create a change event macro that will update multiple formulas that pull data from various tabs on a file. The tabs are named with a single date. So ideally when a certain master cell (Allocation!H2) is updated to the current date the macro will be prompted to update the range that contains formulas and update accordingly...so its and index match fomula based that has a variable being the tab name which is a date. I have a range of dates in cells F4:AB4 and want the formula to go into F5:AB5, I can then fill that down to whatever row I want...that bit I can handle.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Worksheets("Allocation").Range("H2")
[Code]......
View 4 Replies
View Related
Sep 18, 2009
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)
View 3 Replies
View Related
Jul 27, 2007
I'm using Excel 2003:
I have agents that want to "own" records depending on the zip code. I want to dynamically assign agent to a record based on the zip code. If there was only one agent per zip code, this could easily be done with Lookup. However, in many instances there will be multiple agents for a zip code. In these cases, I was hoping to assign the agents to each record in a round robin fashion.
My agent/zip table would may look something like this:
37011 Smith
37013 Jones
37023 Herrman
37025 Martin
37025 Slater
37025 ORiley
37028 Phelps
37028 Trenton
37029 Reagan
I would then have another sheet made up of home addresses that each have a zip-code. If the record contains zip 37013, I can easily assign that one to Jones. However, if the zip in the record is 37025, there are three agents sharing that zip. So for the first record with 37025, I would assign Martin. The next occurence of 37025 would be Slater and the third would be ORiley. The fourth occurence would go back to Martin and so on.
I have found some versions of what I want to do on this site and others but can't find the looping capability.
View 9 Replies
View Related
Dec 8, 2009
I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.
Here is where I'm starting out.
View 9 Replies
View Related
Oct 7, 2011
how to copy cells from different columns to another workbook and to do this for several lines? The macro stops at the blue line
For i = 8 To WS2.Cells(Rows.Count, 3).End(xlUp).Row
If MySheet.Name = WS2.Cells(i, 3).Value Then
WS2(i, 4).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Destination = WB1.Worksheets(table(z)).Range("G10")
View 1 Replies
View Related
Aug 17, 2006
I would like to write a macro that would accomplish the following:
Check (b2..b65k) <> 6710 or 6720
ActiveCell "6400"
Loop
In words. I would like to check for each type account numbers in the cell to the right, if neither occur, then in the activecell write 6400.
View 4 Replies
View Related
