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.
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 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.
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: ....
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'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 am a teacher who has created a gradebook in Excel. Each class has a student template sheet, individual sheets for each student in the class (maximum 26 students/class) which has the structure of the template and some cell referencing to the template, and a couple of mark-entry sheets and a sheet that shows the progress of all students in the class.
A number of teachers in the school have been using this gradebook and I am constantly looking for ways to make it more user-friendly. Currently, when a new course starts, a teacher opens a blank copy of the file (which has all of the sheets I mentioned above present). To set up a class, the teacher enters the students' names once on the mark entry sheet (called the "Binder Sheet"), and then changes the individual student tabs (right click...rename...) to reflect the names of their students.
I am wondering if there is a way to have the individual sheet tabs (Student 1, Student 2...Student 26) update automatically to match the names that the teacher enters on the Binder Sheet in cells A4:A29.
I would like to be able to embed the update procedure/script into the blank template so that it is user friendly for others.
I would like to rename multiple tabs (12 in all) on a spreadsheet by month only. I highlighted all tabs and then performed a cut and past from the previous year spreadsheet, but when the paste was complete the tab names were missing. I need January through December on the 12 tabs. Does anyone know of a shorter process than renaming each tab individually? I have called several people and asked the same question and they are curious if there is a way to do this also and asked that I let all of the know what I find out, so you would be helping quite a few people in several different companies (If that gives you happy thought, then good for all of us ).
how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder) North South East(sub folder) West(sub folder) Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
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.
Example: 12/20/2007 Your Name 123 Somewhere St. Here, OH 45111 Home Visit
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.
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
I have 8 different files all have a set of data in them
each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank. I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
So, today my manager came to me and informed me that he wanted me to do a little data entry for him... WOW... So I have about 2000 invoices to go through(all excel files, in their dated folders... 12-2-2013(folder) and the filenames are 12-2-2013(1), 12-2-2013(2) etc.)
I need to take these individual files and have certain cells from each in certain cells of a new workbook for example...
file 12-2-2013(1)... I need cell C3 from that file to go to cell A2(and this will be the same for all the other files... 12-2-2013(2) cell C3 needs to be in A3 etc etc etc.. this is the sale date) file 12-2-2013(1)... I need cell a15 and b15(they are both "stretched out" cells) to be put in C3 of the new workbook and so on.
is there an easy way/formula to do this all? I have to have this done by friday at the latest(tomorrow preferred @@) I have to input data for 7 cells...
I need to create a dynamic template for importing data from multiple .txt files to my workbook on a daily basis.
I recorded the macro for importing ONE txt files and starting to deveop it into looping through multiple txt files and sheets but to no avail.
1) Currently I have 4 named ranges
FileLocation = Location of my source file FileName = All the file names of my txt files SheetDestination = The sheet to be paste for each txt file (further explaination below) FileFormat = .txt (for VBA purpose)
2) The macro recorded was in "ActiveSheet", thus I thought of looping through each sheet (put the activate code at the first line) for the codes to run.
3) ColA ColB 1SP.txt 2IND.txt 3US.txt 4 5 6
Column A is my SheetDestination, while Column B is the respective filename for source txt file. My final intended result would be simply macthing sheet 1 against SP, sheet 2 against IND, sheet 3 against US... so on and so forth....
I have an excel file for each day of the month. Each file has 2 sheets for sure and some files have 3. I want to make one master file at the end of the month that shows all sheets. *1st sheet is master report. 2nd & 3rd sheets are created as a pivot table.
I have a question about CSV files...I have to import about 80 csv files into excel as separate sheets in the same workbook. I can only get it to import into its own workbook. Is there a way to import all of them into the same workbook all at once? Also, I am currently using csv files, but if what I want done can be done with a different file type, it doesn't matter.
I'm currently using this script to download over 400 txt files which are needed for a project here at work. It works great, however, it will download the ftp files in question (which it gets a list of from another tab) in order. I would like a way to either download all 400 ato nce, or in a batch of 50 or even 100. As it is, the procedure takes around 70 mins to complete. I was hoping of a way to speed it up.
Here is the script I am using.
VB: Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean Dim lngRetVal As Long lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
I have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.
The code I am using is:
Sub su() Dim oWbk As Workbook Dim sFil As String Dim sPath As String sPath = "C:Historical" 'location of files ChDir sPath sFil = Dir("*.txt") 'change or add formats Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited ' do something sFil = Dir Loop ' End of LOOP End Sub
In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed: Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
I need to import about 1500 HTML file into a DB (SQL Server). But (here is the big problem), I only need to import some data of each HTML file (always the same line/variable).
So I thought using Excel as you can say with part of the HTML file you want to import. It works great.
But I did it with one file. Is there a way you can select a whole directory and it does it for you?
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).
I was to combine 130 files into one file without having to open all the files, if it's possible if not, then I guess I'll have to open them up. All Files have the same number of columns but difference number of rows.
