|
|
|
|
![]() ![]() |
Dec 27 2005, 11:37 PM
Post
#1
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
Hi, I want to use filenames on my Excel Spreadsheet for the files I have in my folder.
For example, let's say I have these in a folder: Reportdummy.doc Charts102.xls DecemberGraphs.xls TestScrap.txt etc... And I have this in my Excel spreadsheet: Report1.doc Charts.xls Graphs.xls Scrap.txt etc... Is there an easy way for me to copy the cell's value to the file in that folder? I want them to be copied exactly in that order. So far I have to do this manually (copy from Excel, then rename and paste for each file). Thanks. |
|
|
|
Jul 1 2006, 02:30 AM
Post
#2
|
|
|
Advanced Member Group: Members Posts: 114 Joined: 1-July 06 Member No.: 14,234 |
I guess I don’t quite understand your question., maybe you can clarify. When you said you have files in your excel spreadsheet. Do you mean individual worksheet? I don’t understand how you can have and doc file inside a spreadsheet.
|
|
|
|
Jul 1 2006, 11:31 AM
Post
#3
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
I think I understand what you want to do, and I think it can easily be done with VBA (Visual Basic for Applications) and FSO (file system object). Do you have experience with these technologies? If not I can try to dig up my VB skills and conceive some code for you. I just don't understand by what order you're looking at the files in the directory. I mean, how would the code know to call Reportdummy.doc (the "first" file in the folder) by the spreadsheet value of Report1.doc. Why not call Charts102.xls Report1.doc (after all, C comes before R). I hope I understood your initial request correctly.
|
|
|
|
Jul 9 2006, 02:33 AM
Post
#4
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
I have specific filenames in my Excel spreadsheet that I want to use, but the files themselves are not the same. I have to change them to the spreadsheet one by one now. Hard to explain...just need the change
My experience with VBA and FSO are very limited at best. If you can help write up a VBS or VBA script for this that would be great I could actually get the files by some numerical order. Say something like the following in the folder: Report 0701.doc Report 0702.doc Report 0707.doc .... I want them to have these names (which are in the Excel file): Report1.doc Report2.doc Report3.doc ... Quick question. Is it absolutely necessary to have these files in their own folder each time I run the script? Can I enter a "pattern" for it to search and replace? For example, if I wanted to do the same thing for: Budget 0703.xls Budget 0708.xld ... into: Budget1.xls Budget2.xls ... Maybe something like replace anything beginning with Budget to whatever is on the Excel spreadsheet? |
|
|
|
Jul 9 2006, 10:33 AM
Post
#5
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
I think I understood what you're meaning. I'm working on it as I'm writing this and I hope that in half an hour max. you'll have your working VBA code!
|
|
|
|
Jul 9 2006, 11:35 AM
Post
#6
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
Well, I'm done! Here it is:
It turned out quite a few lines of code less than I had imagined. I hope you understand what's being done here. Open up the VBA editor through Alt + F11. The code is in the file for the first worksheet. If you want me to move it over to the whole workspace, and have it work on the current worksheet, I have to move some stuff around, but you can easily move it to your own worksheets as is. Just don't forget to look at the code and change the three variables there (column, starting row, and directory name). The code looks only for files beginning with "budget" (case-insensitive), and then renames them to the values in your worksheet, sorted by the original filesnames (FSO sorts automatically! Anyways, you can call this macro by going to Tools -> Macro -> Macro Commands (or whatever it's called in English, as I have a Hebrew version
Enjoy!
Attached File(s)
|
|
|
|
Jul 11 2006, 12:47 AM
Post
#7
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
It sounds like you hit it right on the money seec77
I opened up the Excel file and then the Visual Basic editor. I left the row and columns alone for testing purposes. I changed the directory to my D: drive and then set the keyword to look for as leatherman. I have a few files beginning with Leatherman...1, 2, 3, etc. some JPGs and one TXT file. I left the first column alone and tried to test it. It didn't seem to work. Does it matter if I change the name of the VBA macro? I had to change it because it was half in Hebrew and it won't allow me to run the macro like that (it doesn't recognize the characters). What am I doing wrong here? This post has been edited by WeaponX: Jul 11 2006, 01:23 AM |
|
|
|
Jul 11 2006, 08:58 AM
Post
#8
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
Oh great, I have to write this post again because I accidently closed my computer before sending it.
First of all: No, changing the name of the sub should not be a problem. What caused the problem for you was changing "budget" to "leatherman". This new code should solve such further problems: CODE Sub RenameFiles() 'Edit this variable to start the name search from a different row: StartRow = 1 'Edit this variable to specify in which column the names are: StartColumn = 1 'Edit this variable to look for a different string at the beginning of filenames Match = "Budget" 'Define an FSO object Dim FSO As FileSystemObject Set FSO = New FileSystemObject 'Create a variable to store the folder holding the files Dim FilesDir As Folder 'Change the string here to look in a different folder (highly recommended :P) Set FilesDir = FSO.GetFolder("C:\yotam\temp\renamefiles\test\") 'Define a counter variable and set it zero Dim i As Integer i = 0 'Loop through all of the files in the folder Dim CurFile As File For Each CurFile In FilesDir.Files 'If the file begins with the word "budget" then rename it If LCase(Left(CurFile.Name, Len(Match))) = LCase(Match) Then 'Rename the file to the value in the specified cell CurFile.Move FilesDir + "\" + Me.Cells(StartRow + i, StartColumn).Text 'Increment the counter so next time we will use a cell from the next row for naming i = i + 1 End If Next End Sub It was causing problems for you because I hard coded the routine to look in the first 6 letters of the filename for the word "budget", and obviously the code never worked because the program couldn't find the word "leatherman" in the first 6 characters of the filename. I'm trying to make a variant of the routine that can work on the cells you select as filenames. Are you interested? And tell me if it works! This post has been edited by seec77: Jul 11 2006, 08:09 PM |
|
|
|
Jul 11 2006, 11:20 PM
Post
#9
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
seec77, that worked perfectly. Thanks
I tried it with 10 letters and left the old code intact to see if that worked also. No problems For that other variant, you mean instead of having it "freely" selecting from the columns, you will have the option to choose the cells to copy the name? Definitely interested in that one. I actually wanted to have that originally, but this code makes it much easier already. If you have the time, please code it so that it will rename according to the selected cells instead. I have huge tables in Excel and would have to change the values each time I do this...I will test it out and report back on the progress of it Question on the renaming. Does it rename in the order the files were sorted in the folder or alphabetically? Also, how does it deal with files with different extensions? For the test, I had 4 JPG files and 1 TXT file. For some reason, it renamed the TXT file first and then the 4 JPG file. |
|
|
|
Jul 12 2006, 09:01 AM
Post
#10
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
As for the most immediate solution to choosing different rows/columns each time, change the first few lines of the subroutine to:
CODE StartRow = CInt(InputBox("Row to start from?", "Row", "1")) StartColumn = CInt(InputBox("Column to start from?", "Column", "1")) This will make the macro pop up two question boxes each time you run it to ask you for a row and a column. About the ordering. I really don't know, because FSO (the file system object which I use to access the files) apparently sorts the files on its own, but I don't know what rules it works with. I can easily create an ordering function (I think I've been messing around with the selection thing. This is really fun, but I have no results to show you right now, but I'll post back as soon as possible. |
|
|
|
![]() ![]() |
Similar Topics
| Topics | Topics | |
|---|---|---|
|
|
|
|
Lo-Fi Version | Time is now: 12th October 2008 - 02:34 AM |