Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
I have 56 values in A1:A56 and 56 folders (1,2,3.....56) in C: drive.
Can a macro be written such that in a single click the the value in cell A1 is assigned to 1st folder in C:, cell A2 value assigned to 2nd folder, A3 value assigned to 3rd folder etc.... If yes, please let me know the macro to accomplish this. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Exactly what do you mean by "cell A1 is assigned to 1st folder in C"? Do you
mean you want to rename the folder (as your Subject line seems to indicate) or do you have something else in mind when you say "assigned"? Also, what do you mean by 1st folder, 2nd folder, etc. To the best of my knowledge, folders don't really have a predictable "order" to them. Rick wrote in message ups.com... I have 56 values in A1:A56 and 56 folders (1,2,3.....56) in C: drive. Can a macro be written such that in a single click the the value in cell A1 is assigned to 1st folder in C:, cell A2 value assigned to 2nd folder, A3 value assigned to 3rd folder etc.... If yes, please let me know the macro to accomplish this. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Yes.
Are your folders actually called 1,2,3 etc - if so then surely you can just put these values into your cells. If not then what makes the first folder the first one and not the last one? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 11:25 am, "Rick Rothstein \(MVP - VB\)"
wrote: Exactly what do you mean by "cell A1 is assigned to 1st folder in C"? Do you mean you want to rename the folder (as your Subject line seems to indicate) or do you have something else in mind when you say "assigned"? Also, what do you mean by 1st folder, 2nd folder, etc. To the best of my knowledge, folders don't really have a predictable "order" to them. Rick wrote in message ups.com... I have 56 values in A1:A56 and 56 folders (1,2,3.....56) in C: drive. Can a macro be written such that in a single click the the value in cell A1 is assigned to 1st folder in C:, cell A2 value assigned to 2nd folder, A3 value assigned to 3rd folder etc.... If yes, please let me know the macro to accomplish this. Thanks- Hide quoted text - - Show quoted text - Rick, Anon that was not a well-written question. I admit. Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. I hope you got it. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 12:06 pm, wrote:
On Oct 23, 11:25 am, "Rick Rothstein \(MVP - VB\)" wrote: Exactly what do you mean by "cell A1 is assigned to 1st folder in C"? Do you mean you want to rename the folder (as your Subject line seems to indicate) or do you have something else in mind when you say "assigned"? Also, what do you mean by 1st folder, 2nd folder, etc. To the best of my knowledge, folders don't really have a predictable "order" to them. Rick wrote in message oups.com... I have 56 values in A1:A56 and 56 folders (1,2,3.....56) in C: drive. Can a macro be written such that in a single click the the value in cell A1 is assigned to 1st folder in C:, cell A2 value assigned to 2nd folder, A3 value assigned to 3rd folder etc.... If yes, please let me know the macro to accomplish this. Thanks- Hide quoted text - - Show quoted text - Rick, Anon that was not a well-written question. I admit. Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. I hope you got it.- Hide quoted text - - Show quoted text - Also, it would be great if I know the macro for vice versa. i.e. Grabbing the folder's name or file name and putting it in the cells of the excel worksheet. But, please give my previous query the first preference. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in
C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. This macro should do that... Sub RenameFolders() Dim X As Long For X = 1 To 40 If Dir("C:\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub You do realize this will only work once, right? I mean, after you run it, there will be no folders left with the names 1, 2, 3, etc. against which you could run the macro. Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Also, it would be great if I know the macro for vice versa. i.e.
Grabbing the folder's name or file name and putting it in the cells of the excel worksheet. I don't see how that would be possible. I mean, there is no way of identifying the folders that used to be named 1, 2, 3, etc.; so, out of all the folders on the C drive, there is no way of knowing which 40 you would want. Also, **assuming** you could somehow identify them, unless the text in the cells of Column A had sequential numbers in them when you renamed the folders, there would be no way of determining the right order to read the folder names back in. Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 12:36 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. This macro should do that... Sub RenameFolders() Dim X As Long For X = 1 To 40 If Dir("C:\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub You do realize this will only work once, right? I mean, after you run it, there will be no folders left with the names 1, 2, 3, etc. against which you could run the macro. Rick Rick, just for testing I am having 11 folders (1,2,3.......11) in C: \test directory and I using the following code. The excel file has values in the cells A1 to A11. I executed the macros and I dont see the folders getting renamed. Am I missing something something? Please let me know. Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\test" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub I realize it would work once. Also, I understand ur clarification to the other question. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Rick,
To test, I have 11 folders in c:\test directory and an excel file with A1 to A11 cells with values. I am using the following code. I dont see the folders getting renamed. Am I missing something? Thanks Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\test" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub On Oct 23, 12:36 pm, "Rick Rothstein \(MVP - VB\)" wrote: Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. This macro should do that... Sub RenameFolders() Dim X As Long For X = 1 To 40 If Dir("C:\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub You do realize this will only work once, right? I mean, after you run it, there will be no folders left with the names 1, 2, 3, etc. against which you could run the macro. Oh ya.. Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Are you executing the subroutine from the correct Sheet's code window?
Alternately, you could qualify the Range objects location with Worksheets("Sheet1") or the like. Rick wrote in message ups.com... Rick, To test, I have 11 folders in c:\test directory and an excel file with A1 to A11 cells with values. I am using the following code. I dont see the folders getting renamed. Am I missing something? Thanks Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\test" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub On Oct 23, 12:36 pm, "Rick Rothstein \(MVP - VB\)" wrote: Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. This macro should do that... Sub RenameFolders() Dim X As Long For X = 1 To 40 If Dir("C:\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub You do realize this will only work once, right? I mean, after you run it, there will be no folders left with the names 1, 2, 3, etc. against which you could run the macro. Oh ya.. Rick |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 2:14 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Are you executing the subroutine from the correct Sheet's code window? Alternately, you could qualify the Range objects location with Worksheets("Sheet1") or the like. Rick wrote in message ups.com... Rick, To test, I have 11 folders in c:\test directory and an excel file with A1 to A11 cells with values. I am using the following code. I dont see the folders getting renamed. Am I missing something? Thanks Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\test" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub On Oct 23, 12:36 pm, "Rick Rothstein \(MVP - VB\)" wrote: Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. This macro should do that... Sub RenameFolders() Dim X As Long For X = 1 To 40 If Dir("C:\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub You do realize this will only work once, right? I mean, after you run it, there will be no folders left with the names 1, 2, 3, etc. against which you could run the macro. Oh ya.. Rick- Hide quoted text - - Show quoted text - Rick, I am using the subroutine in a module and I call the subroutine by clicking command button. Now I am getting Path/File access error. Can you please send the excel file (the one you have tried writing the code for me) you have to Thanks Private Sub CommandButton1_Click() Call RenameFolders End Sub In a module I have this: Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Worksheets("Sheet1").Range("A" & CStr(X)).Value) < "" Then Name "C:\test\" & CStr(X) As "C:\test\" & Worksheets("Sheet1").Range("A" & CStr(X)).Value End If Next End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 3:01 pm, wrote:
On Oct 23, 2:14 pm, "Rick Rothstein \(MVP - VB\)" wrote: Are you executing the subroutine from the correct Sheet's code window? Alternately, you could qualify the Range objects location with Worksheets("Sheet1") or the like. Rick wrote in message oups.com... Rick, To test, I have 11 folders in c:\test directory and an excel file with A1 to A11 cells with values. I am using the following code. I dont see the folders getting renamed. Am I missing something? Thanks Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\test" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub On Oct 23, 12:36 pm, "Rick Rothstein \(MVP - VB\)" wrote: Okay, I have 40 folders named 1, 2, 3, 4... 40.. The folders are in C: The folder named 1 should be renamed by the text/value in cell A1. The folder named 2 should be renamed by the text/value in cell A2. This macro should do that... Sub RenameFolders() Dim X As Long For X = 1 To 40 If Dir("C:\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Range("A" & CStr(X)).Value) < "" Then Name "C:\" & CStr(X) As "C:\" & Range("A" & CStr(X)).Value End If Next End Sub You do realize this will only work once, right? I mean, after you run it, there will be no folders left with the names 1, 2, 3, etc. against which you could run the macro. Oh ya.. Rick- Hide quoted text - - Show quoted text - Rick, I am using the subroutine in a module and I call the subroutine by clicking command button. Now I am getting Path/File access error. Can you please send the excel file (the one you have tried writing the code for me) you have to Thanks Private Sub CommandButton1_Click() Call RenameFolders End Sub In a module I have this: Sub RenameFolders() Dim X As Long For X = 1 To 11 If Dir("C:\test\" & CStr(X) & "\", vbDirectory) < "" And _ Trim(Worksheets("Sheet1").Range("A" & CStr(X)).Value) < "" Then Name "C:\test\" & CStr(X) As "C:\test\" & Worksheets("Sheet1").Range("A" & CStr(X)).Value End If Next End Sub- Hide quoted text - - Show quoted text - Rick, Got this working: Sub RenameFolders1() Dim X As Long For X = 1 To 11 Name "C:\test\" & CStr(X) As "C:\test\" & Worksheets("Sheet1").Range("A" & CStr(X)).Value Next End Sub I used F8 and found that the line after the if loop was not executing. So, I removed the if loop and got it working.. But still wondering why the if loop didnt do the trick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
Rick, Got this working:
Sub RenameFolders1() Dim X As Long For X = 1 To 11 Name "C:\test\" & CStr(X) As "C:\test\" & Worksheets("Sheet1").Range("A" & CStr(X)).Value Next End Sub I used F8 and found that the line after the if loop was not executing. So, I removed the if loop and got it working.. But still wondering why the if loop didnt do the trick I put the If-Then test in there to stop errors that could result if the folder didn't exist for some reason or if the cell was empty. As long as you are sure the folders are all there and that the cells all have text in them, you should be alright without it... but I would rather see it there myself. Since I test the same things I use in the Name..As statement, I am a little puzzled as to why the If-Then test was blocking you. I'm on my way out for the evening; but if you still want me to send you my test file, I will do so later on this evening... just let me know. Rick |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 5:17 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Rick, Got this working: Sub RenameFolders1() Dim X As Long For X = 1 To 11 Name "C:\test\" & CStr(X) As "C:\test\" & Worksheets("Sheet1").Range("A" & CStr(X)).Value Next End Sub I used F8 and found that the line after the if loop was not executing. So, I removed the if loop and got it working.. But still wondering why the if loop didnt do the trick I put the If-Then test in there to stop errors that could result if the folder didn't exist for some reason or if the cell was empty. As long as you are sure the folders are all there and that the cells all have text in them, you should be alright without it... but I would rather see it there myself. Since I test the same things I use in the Name..As statement, I am a little puzzled as to why the If-Then test was blocking you. I'm on my way out for the evening; but if you still want me to send you my test file, I will do so later on this evening... just let me know. Rick Rick, please send the file to me. Thanks. My email id is |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to assign cell values as folder name
On Oct 23, 5:17 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Rick, Got this working: Sub RenameFolders1() Dim X As Long For X = 1 To 11 Name "C:\test\" & CStr(X) As "C:\test\" & Worksheets("Sheet1").Range("A" & CStr(X)).Value Next End Sub I used F8 and found that the line after the if loop was not executing. So, I removed the if loop and got it working.. But still wondering why the if loop didnt do the trick I put the If-Then test in there to stop errors that could result if the folder didn't exist for some reason or if the cell was empty. As long as you are sure the folders are all there and that the cells all have text in them, you should be alright without it... but I would rather see it there myself. Since I test the same things I use in the Name..As statement, I am a little puzzled as to why the If-Then test was blocking you. I'm on my way out for the evening; but if you still want me to send you my test file, I will do so later on this evening... just let me know. Rick Rick, please send the file to me. Thanks. My email id is |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign Cell Values without a Loop | Excel Discussion (Misc queries) | |||
How to assign values to a cell based on values in another cell? | Excel Worksheet Functions | |||
can I assign a macro to a cell? | Excel Programming | |||
Assign values to text within a cell | Excel Worksheet Functions | |||
Assign Macro to cell??? | Excel Programming |