Scan Through All Files And Subdirectories
I am trying to scan through all files within a directory and all subdirectories to find files where the name begins with certain characters. Below is code that will accomplish this for a certain directory, but I do not know how to look through the subdirectories within the specified directory. So, the issues are:
1. How do I loop through each subdirectory within a specified directory?
2. I am trying to return the file name, path name (separate from the file name), and modified date. I can return the name. I can return the path, but it includes the name. Is there a way to return just the path?
3. What is the code to return the modified date of the file?
View Complete Thread with Replies
Related Forum Messages:
Loop Through All Files In Subdirectories Of A Directory
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,
Scan Through Multiple Files
I send out 300 or so spreadsheets. The spreadsheet contains lines of products that recipients have on hire. Column L allows to the user to put YES or NO on each line, to state whether it is correct or not. The user then sends this sheet back.
The difficult part for me is I have to open each spreadsheet, and deal with all the instances of NO in column L. It takes almost a day to go through every spreadsheet.
Is it at all possible to have some sort of macro, VBA sript or access front page, that allows me to search all instances of 'NO' in all the spreadsheets which are in 1 designated folder?
Hopefully I am making sense in what Im after.. ideally I'd need a button on a spreadsheet that I click to give me all rows that contain NO in column L.
All the file names will be constant each week. (so each file name will be 01 - Hire List.xls, 02-Hire List.xls, 03-Hire List.xls etc etc).
Scan Worksheets In Reverse Order?
I have some code that scans each worksheet in a workbook for sheetnames beginning with 'Model'... if the worksheet name matches, it prints only the used pages on that sheet (see code below).
Dim Ws As Worksheet
For Each Ws In Worksheets
If Left(Ws.Name, 5) = "Model" Then
If Ws.Range("A46").Value = "Page 1-1" Then
Ws.PrintOut From:=1, To:=1
ElseIf Ws.Range("A46").Value = "Page 1-2" Then
Ws.PrintOut From:=1, To:=2
This code works... but I would like it to scan the workbook in reverse order...
ie. print from the last matching worksheet first.
Scan The Rows And Find The Last Remaining Row
I can't figure out the specific way to write a Do Until where it exits once there is no more text in any further rows.
Basically scan the rows and find the last remaining row. If there is no data after 20 rows then exit the loop.
This is a sample.
I would want it to understand that there is no information after "J" and to exit the loop.
Drop To The Next Row Each Time I Scan In Each File
i've created a spreadsheet that will take in scanned files (from a barcode), log into our system, update the files based on certain criteria, then take information from the file and place it in another worksheet for inventory purposes.
what i'm asking is... is there a way i can have excel drop to the next row each time i scan in each file?
i've noticed the output of the scanner is formatted like this: +000000
i've tried using the sendkeys method with the plus sign, but it always moves down THEN enters in the numbers. i'm not sure if there is a way with the Len function... or some variation of that.
i'm also thinking if i could maybe find the .dll for the scanner, maybe i could set up a reference to it, and have it do something like "afterbarcodescan.activecell.offset(1,0).activate".
SCAN(A1:P25) To Find A Cell Containing Certain Text
The issue is to find the address (like A1) to the cell that contain specific text string?
I import text from a web page and I want to calculate some numbers from this information. All the information is spread out in 10-15 columns and 20-25 rows, and the specific value I'm looking for is imported into different rows as the dynamic web page changes, but the column is always the same.
The cell next to the left side of my wanted cell always contain the same text, so I thought I could search or scan the area A1 to P25 for this text and then extract the value of the cell on its right hand.
Add Another Worksheet & Continue Importing The Html Files Until There Are No More Files To Import
I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
Transfering Files From Lots Of Files To One Single Exel Master File.
Need a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
Search CSV Files For Word & Make .xls Files Of Them
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.
how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)
Saving Comma Separated .txt Files As .csv Files
I have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.
Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).
Open Files In Folder- Wait 30 Seconds Then Close The Files
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
Files Saved To Program Files Folder, Open As Read Only
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
Convert Csv Files To Tab Delimited Files
I would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user
Save Ranges In Multiple Files To Text Files
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
123 Somewhere St.
Here, OH 45111
345 Anywhere St.
There, OH 45211
Convert Multiple Files To Text Files
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
Search Folder For Files Containing "Temp", Delete Files
Is there a code that will search a pre-determined folder for all .xls files containing the word "Temp", and deleting those files?
In the root of drive M, I have several Excel files. Anytime one of the original tracking logs is opened, a temp file of that log is automatically created. I would like to automatically search the drive and delete all files containin the word "Temp".
I've attached a picture of the directory tree, in case that will help. The file will always be named "PO Response Tracking - Temp#######.xls", with ###### representing a series of either 5 or 6 numbers.
I have a scenario here and i really dont even know where to start. If anyone can help me with some code for this project that would be great.
I have a folder that contains several subfolders (10-20). Within each subfolder is a number of excel files (the number of files varies) that contain 2 worksheets each.
What i am trying to arrive at is one file for each subfolder that has the first tab of each of the files. The original files contain links, so the values and formats must be pasted. It woul dbe great if all of the new files could be saved in the main folder among all of the subfolders.
It would be especially great if each tab could be named for its respective file and each new file for its respective folder.
Compare The 2 Files
I have 2 xls file what I want to do to compare the 2 files see attachment
On sheet 2 is the main xls ... what I want to compare sheet 2 to sheet 1 and if it's on sheet 2 and sheet 1 if will post the result to sheet 3 pulling all the data to those colum ie
Employee IdInstrument IdQuantity Br Account
95086 Dec 300 511 2046
what has to match is sheet 2 Emp# and Qty to sheet 1 Employee Id
Files Within Sub Folders
I 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.
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........................
If I have a normal Excel file and want to change it to a CSV file compatable with OziExplorer and MapSource mapping software, how can I convert it to a suitable CSV file?
Merge Many Files Into One
1. I have a master file (master.xls), currently empty
2. I have 80 excels files (all look a like but their worksheet name are different)
3. I would like all the data from these worksheet to be appended to master.xls with a click of button.
4. I tried using some codes from this forum but they did not work.
5. All the source file starts with ums bla bla.xls
Renaming Files ...
I have a folder containing pictures.
I have an excel sheet containing data.
The pictures' filenames are #s which are located in Column A of the spreadsheet. I would like to be able to have excel take the picture name, lookup which Row it is and then add the information from Column B, C, D and E into the filename. It would need to do it for all the pictures located in the folder.
I need to find the .xls files inside a folder. I am just stuck in the midway. My code is:
Dim fil As Object, fld As Object, fso As Object
Dim fldpath As String
Dim count As Integer
fldpath = "H:DesktopTestMacro TestTest Folder"
Set fso = CreateObject("Scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)
count = 0
For Each fil In fld.Files
If Files.Name = "*.xls" Then
count = count + 1
MsgBox "Number of files is " & count
I am stuck in the text in RED. Please let me know how to get this done.
Also, I need the full filenames of all the file types in that particular folder in a new worksheet.
Merging Two Files
merging two Excel files. 2009 Item List.xls file has two columns Part number and Description. Price.xls file has two colums: part number and price. I need to merge them into a file that whould have three colums: part number, description, price. There 6712 rows in 2009 Item List.xls and 4743 rows in Price.xls file. So I need them matched based on the part number, and have the cells blank for the part numbers for which the price was not assigned.
Compare Two Files With The Same ID
I have one xls sheet with employee’s information, at column A I have ID number -called Mater. I have second xls sheet with update employee’s information, at column A i have the same ID number - called update file. I need help with VBA code that update the Master file according to the update file (The ID at column A is the key between the files)and mark with color the update. The file contain more the 20,000 records and with 10 columns.
Number Of Files
How can I find out the number of files in a folder when coding in VBA? I need to use the number of files that I have as the bound on a loop in the code, and the number of files in the folder can vary so I can't just count them.
For the first time we are going to try and give out CD's with half a dozen Excel files that are used for illustration purposes. With data entry and presentation graphs you can print off. The problem is, we want to protect this as much as possible. NOTE - the users will be financial advisors and not computer savvy for the most part so ignore the fact that most of you could get around any walls I could put up.
So far I am just going thru and protecting the sheets and workbooks to the extent we want them protected. Being that these are just excel files, is there anyway to have an expire feature? I would even consider buying a software to be an umbrella for these files if needed.
Merge Files ...
I have two files that need to be merged together (file1 & file2). These two files have three columns each. One column on both files is the same. This column is labled "phone number". One file has more lines the other has less.
I would like to make a macro that will check file1 against file2. If it finds a phone number the same it will cut the whole line and append staring in column 4 to same line in file1.
I want to search a particular folder & delete certain files. I'm using the code below.
'The folder where my files are located
.LookIn = "C:Documents and SettingscDesktop"
.SearchSubFolders = False
'Search text in any type of files
.Filename = "*.htm"
Open Different Files With 1 Button
I would like to create a button that will open a file in a location based on the date of the file. So if I click the button the file with the latest date is activated as read only. The file name is always the same except, the date is added to the file name each day.
Worksheet From Text Files
Hi all, first up I'm a total novice but I'm trying to populate a worksheet from a text file, but here's the kicker, I'd need specific lines of the text file to populate specific columns.
None of the text files will contain the same data, but, they will all have the same related data on the same lines. So I'd like to see:
.txt file 1 line 1 > Worksheet A:1
.txt file 1 line 6 > Worksheet B:1
.txt file 1 line 12 > Worksheet C:1
.txt file 2 Line 1 > Worksheet A:2
.txt file 2 Line 6 > Worksheet B:2
Everything needs to go into the same worksheet. Some .txt files won't have any data in the specified lines so skipped if blank, or just blank data copied.
I need to run through about 1300 .txt files in a directory in this fashion, can it be done?
Merge Two Data Files
Right now I need help mergng 2 files:
#1 One is a txt file with emails
#2 the other is a .csv excel file which contains several fields: email, name, address, etc
Basically what I need to do is to create another .csv excel file that contains all the emails in file#1 that are also present in the file #2 plus their corresponding additional fields ( name, address ) etc
Combine Xls Files
My question is do u know any sort of program that can combine 100 xls Files (all has only one sheet ) to only one huge file in just one sheet !
and yes all the sheets having same no of columns and rows of data !!
Import Latest 6 Files
I would like to create a macro that looks in a specific directory "h:data"
It finds the 6 most recently modified files in this directory (csv files)
Then imports these 6 files into seperate worksheets in the same workbook "sorter.xls"
Aggregate Text Files
To go through all the database files (.txt format) in a designated folder in order to update an Excel Sheet with the required information from those database text files. Then to summarise the information in a simple table.
The text files are individual Market Exports from a game I play, and are titled as "Region-ItemType-YYYY.MM.DD HHMMSS.txt"
eg. "Heimatar-Tritanium-2009.07.02 133353.txt".
I need to know how to sift through the text files to find the most recent one with matching "Region-ItemType" parts in the file name, and refresh the Excel Sheet with the updated data.
After that, I need to be able to combine all of the cheapest sale prices for each item, with a minimum amount for sale (differs per item), while also being limited to location the item is being sold at, into a single sheet of the important information I wanted to extract in the first place.
At the end of the whole process, I need to have a single Excel Sheet with every different item's lowest price for each different place, displayed in a table I can use to make formulas from.
So if I have an export from a place called "place1" and one from "place2", looking at the same item called "item1", I should have two rows of tabulated data, one for each place's lowest price. And this should update at the touch of the "Refresh All" button, or... However you'd have to do it
I have attached a copy of one of the database text files.
Winrar Files In VBA
I have the following code which works. The problem is when it zips the file it zips up all the folder locations where the zipped file is saved.
For example this code zips the file and all the folders where the file is saved, so it zips the file in the following folders.
Documents and SettingsRichard.DruryDesktop
I dont want this to happen I just want to ZIP the file.
FileSearch Never Finds My Files
I'm trying to get a count of the number of workbooks in a directory and it keeps returning 0 when there are three WBs in the directory. What am I doing wrong?
Here is my code.
.LookIn = "C:Documents and Settingsdt64864DesktopTesting"
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
Printing Of PDF-files
Is it possible to print a pdf file from excel ?
I am making a list with pdf files that are on my hdd. I want to be able to select them and then send them to the printer.
(Yes I know it can be easily done from within the windows explorer by selecting and right-clicking, but my excel-file has to be able to do more than that )
Opening Files Using Vba
i wish my vba program to enable the user to select a file to open from the regular 'open file' dialog box.
in addition, I wish to be able to list the filenames in a user form that have already been saved in a specific directory.
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
Printing Pdf Files
We have a set of documents, all pdf files, residing in a network server. The shipping personnel will have a Laserjet 8150 ND (also on the network) to print the document they need at the time of shipment, for a specific product (the pdf files are actually User's Manuals for various products we sell).
We would like to create an Excel form, listing those documents, with a selection method to indicate which document is to be printed and how many copies. Then clicking on a "PRINT" button will send a command to the printer to print the desired manual.
I searched the forum threads and noticed that the subject mostly deals with printing TO pdf, whereas my need is to print pdf FROM Excel, but no such a command exists.