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: ....
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|
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 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 AAA-BBB-001.PDFAAA-BBB-001-Description.PDF AAA-BBB-002.PDFAAA-BBB-002-Description.PDF AAA-BBB-003.PDFAAA-BBB-003-Description.PDF AAA-BBB-004.PDFAAA-BBB-004-Description.PDF AAA-BBB-005.PDFAAA-BBB-005-Description.PDF
I keep them in the destination folder below:
C:UsersmeDesktopA
I prefer to use an excel macro since I can't install any additional software on my work computer.
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:
Monday.csv Tuesday.csv
I'd like to run some kind of macro to rename them to:
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 Wend
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.
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.
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
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 Next
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).
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).
Code: Private Sub updateWorksheetName_Click() Dim fNumber Dim pCheckNumber
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.
LIST A B C 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!)
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.
Code: 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
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.
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 Else 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.
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
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.
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).
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.
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,
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 .NewSearch 'Change path to suit .LookIn = "o:DataFeeds" .FileType = msoFileTypeAllFiles.....................
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...
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?
