Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

Sponsored Links:

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.
_____________________________________

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub

View Replies!   View Related
List File Names From Folder
Need code to open a browser to select a folder and list the files in that folder in excel. I do not want to include sub directories.

View Replies!   View Related
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:

Sub Load_List()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'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.

View Replies!   View Related
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()
Range("b3:b6500").Clear
Range("c3:c6500").Clear
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
Next
End Sub

View Replies!   View Related
Write Folder & File Name Of All Files In Directory
I need some VBA code to cycle through each and every folder and file in a given root directory and write the file name string to a cell. (does not need to look at the content of sub-folders only the names of all objects sitting directly under the given root.)

View Replies!   View Related
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
ActiveWorkbook.SaveAs FileName:=ThisFile

...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 ....

View Replies!   View Related
Automatically Remove Folder From Directory Path
i am wanting to automaticaly remove a file from a directory when this directory is populated and move to another directory that is secure, how would i do this? just say directory 1(where it will be removed from) is s:/ddc/reports

moved too-- s:/ddc/test

test folder will be secure

View Replies!   View Related
Create Directory Or Folder Using Date In Cell
How can you add the date from a sheet say A1 which is a long date and make a directory which the date incorporated into the new directory name?

View Replies!   View Related
Directory Names On Different Computers
I have been limited lately to only working on this project at work because I have added a few lines here and there throughout the workbook that make a reference to the directory the file is located in. for example:

View Replies!   View Related
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 data

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

P'folioDateWCTARIWCTASIWSTFDGWTDTADWTFFI3WTGTFAWTRLGD1/11/2008-3,923.72-73,456.20-4.18-136,496.19-562,967.29-2,319.40-15.762/11/2008-3,923.72-73,456.20-4.18-136,496.19-562,967.29

View Replies!   View Related
Macro Code To List Files In Specific Folder
I am unable to install a disk catalog and have been trying with no success to try and get something in excel that will look at a directory and display the contents of that folder with the file attributes etc

View Replies!   View Related
Copy File Names ,date From A Directory
I need to copy the names and date modified from all pdf files in a open directory. The directory name will change month to month. I will copy the names into an excel workbook that will be opened as well called Shipped starting in sheet1 cell P2


View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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
Range("A2:A300").Clear
Set objFSO = New Scripting.FileSystemObject
Set objFol = objFSO.GetFolder("c: est")
Set objFiles = objFol.Files
Range("A2").Select
For Each objFile In objFiles
ActiveCell = objFile.Name
ActiveCell.Offset(1, 0).Select
DoEvents..................................

View Replies!   View Related
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: .....

View Replies!   View Related
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:-
DFCF0156
DFCF0157
DFCF0159
DFCF0161
DFCF0164
etc

In Col B starting at B1 I have the following:-
ID3425
ID5823
ID427
ID7272
ID8875
etc

In c: emp is have:-

DFCF0156.JPG
DFCF0157.JPG
DFCF0159.JPG
DFCF0161.JPG
DFCF0164.JPG
etc

What I want to end with in C:TEMP is:-

ID3425.JPG
ID5823.JPG
ID427.JPG
ID7272.JPG
ID8875.JPG

View Replies!   View Related
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.

View Replies!   View Related
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.

Sub ReName_Files()
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
wbOpen.Close
Next rCell
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
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

=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)

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?

View Replies!   View Related
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: ....

View Replies!   View Related
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....

View Replies!   View Related
List Of .xls Files In Directory
Hey is there any way to get a list of filenames into VBA from a specific directory?
for example all files in directory c:my documentsexcel?

View Replies!   View Related
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
Else
sPath = "C:PensionDataWorkBench" ' Set the path, must end with backslash
End If
Else
If Right(sDirectory, 1) <> "" Then
sPath = sDirectory & ""
Else
sPath = sDirectory
End If
End If

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.

View Replies!   View Related
List Of Files In A Directory
Is it possible to get a list of files in a certain directory as a list stored in a spreadsheet?

View Replies!   View Related
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:

c: est

has the following files:
Untitled.xls - read only
abc-09-001.xls
abc-09-002.xls
abc-09-003.xls
abc-09-004.xls

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).

View Replies!   View Related
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.

View Replies!   View Related
List Folders & Subfolders From Directory
how 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.

View Replies!   View Related
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... ;(

View Replies!   View Related
Worksheet Names :: Loop Through All The Workbooks In A Network Folder
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).

View Replies!   View Related
Macro To Locate Folder In A Folder And Save
looking for some code to save to a destination

destination address is
C:Documents and SettingsstDesktopOJF

now the problem is OJF has folders named 1 to 500

so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.

View Replies!   View Related
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.

Example:

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.

View Replies!   View Related
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:

Sub HyperlinksToDirectory()
' 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()
Loop
R.CurrentRegion.Sort key1:=R, order1:=xlAscending, header:=xlNo
End Sub

View Replies!   View Related
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.

Sub INP_files()
' searches within the file location
' C:Arenium_Projects31-1853012_Barnawartha BOSCADDMX
Dim rngOut As Range
Dim strPath As String
Dim strfile As String
' Sheets("INI_FILES").Select

View Replies!   View Related
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.

View Replies!   View Related
Copy File Names From A Directory Into An File
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 Replies!   View Related
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"

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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).

View Replies!   View Related
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...

Sub OpenLocker()
ActiveWorkbook.FollowHyperlink Address:="C:Documents and SettingssharkfootDesktopTNT"
End Sub

Because I am still a novice and because i wrote this myself, it doesn't work.

View Replies!   View Related
Auto Email Directory From VBA Macro
I got a task from my lab to write a macro in excel to "automatically email the detailes of the file listing from c: dir "

View Replies!   View Related
List Files In Folder
Is there a way list load all the files names in a folder in to a listbox in a userform

View Replies!   View Related
List All Files In Folder
I have a folder C:My Documents that contains excel & Pdf files. Is it possible to list all the files by name contained in this folder in a excel spreadsheet i.e one filename one cell?

View Replies!   View Related
List All Files In A Folder
I want to run a macro to list the names of all files in a folder in a worksheet. I want the file names along with extensions.

View Replies!   View Related
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.

View Replies!   View Related
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 <> ""

View Replies!   View Related
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.

View Replies!   View Related
List Files In A Folder In A Worksheet
I have a folder containing many Excel files. Each is unique, and I wish to characterise them in a separate contents file in Excel. To do so, it would be very useful to populate a worksheet with the names of the files in the folder.

View Replies!   View Related
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".

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved