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
I need a macro to loop through a dir and the sub directories to find xlsm files, when it finds one open, go to the sheet named data, look at c1 and if the value is between 12.1 and 13.4 then i need it to write the file path, filename to a1 in my excell sheet and then write the value from c1 in the original file to A2 of line in my excel sheet.
I have hobbled pieces of code together without any good results.
I have been trying to process Excel files in a directory with the following
Sub FindExcelFiles()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long
foldername = "c:usersseagreendesktopTuesdayFeb102009week ending feb 7 2009 esting2"
Set FSO = CreateObject("Scripting.FilesystemObject")
Set fldr = FSO.GetFolder(foldername)
For Each file In fldr.Files
If file.Type Like "*Microsoft Office Excel*" Then
cnt = cnt + 1
End If
Application.StatusBar = "Now working on " & ActiveWorkbook.FullName
DoSomething ActiveWorkbook
Next file
Set file = Nothing
Set fldr = Nothing
Set FSO = Nothing
Range("A1").Value = cnt
End Sub
Here's the stub for the subroutine that's being called:
Sub DoSomething(inBook As Workbook) 'Massage each workbook
'Debug.Print "Hello"
Debug.Print ActiveWorkbook.FullName
End Sub
I am using Excel 2007. I found out I cannot use Application.Filesearch as Microsoft has dropped this method for 2007. My problem now is that I just see "Now working on c:usersseagreendesktopTuesdayFeb102009week ending feb 7 2009 esting2file1.xls written six times in the immediate window.
I have a workbook that is produced by some other application that names the file "FAMMToday_#.xls"
Where # increases from 1 to 2 to 3 to 4 to 5 and so forth until all the analyst are done with adjustments. so the directory can look like but doesnt always go to five and nor is it limited to five.
"FAMMToday_1.xls"
"FAMMToday_2.xls"
"FAMMToday_3.xls"
"FAMMToday_4.xls"
"FAMMToday_5.xls"
Is there a way to pull the latest file?
I 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.
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
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies View Relatedhow to list all the folders/subfolders (no files) of a specified directory?
I've seen a few examples of listing filenames from directories but I'm unsure how to just list folders/subfolders.
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 RelatedI 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...
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
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] ....
I am having a bit of trouble with a bit of code. I have a bunch of code that I want to run on all excel files that live one level down from my main directory.
I have a general directory (orders) which contains a variable amount of subfolders each month (in picture below, 'Dan', 'Frank', 'Steve'), and I would like to look through all the excel files in 'Dan', 'Frank', 'Steve', but not in the main directory (orders), or any subdirectories within 'Dan', 'Frank', 'Steve'.
I am able to loop through all the excel files in the subdirectories individually if I hardcode the directory names, but as they are variable,
Does anyone have a macro that would list all the folders (with path) and
subfolders to an excel sheet but not the files?
Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?
View 9 Replies View Relatedis 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
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?
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.
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?
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.
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.
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
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
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.
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].....
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?
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
I have been tasked with trying to find out the permissions on a set of folders on a company network. I know write vba code to manipulate a excel spreadsheet without any problems, but it is when i'm using objects that it starts to go beyond my capability. whilst searching i found this code:
Code:
Sub test1()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const FullAccessMask = 2032127, ModifyAccessMask = 1245631, WriteAccessMask = 118009
Const ROAccessMask = 1179817
[code].....
It does a perfect job but puts the data into an html file. i managed to get it to input the data into the spreadsheet as well but i would like to clean this code up so that it is not producing another file (namely the text file or the html file) and just put the results into the excel file I have entered the module into.
I did start to remove the references to any file name, but that is where i run into trouble and it wouldn't run any more due to object not found.
I'm on Win 7 using Office 2010
By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").
View 4 Replies View RelatedHow can i loop through each excel file in a directory.
View 1 Replies View Related
