Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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? |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |