Renaming Worksheets In Files In A Directory

Jun 18, 2008

Hope i can get some help here as my vba experience is extremely limited. I'm trying to run a macro from a spreadsheet that will go down a list of file names that i have entered in a worksheet where the macro resides and open those spreadsheets and rename the worksheets in each file according to a list of names that i have entered in the 10 columns next to the file name. It's easier to explain with the layout of my macro spreadsheet: ....

View 9 Replies


List All Files From Directory And Sub Directory?

Sep 5, 2013

The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.

Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|


View 8 Replies View Related

Renaming Files Using VBA

Sep 15, 2009

I have forty files in a folder that are generated by a system every day.
The files all have something in common, they all include "V9TEST" in them.

for example:

I would like to run a script that would rename these files in the folder, taking out the V9test part.


View 3 Replies View Related

Renaming Files ...

Feb 16, 2010

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.

View 14 Replies View Related

Renaming Multiple PDF Files?

Apr 18, 2014

I need to rename a large number of .pdf files. I have a list of the current file names in column A and the desired file names in column B.

Data in excel sheet1:

Current NameDesired Name

I keep them in the destination folder below:


I prefer to use an excel macro since I can't install any additional software on my work computer.

View 3 Replies View Related

Renaming All CSV Files In A Folder

May 14, 2007

I have several (around 35) CSV files that I download regulary. Is there a quick way to rename all the CSV files in a given folder based on adding "Update_" to each file name.

E.g Say two of the orginal files are called:


I'd like to run some kind of macro to rename them to:


View 8 Replies View Related

Renaming Files In A Folder

Sep 28, 2007

Every week I get a folder full of files and I have to rename them with a week ending date...Below is what I hacked together, but it's not working...

Sub Rename()

Dim OldName, NewName, npath As String
weekendingdate = InputBox("What is W/E Date??")
npath = "J:Test*.pdf"
While Smith ""
OldName = Smith: NewName = Smith & weekendingdate
Name OldName As NewName

End Sub

View 9 Replies View Related

Renaming Files From A Spreadsheet

Dec 1, 2007

to take a spreadsheet in excel and rename files with it. I will get all the columns together and

named properly by using the concatenate function. So when i go to rename i would have a

list of say 5,000 things to rename. In column A will be the full path. In column B will be the

new name i want it to replace the old one with extension and all. The reason i want to do this

is because that way i can work with the 5,000 files column A, B, C, D, get everything the

way i want it and then concatenate them. Then rename the files after copying and repasting

them so there is no formula. This would just be much easier than having to go to each

individual file and rename. also a lot easier to be able to compare and see what is going on

in spreadsheet i can sort compare. just a lot easier. i have messed around with excel some

but not macros much. what i would like to do is for a macro to look in A1 for path then

rename with B1. then A2 for path then rename with B2. loop through all rows until there is

nothing in A? that way there does not have to be a certain number of files. i do not know if

C: estSGB04SGB04-08 - Frank Sinatra - Wives And

rename to B1

SGB04-08 - Sinatra, Frank - Wives And

running excel 2000 windows xp

View 9 Replies View Related

VBA - Renaming Files In A Folder And Then Combining Them

Feb 21, 2014

I have a system that creates text files once a day in a set folder. I want to have a VBA script that will combine all text files in the folder (C:Daily Folder) into one text file (Full.txt) using a simple cut/paste addition, i.e., the lines from the second file are added after the lines from the first, the lines of the third after those, etc. The order does not matter.

I know there are other threads about combining text files and I'm sure I can figure that out if need be, but the problem is the system creates these files by adding .XXX to the name, with XXX being the file order number. So after 4 days, I have four files named K2500.001, K2500.002, K2500.003, and K2500.004. Windows reads these as being four different file types (.001, .002, .003, and .004). If you click on one of the files, Windows prompts you to associate it with an application. I've tried shell commands such as ren *.* *.txt , but the use of a "." in the file name seems to be a problem. Is there a way to change all "." to "-"? I've tried some PowerShell scripts there that haven't worked. I'd like a VBA solution because I don't know my butt from a hole in the ground when it comes to shell programming and so that it can be added to my VBA script for the Full.txt file.

Maybe the files don't need to be renamed at all--like I said, the end goal is the Full.txt file in C:Daily Folder that contains all the lines from the daily text files.

View 3 Replies View Related

Renaming Files In Infinite Number Of Sub Folders?

Feb 25, 2014

I have written a procedure that renames excel files within a folder based on certain words being in the title. It works for the files in the first lot of subfolders but I am trying to work out how to get it to rename all files in all subfolders regardless of the level.

I've seen similar things done using recursive subs but I have been trying to convert some of the sample codes I've found online to fit my situation and am not having much luck.

how to manipulate my code into something like a recursive procedure or anything else that will do what I want.

Here is my code.

[Code] .....

View 2 Replies View Related

Renaming Files And Changing Vlookup References

Dec 19, 2012

I have about 85 files that I need to rename. These files contain data concerning delivery and quality data of each of our suppliers...1 file per supplier.

Current File Name: SupplierA 2012.xlsx
New File Name: Supplier A 2013.xlsx

Is there a way to do mass rename the files..

and that leads to my second question...each of these files contain vlookups to 2012 data...which is contained in the a file with 2012 in the file name. I need to replace source data file (i.e. 2012 Index) with the new file containing the 2013 data (i.e 2013 Index). Is there a way to replace that source data across 85 or so files using some time of mass find and repalce? All the cell references remain the same

View 2 Replies View Related

Renaming Worksheets Via VBA

Jul 5, 2007

I am probably just having a stupid moment, however I have a problem

Whats the best way to rename a worksheet using VBA?

I was going to use a count function to count the number of worksheets, then change worksheet (eg) 10, 11 & 12

View 10 Replies View Related

Renaming Tabs On The WorkSheets

Aug 4, 2007

I mean renaming tabs on the sheets, the tabs at the moment are called Output 1 (*****) instead of Sheet 1 etc.. and i would like that changed to contents in cell A9. If possible only the Output 1 will be removed and it will change to something similar "X-Ray (5E4TT)"

i have a workbook with worksheets named Output 1 (*****)

(the stars being a five digit/letter code - the only thing that changes on the workbook)

now the problem is, i pull of reports and sometimes worksheets can be up to one hundred.. now in cell a9 is the name of the report. I have found vba code to rename sheets to cell contents **extract below**

but any chance of renaming contents of cell a9 to a worksheet named Output 1 (*****) etc.. and if the contents of cell a9 can be trimmed so only certain part of a lengthy title

Sub RenameTabs()

For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If

End Sub

View 14 Replies View Related

Adding And Renaming Worksheets Using VBE

Apr 13, 2006

I've undertaken a project for work with my very very limited knowledge of Excel VB.

What I am trying to do is automate the process of creating a new worksheet in a "Master" Workbook, rename and save that worksheet with the date it was created.

Each worksheet will hold a list of dates for staff members who have attended or attempted courses for that week and the information will come in via email in separate pre-created worksheet templates. The consolidated weekly data will ultimately end up in an MS Access db (but needs to be sorted and validated before upload).

Here is the code I have so far:

Private Sub AddSheets()

Application. ScreenUpdating = False

Worksheets.Add.Move Before:=Sheets(1)
Worksheets.Visible = True

Dim myDate
myDate = Date

Sheets. Name = Date

End Sub

Using VB6.3 in Excel 2000

View 4 Replies View Related

Renaming Worksheets While Leaving Some Unchanged

Dec 18, 2012

I have a workbook with 70 worksheets and I'd like to rename 65 of them while leaving the first 5 worksheets unchanged.

The following changes all worksheets. Is there anyway to modify so that the name on the first 5 worksheets remain unchanged.

Sub ChangeWorkSheetName()
Dim WS As Worksheet
i = 1
On Error Resume Next
For Each WS In Worksheets
WS.Name = "Cost" & i
i = i + 1
End Sub

View 6 Replies View Related

ActiveSheet Renaming With Multiple Worksheets

Aug 19, 2013

I've currently got a problem with renaming the ActiveSheet when multiple worksheets are present. The code below, utilizes existing fields on the worksheet and a concat with static text. The code works great if only one sheet is present. However, as soon as the second sheet is inserted in the workbook, I'm now receiving a 1004 error (application-defined or object-defined error).

Private Sub updateWorksheetName_Click()
Dim fNumber
Dim pCheckNumber


View 9 Replies View Related

Renaming New Worksheets From List In Existing Worksheet

Jan 11, 2013

I'm attempting to add a variable number of worksheets (dependant on the number of entries in an existing list), then rename those sheets to the name of the entries in that list. i.e.

With the list above, i'd want 3 new sheets added, one named 'A', one named 'B' and one named 'C'.

The trouble i'm having is that because the number of entries/sheets varies, i don't want to refer to 'Sheet1' 'Sheet2' etc. to rename them.

***Additional Info*** the first record in the list is always "W4" on sheet "Data"

Below is where i've got to so far (however, i'm pretty new to this so it's almost certainly not all that efficient!)

Sub setup_1()


[Code] .......

View 2 Replies View Related

Looping Files In Directory - VBA

Aug 22, 2013

I wrote the following routine to loop files in folder. I have 4 .xls files in my test folder. This code is only grabbing 1 then exiting. I have used the Dir() loop before with no problems.

Private Sub GetDataMultipleFiles(ByVal importExt As String, ByVal xFileNum As Integer, ByVal importLoc As String)
Dim Sep As String, myFile As String
Sep = Application.PathSeparator
myFile = Dir(importLoc & Sep & "*" & importExt & "*")
Do While Len(myFile) > 0

[Code] ..........

View 2 Replies View Related

Lots Of XLS Files In A Directory

Feb 6, 2009

Lots of XLS files in a directory (C: emp). Each book has only a sheet1 with the data.

I have another workbook that i need to use to overwrite certain values in one column from each book.

The workbook I have has a column A and B, ID and value.

In each file inside the directory, A3 to last row used in A had each ID. If the id matches to an id in the workbook with the new values, I want to take the value from B in the override table and put it in C in the file. Look at each ID in each file against each ID in the override, replace and close.

View 9 Replies View Related

List Of .xls Files In Directory

May 26, 2006

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

Finding Files In A Directory

Jul 18, 2006

I have the following
Sub test()
Set fs = Application.FileSearch
With fs
.LookIn = "\directory"
.Filename = "3000333"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
MsgBox "There were no files found."
End If
End With
End Sub

It looks into a specific directory, and tells me how many files beginning with 3000333 there are in that folder. I have over a thousand files in the folder, 13 of which begins with 3000333. However, according to this code, it only finds 10. After searching for various different strings, I've found out that it doesn't search for any file that has a modified date later than 6/2/2006. However, if i just use .Filename = "3000333" , that is, search for all the files in the directory, I get all the files, even those that were modified later than 6/2/2006 that I could not previously find.

View 9 Replies View Related

List Of Files In A Directory

Oct 8, 2006

Is it possible to get a list of files in a certain directory as a list stored in a spreadsheet?

View 5 Replies View Related

All Image Files From Directory

Nov 25, 2006

I would like to list all the files I have in a directory. I know how to do this.

* * *

Now I would like to list just the image files such as PNG, JPG etc. I mean just the files I could get with: Application.Dialogs(xlDialogInsertPicture).Show

My Problem, I don't know what extension is "Todas las imagenes", "All the Images"
I mean the Extension I get When using: Application.Dialogs(xlDialogInsertPicture).Show

View 3 Replies View Related

Moves Files To New Directory

Dec 7, 2006

I've a master sheet called records. It consolidates all the data from various worksheets. Overtime, the lists gets very long. So I'm proposing this. I want a macro which - Shift all the data yearly into a new directory at

G:MMTPMVarious StockArchive. When it saves at this directory it should bear the year as the folder name. The dates are retrieved from column H. - it should be operated at the every end of the year.

View 4 Replies View Related

Run Macro On All Files In A Directory

Jul 5, 2007

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

List All Files In Directory

Feb 7, 2008

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
End If
If Right(sDirectory, 1) <> "" Then
sPath = sDirectory & ""
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 5 Replies View Related

Loop Through All Files In Subdirectories Of A Directory

Feb 26, 2010

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,

View 9 Replies View Related

Directory Listing: Skips .zip Files

Jun 10, 2006

I'm using a piece of code I found here on Ozgrid to create a list of files in a specific folder. And even though this worked fine for me a week ago, now when I run this same macro, the. zip files are left out of the list, even though I'm specifying msoFileTypeAllFiles. Archive files with other extensions, such as .tar.gz, do make the list. I get the feeling that my zip files are being considered folders instead of files. Do you have any idea why this is happening, or what to do to get that list of zip files?

Sub HyperlinkXLSFiles()
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
'Change path to suit
.LookIn = "o:DataFeeds"
.FileType = msoFileTypeAllFiles.....................

View 2 Replies View Related

Import Few Txt Files From One Directory To One Sheet

Jul 26, 2006

My text files look like this(each has different number of rows):
2006-08-25 13:33:20 82,8 g
2006-08-25 13:34:10 58,5 g

between each column there is Tab

To import them from one directory (directories are named as month e.g. 8) I used:

Sub Consolidate()
Dim mnthNum As Integer
Dim myBook As Workbook
Dim myRows As Long

With Application
.DisplayAlerts = False
.EnableEvents = False
. ScreenUpdating = False
End With

and it works almost perfectly.. the problem is that each time it loads next file it overwrites the previous..

e.g. if i have in my directory 4 files and sum of the rows is 20 it will show only 17. The import is good because I checked it with debugger but each time a new file is copied it starts from last row of previous file.. that's the problem I can't solve...

View 5 Replies View Related

Breakdown Of All Folders & Files In Directory

Sep 12, 2007

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 Related

Copyrights 2005-15, All rights reserved