WeaponX
Dec 27 2005, 11:37 PM
| | 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. |
Comment/Reply (w/o sign-up)
doudou
Jul 1 2006, 02:30 AM
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.
Comment/Reply (w/o sign-up)
seec77
Jul 1 2006, 11:31 AM
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.
Comment/Reply (w/o sign-up)
WeaponX
Jul 9 2006, 02:33 AM
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?
Comment/Reply (w/o sign-up)
seec77
Jul 9 2006, 10:33 AM
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!
Comment/Reply (w/o sign-up)
seec77
Jul 9 2006, 11:35 AM
Well, I'm done! Here it is: Click to view attachmentIt 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!  ). The little testing I've done proved it quite useful, but it if you're having trouble with it write back. 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 ). Personally, I would advise you to give the macro a button on your toolbar by doing this: - Go to Tools -> Customize...
- Open the Toolbars tab, and click on New...
- Name your new toolbar "Rename" or something like that.
- Go to the Commands tab, and scroll the right listbox down to Macro Commands.
- Drag a Customizable Button to your newly created toolbar that should be open.
- Right click the new button on your toolbar and choose the lowest option ("Associate Macro", or something to that effect) and choose the RenameFiles macro.
- Right click again and change the name and other properties of the button.
- Drag the toolbar to wherever you want it at.
Enjoy!
Comment/Reply (w/o sign-up)
WeaponX
Jul 11 2006, 12:47 AM
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?
Comment/Reply (w/o sign-up)
seec77
Jul 11 2006, 08:58 AM
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. Anyways, I now put the word to match in the Match variable, and it automatically makes it lower-case and measures its length, so you should have no problem messing around with it now. 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!
Comment/Reply (w/o sign-up)
WeaponX
Jul 11 2006, 11:20 PM
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.
Comment/Reply (w/o sign-up)
seec77
Jul 12 2006, 09:01 AM
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 ) so tell me what you want. And maybe the program as of now regards the extension as a part of the filename, and takes the whole file as a string instead of taking the extension seperately. That could explain why the TXT file came before. 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.
Comment/Reply (w/o sign-up)
iGuest
Oct 29 2009, 12:46 PM
Dynamically renaming files within a folder
Renaming Files (Using Excel Spreadsheet)
I have one folder with some image files in it...I need to dynamically rename the files ,w.Rto the data in an excel.
e.G.
I'm having one excel sheet with the following data
Market Code Market Name
500 Boston
505 Atlanta
600 albany
etc...
The Image files in the folder are stored in the name of "Market Name" (e.G. Boston.Jpg) .I need to Rename the files w.Rto "Market Code" ( I.E 500.Jpg instead of boston.Jpg).
there are around 270 image files in the folder.
can anyone please suggest a solution for this issue...The solution may be in any technology (Macro, batch file etc)
-reply by poorani
Comment/Reply (w/o sign-up)
iGuest
Sep 23 2009, 07:10 PM
EXCEL Macro/VBA help
Renaming Files (Using Excel Spreadsheet)
Hi,
Although I am good with excel I can't do crap with Macros other than record them. Can a macro be written to find certain data and move the entire row to another sheet in the same workbook? I can't just record one because each month the data I need is in a different row when I get the report.
For example, I need the row of information with code 8MG to be pulled from the list and cut or copied to row, whatever, we'll say row 4 in the next sheet over. Then I need it to do the same for the row of information with code 8C4 to other sheet row 5…and so on. My issue is the codes mentioned fall into different rows each time the original report is printed. ARGHHH! 
Thanks,
Lou
Comment/Reply (w/o sign-up)
iGuest
Aug 18 2009, 02:00 AM
Need to check whether a particular link is active in a workbook
Renaming Files (Using Excel Spreadsheet)
Hi all,
my file is linked to many other excel file, which needs to be changed every month with different filename thru edit link. So
I need a macro code which can helps me to check whether a particular path&filename is linked in the workbook. If the link is acive I can change it thru edit link code in macro. If not active or if couldnot find the link it can be ignored.
Prabhu
Comment/Reply (w/o sign-up)
iGuest
Jan 2 2009, 09:48 AM
Macro to compare two excel files
Renaming Files (Using Excel Spreadsheet)
Hi Experts,
Am undergoing a project in Excel and I need a macro to Automate my job.
I need to compare two Excel files. One containing the with values in the second column I.E, in Column B (range 3:255) for eg . Table.Xls and another excel file containing the orginal values in row wise I.E, (range B3:M3) for eg. Original .Xls. And the original .Xls excel file has Several Sheets. I need a macro to compare the values in the first sheet of original .Xls with the vales in the Table.Xls. If it is matched a massage box should be shown as "Values Matched" or "Values Not Matched". It should be done for all the Sheets.
I hope I will get good help from this site.
Please Help me as soon as possible
Thanks
jagadesh
-question by jagadesh
Comment/Reply (w/o sign-up)
iGuest
Nov 20 2008, 03:59 AM
Rename a Macro to a the value of a cell
Renaming Files (Using Excel Spreadsheet)
I have a very VSB question Here it I I have a monthly workflow that needs to be renamed to the value of cell AG65 Here is the expresion I have so far it renames it and saves it into ChDir "Q:\AR - PS Comment_RTP Queue\Templates" Workbooks.Open Filename:= _ "Q:\AR - PS Comment_RTP Queue\Templates\Linked CMT_RTP WorklfowTemplate New.Xls" _ , UpdateLinks:=3 Windows("Linked Workflow Template1.Xls").Activate Windows("Linked CMT_RTP WorklfowTemplate New.Xls").Activate Windows("Linked Workflow Template1.Xls").Activate ChDir "Q:\AR Supervisor\WorkFlow\Workflow 2008" Range("BC49").Select ActiveWindow.SmallScroll Down:=-12 ActiveWorkbook.SaveAs Filename:= _ "Q:\AR Supervisor\WorkFlow\Workflow 2008\New AR Workflow.Xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Windows("Linked CMT_RTP WorklfowTemplate New.Xls").Activate ChDir "Q:\AR - PS Comment_RTP Queue" ActiveWorkbook.SaveAs Filename:= _ "Q:\AR - PS Comment_RTP Queue\New CMT_RTP Workflow.Xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=Falsedifernte locations but I need it ot rename it as the weeks go on Instead of "New AR Workflow" I need it to be the value of cell AG65 which would be the begining weekday -reply by robert
Comment/Reply (w/o sign-up)
Similar Topics
Keywords : renaming, files, excel, spreadsheet
- Help With File Renaming Based On An Excel Spreadsheet
(2)
Help Needed With Office 12 Beta 2
folder trees and files inside (1) Really stupidly, after finally downloading my copy of office 12 beta 2, with office professional,
groove, onenote, project, sharepoint designer, and visio, i extracted the files from each exe
installer package into directories, and someone (probably me) deleted my original downloads. The
problem is, that i can't install it now, because it wants the folder trees it would make when
extracting normally. For example, groove: my extraction has it all in one folder, all files. Whereas
the server version (which i'm not interested in installing, didn't come as a sing....
MS Excel -- Automatic Update Of Data
(11) I've got a cell that contains a number, and it is likely for hat number to increment or
decrement itself on a ragular basis. Could someone briefly explain how it would be made for the
number to adjust automatically? For example, lets say that that cell is driven by a database, if I
import data from a database, and then the information in the db changes, will Excel adjust
accordingly? Please help -- I'm really stuck on this. It's part of some coursework I'm
doing and I'm desperate for an answer.....
Excel Question
(2) I am trying to make a spreadsheet that will automatically add the numbers in the first two collumns
in every row and write the answerin the third collumn, but I'd like it to carry over for every
row after that, but without me having to set it to add them, does anybody know how to do this?....
Excel - Verifying Data...
(3) Hi, I have an Excel spreadsheet here that has 4 columns in it. The Date, some number, and whether
it was received or not. I have the Not Received column highlighted in blue color. But what I want
to do is that if there is an X marked in the column for Received, and the number matches to the one
that's not received, I want the blue color to be changed to the default black again. As of now,
I'm doing a lot of this manually. So, for example: Date Number Received Not
Received 10/1/05 123 X 10/4/05 456 X 10/8/05 12....
Pdf Plugin For Excel?
is there a way to export to pdf in excel (6) Hey, Just curious if anyone knows if tehre is a plug in of some sorts for excel that allows you to
export to pdf format instead of the usual excel document? In open office they allow you to export
to a pdf format, for the last little while I've been creating spread sheets within excel and
then porting them over to open office and exporting from there to pdf format (either that or
creating the document right in open office). When I do create the documents in excel and then move
them over to open office to export something always seems to change within the document t....
Looking for renaming, files, excel, spreadsheet
|
See Also,
*SIMILAR VIDEOS*
Searching Video's for renaming, files, excel, spreadsheet
|
advertisement
|
|
