Macro To List Folder Names Within A Directory
i'm looking for a macro which within a sheet named "foldernamedump" will list in a column the folder names within a directory I specify. I have seen a couple of sample codes but I just cant seem to get them working at all so I think its best to start from scratch and the work i get supplied here is always perfect. I also want the macro to clear the contents of the sheet before it loads again just to ensure there is no old data within the sheet.
View Complete Thread with Replies
Related Forum Messages:
Folder Names Instead Of File Names/macro
I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Cells(i, 1) = "No files Found"
List Names Of Workbook Files In Folder
I tried to write some VBA code to loop through all the files in a folder and return the name of the file. (In my current example, all the files are excel workbooks)
Here is the code I have used:
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Set wbCodeBook = ThisWorkbook
'Change path to suit
.LookIn = "G:CFOMiddle OfficeDannyFine Tunning for JP"
.FileType = msoFileTypeExcelWorkbooks
However, when I tried to run the code, it give me the error message saying: the defined type of the variable are not been defined. Seems to me, that VBA dont have the variable type as Folder, or File.
List Of Folder Names Missing Leading Zeros
I have sub folders with names 0001, 003A, 0032 etc in a main folder. I am using the following code to copy these sub folders name to the worksheet column A. This works well except that folder 0001 is copied as 1, 0032 as 32. How to modify the code so that 0001 is displayed in column A instaed of 1.
Private Sub Workbook_Open()
Dim fs, F, f1, fc, s, i
Range(Cells(3, 1), Cells(6500, 1)).Clear
parentfolder = ThisWorkbook.Path
Set fs = CreateObject("Scripting.FileSystemObject")
Set F = fs.GetFolder(parentfolder)
Set fc = F.SubFolders
For Each f1 In fc
Cells(3 + i, 1) = f1.Name
i = i + 1
Create Folder.... Make Directory MkDir
how can I creat a directory and then save the same file into that directory. At the moment Im using -
ThisFile = Range("T3").Value
...which works great, but before I do this, I want to create a folder with the name the name/value in T4, then ofcourse save the file into that folder ....
Macro- List Of CSV Files In A Folder Called Balances
creating a macro, I have a list of CSV files in a folder called balances, i need a macro that will look into each CSV file and grab all p'folios which have a negative balance in Col D and line them up as per example shown below. The extracted data needs to be copied to file named "Workings" S:RecsalancesWorkings
All data in CSV file looks like this, where header row is row 1 starting at A1
CSV filenames all start with ddmm so e.g. 0111
End DatePfolioA/c CodeA/c Native at End1/11/20081GRVMAAUDCUST 1/11/2008AFSBGEAUDCUST 1/11/2008AFSMBEAUDCUST 1/11/2008AFSPEEAUDCUST 1/11/2008AFSTRNAUDCUST 1/11/2008AMPFSVAUDCUST 16,565,587.811/11/2008GIORIQAUDCUST 155,642.251/11/2008GIORISAUDCUST 1/11/2008PINESIAUDCUST 5,654,632.861/11/2008WACCGIAUDCUST 7,457.561/11/2008WCTARIAUDCUST -3,923.721/11/2008WCTARWAUDCUST 1/11/2008WCTASIAUDCUST -73,456.201/11/2008WCTBGFAUDCUST 1,662.651/11/2008WCTDAIAUDCUST 1/11/2008WCTFUNAUDCUST 27,665,698.291/11/2008WCTGMIAUDCUST 31.251/11/2008WCTROWAUDCUST 6.961/11/2008WCTTHSAUDCUST 22,030.01
Macro to line up data as per below
Changing File Names In A Windows Directory
Using VBA, can anyone please advise if it is possible to change the name of a large number of files in a directory. I want to get each file name as a string, truncate the first few characters, then change the name to the new filename, all without opening any of the files whose names are to be changed.
Create A List Of Unique Names From A List Of Multiple Names
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
Macro To List File Names
I have a Macro which will list file names in column A, but it does not include the sub folders. Edit this macro to include sub folders as well.
I also want to display the file path in the column C for ech document displayed in the column A
HTML Sub ListFiles()
Dim objFSO As FileSystemObject
Dim objFol As Folder
Dim objFiles As Files
Dim objFile As File
Set objFSO = New Scripting.FileSystemObject
Set objFol = objFSO.GetFolder("c: est")
Set objFiles = objFol.Files
For Each objFile In objFiles
ActiveCell = objFile.Name
To Import All Data Files In A Directory. File Names Change Each Day
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with: .....
Macro To Convert File Names According To List
I need to rename some jpg's in the c: emp directory ( about 600 ! )
In Col A starting at A1 I have the following:-
In Col B starting at B1 I have the following:-
In c: emp is have:-
What I want to end with in C:TEMP is:-
Macro: List Worksheet Names & Count Of Data
I am desperately in need of a macro that will count the number of items in column A of each worksheet and then summarize the results next to the names of the worksheets (i.e. in two adjacent columns) on Sheet 1 of the book. Obviously, Sheet 1 needs to be ignored by the macro.
Re-Names Files In Folder
I have the below macro which had previously worked for me, but now I am getting a runtime error. I tried troubleshooting this on my own now, but am coming up with nothing.
Dim rFiles As Range, rCell As Range
Dim StrNewName As String, strOld As String
Dim strPath As String
Dim wbOpen As Workbook
Application.ScreenUpdating = False
Set rFiles = Range("A1", Range("A65536").End(xlUp))
strPath = "E:FinanceUtilization Reports2008Month End"
For Each rCell In rFiles
strOld = strPath & rCell
StrNewName = rCell(1, 2)
Set wbOpen = Workbooks.Open(strOld)
wbOpen.SaveAs strPath & StrNewName
Application.ScreenUpdating = True
Looking Names In A List With Names Written Differently And With Duplicates
I am using Excel 2003 and Windows XP.
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
Query File Names In Folder
Is there a way to write somethign that will go to a particular folder on your computer and bring back the Names of all files in that folder?
I need to build a sheet that lists certain information from each file, which is already included in the file name - trying to avoid opening hundreds of files just to get data from one cell....for example:
All files are in the following folder: ....
Search File Names In A Folder
I need help in creating a code which will search for files in a destination path and popluate a table to tell me if the file is present(Yes or No).
To explain I need a code which will find a specific path depending on the criteria and check if the files are present in those folders. These files will be named as todays date. Please find below the table format....
List All Files In Directory
I have created a spreadsheet with VBA code that lists the files in a directory. The code is as follows:
ListFiles = True
Set Sh = ThisWorkbook.Worksheets(sFileSheet)
lstAttr = vbNormal + vbReadOnly + vbHidden
lstAttr = lstAttr + vbSystem + vbDirectory
lstAttr = lstAttr + vbArchive
If sDirectory = "" Then
If iColumn = 1 Then
sPath = "C:PensionDataPensionPath" ' Set the path, must end with backslash
sPath = "C:PensionDataWorkBench" ' Set the path, must end with backslash
If Right(sDirectory, 1) <> "" Then
sPath = sDirectory & ""
sPath = sDirectory
sFileName = Dir(sPath, lstAttr) ' Retrieve the first entry.
lDataRow = 1
lRows = 1
This works on my PC and it works on other PCs. However, the one PC that it should and must work on, it does not. There is no file listing created.
I have duplicated the files and the folders on my PC and the files in the directories are listed. However, on that one PC, there is no file listing.
Highest Number From File Names In Folder
I need a macro which checks for next available number from list of filenames in a folder. Then it returns that value to one predefined cell in a new document (here Untitled.xls). For example:
has the following files:
Untitled.xls - read only
Next available filename should be "abc-09-005" and macro returns that value to predefined cell in a new document (Untitled.xls). There should be NO saving at this point whatsoever. Macro simply picks the next value from a filelist. As you've noticed the value has some constants too. New document doesn't know the last picked value before macro is active (predefined cell should be empty at startup).
Replace Bad Names From A List Of Good Names
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?
The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.
Create List Of Files In A Directory
Is it possible to create a VBA script that will list the filenames of files in a set directory? I have about 100 directories, each containing 1000 files and they need to go into an excel spreadsheet (each file on a new row)so they can be audited... ;(
List All Files In Directory In An Excel File
In my search I found several example similar to what I need, but nothing I could adapt to filt my needs, (at least not wim my limited skills).
Here what I need to do:
I have multilple Excel files in a directory. (M:/Archived PO Responses/Domestic). On a daily basis, these files are processed via VBA, and deleted after processing. What I need to do is, prior to processing and deleting these files, create an ongoing log of the filenames in that directory.
The macro would open an Excel file named "Processed Orders.xls", which is stored on the network drive "M:". It would then append all of the filenames in the directory mentioned above to a sheet named "Processed Orders" in that workbook, below any filenames that already exist.
List Directory Files And Make Hyperlinks To Them
I would like to list all files in a directory (display the full path) and make hyperlinks to all listed files at the same time.
I came accross the code below which works well for files in a directory:
' puts hyperlinks to each of the files in a directory of your choice
' into the active sheet starting at the active cell
Dim stDir As String
Dim stFile As String
Dim R As Range
Set R = ActiveCell
stDir = InputBox("Directory?", , Default:=CurDir())
stFile = Dir(stDir & "*.*")
Do Until stFile = ""
R.Hyperlinks.Add R, stDir & "" & stFile, , , stFile
Set R = R.Offset(1)
stFile = Dir()
R.CurrentRegion.Sort key1:=R, order1:=xlAscending, header:=xlNo
List Files In Directory With Date Modified
I currently have a macro working where I can populate the A Column with files from a directory and based on its file extension, however I am having problems trying to populate the second (B) column with that files date modified. I have searched the forum to no avail and I must admit I not the best with vba.
' searches within the file location
' C:Arenium_Projects31-1853012_Barnawartha BOSCADDMX
Dim rngOut As Range
Dim strPath As String
Dim strfile As String
Looping Through Directory List To Perform Functions On Each File
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.
Open File Macro: Same Directory As Macro Workbook
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
Copying A List Of Data To List Of Range Names
I do not have any code for this as I am unsure if it is possible. I have a list of range names in a column (A) that refer to different sheets and in an adjacent column (B) I have a list of data. Is it possible to write VBA code that will allow me to copy each value in column B to the corresponding cell in the workbook that relates to the range name in column A.
List Names, InputBox, Check If Name Is On The List
I have an excel sheet with 30 names in column A and I'm asked to do the following:
Write a procedure – a sub – that uses an input box to ask for a name. The procedure should then scan through the names in the list to search for the name and make a message box to state “X is not in the group” or “X is in the group”, where X is the name from the input box. HINT: You can make a variable “found” that starts with the value 0 and gets the value 1 if the name is found in the list.
Run Macro On All Files In A Directory
The title pretty much sums it up, actually. I have a macro, and a beautiful piece of work it is. It selects a particular selection of the active workbook, parses it in a particularly thrilling way, and writes the result to a text file. I'd like it to do this for ALL the files in a particular directory (somewhere over 500 files, if that matters).
Opening A Directory Via A Macro Button
I would like to make a button that, when clicked, opens the following directory in a seperate window: "C:Documents and SettingssharkfootDesktopTNT" Here is what I tried...
ActiveWorkbook.FollowHyperlink Address:="C:Documents and SettingssharkfootDesktopTNT"
Because I am still a novice and because i wrote this myself, it doesn't work.
Hyperlink List Of All Files In Folder
I have a question about creating a custom drop down menu. In AutoCAD, I can create custom drop down menus that loads each time AutoCAD loads and i was wondering if excel have something similar. This menu would be added to the default menu list: "File_Edit_View_Insert_Format_Tools_Data...etc". I have searched online but i keep getting things about how to make drop down menus in cells but nothing about adding a main drop down menu.
What I want to do is create a menu with hyper links to all of the excel files we use quite often here at work.
I have already done this by just creating a sheet with a list of hyper links on it but would like to put it in a drop down menu instead if its possible.
List Cells From Other Workbooks In Folder
I am having a bit of trouble with something i am trying to do in my Excel workbook (which we will call the home workbook). Basically what the home workbook does is opens a number of different Excel workbooks that are located in a certain directory. It then finds the first blank cell in on the worksheet "Data Import" ...the cell adress is stored in a variable called found. I need to utilize the values that are in certain cells of the workbooks that the home workbook is opening, but I keep getting a 'subscript out of range' error. I am almost positive it has something to do with accessing the other workbooks. Here is the code I've come up with (well parts of it that are relevant):
Dim found As Range
Dim strFile As String
Dim strPath As String
strPath = "C:directoryPath" 'amend directory as appropriate
strFile = Dir(strPath & "*.xls") ' amend extension as appropriate
Do While strFile <> ""
List Of Links To All Files In Folder
We use Excel 2003 and 2007 to track labor data for a field service company. The field tech's complete daily forms and the office staff copy/paste this data into weekly billing worksheets. They use naming conventions to show the jobs and dates in the file name (Daily Labor - Job1 - 031308.xls and Weekly Labor - week ending 3-16.xls. What I would like to do is link the daily data to the weekly forms as they come in. Is there some way to do this when the job names and the dates are always different? Can I tell Excel to expect data files based on the naming conventions as in the Weekly is named "Weekly Labor - Week Ending 3-16.xls so data files named LaborJob1310.csv, laborjob1311.csv would automatically update the weekly sheet? I can put these in a folder and link them, but I don't know how to provide an easy way to do this for the upcoming week.
List Same Cell From Workbooks In Folder
I have two hundred workbooks in a folder located at "C:ozgrid". I'm trying to extract the information in cell B9 in each of these workbooks and input them into a new workbook starting in cell A1 and continue on down through cell A200. Each workbook name begins with "Summary" and has the format of "Summary XXXXXX.xls".