Rename folders in VBA

[Jimmcculloch]

I have an Excel file that does a lot of tasks using VBA,then makes a new folder named "New" in a specific directory. It then saves the file in that folder.
That works great but I would like to be able to rename that folder to the same name as the Excel file which is in a specific cell in the file.

I use VBA a lot but not to this depth. Can anyone help me please.

 

[Norie]

Why don't you use the name you want when you create the folder instead of 'New'?

 

[Jimmcculloch]

Thanks for the prompt reply.I do not run the macro. Other QEs do. I currently go into what they have created and manually change it, the file name is different every time the macro runs.

 

[Norie]

In VBA you can use Name to rename files and folders.

Dim strOldDirName As String
Dim strNewDirName As String
 
strOldDirName = "C:\New"

strNewDirName = "C:\TheNewName"

Name strOldDirName As strNewDirName

Obviously you would need to use the new directory name that you want.

 

[Kenneth Hobson]

Sub RenNewFolder()
  Dim rootFolder As String, subFolder As String, fn As String
 
  rootFolder = "x:\test\"
 
  subFolder = rootFolder & "New\"
  If Dir(subFolder, vbDirectory) = Empty Then Exit Sub
  fn = GetBaseName(Dir(subFolder & "*.*"))
  If fn = Empty Then Exit Sub
  If Dir(rootFolder & fn, vbDirectory) <> Empty Then
    MsgBox rootFolder & fn & " exists.", vbCritical, "Macro Ending"
    Exit Sub
    Else
    Name subFolder As (rootFolder & fn)
  End If
End Sub

Function GetBaseName(Filespec As String)
  Dim FSO As Object
  Set FSO = CreateObject("Scripting.FileSystemObject")
  GetBaseName = FSO.GetBaseName(Filespec)
End Function

 

[Jimmcculloch]

Kenneth many thanks for your reply but I do not understand a couple of things.
Where do I reference a Cell in my excel sheet to give the folder name
eg In Spreadsheet " Test", Cell "B6" has an entry of "2011010" I wish that to be the name of the new folder. This cell entry is fed from another macro which gives the spreadsheet that name automatically from a log of entries

My folders for this test are arranged as follows.
U:\Excel\Test Folder
My spreadsheet "2011010" is in the following location
U:\Excel\Test Folder\New.

I really appreciate your help.

If that is not possible is it feasible to use a User Input box to type in the new folder name then change the folder to it?

 

[Kenneth Hobson]

The code that I gave named the New subfolder to be the name of the first file's base name in that folder. I guess that I did not read your post as well as I thought.

When you say spreadsheet Test, what is the exact name or just what is this file? Is it the current workbook with the code, or U:\Excel\Test Folder\New\Test.xls or? Cell B6 is in what sheet name?

While you can add an inputbox, why do it if your process follows a logical path. The hard part for us is to understand what your process actually is. I have a feeling that your process can be fully automated with a single run with no input.

 

[Jimmcculloch]

Sorry if I did not make it clear. Let me try with the actual rather than the test.
My folder tree is as follows
T:\QE\Current SCARs\New SCAR (A SCAR is a supplier concern)

When a concern is raised an engineer will open and complete "Blank SCAR.xls" He then hits a macro button which opens "Log.xls" it then takes the pertinent data from "Blank SCAR.xls" and transfers it to "Log.xls" It then gets the next unique record number from "Log.xls" and transfers it back to Cell "C17" in "Blank SCAR.xls" The macro then makes a folder named "New SCAR" in the tree above and saves "Blank SCAR.xls" as the unique number from Cell "C17"
i.e. Blank SCAR.xls becomes 2011001.xls in the folder named "New SCAR"
I would like it to do this:
Blank SCAR.xls becomes 2011001.xls in a folder named "2011001"
The 2011001 will increment up one every time an engineer sends out a concern.

I hope this makes it clearer but maybe what I am trying is not possible?

Many thanks for any help I can receive. I believe in making Excel do what I want it to do to "Reduce work".

 

[Jimmcculloch]

To further clarify
Here is my code to make the new folder


MkDir "T:\QE\Current SCARs\New Scar"
ChDrive "T:\QE\Current SCARs\New Scar"
ChDir "T:\QE\Current SCARs\New Scar"

Is there any way to replace the words "New SCAR" with a cell range in the spreadsheet?

 

[Kenneth Hobson]

It sounds like what I posted would suffice for Case 2 below. You just need to changes the paths in rootFolder to "T:\QE\Current SCARs\" and subFolder to "New Scar".

There may be some other things that I don't understand. e.g.
Case 1 Exists?
"T:\QE\Current SCARs\New Scar\Blank SCAR.xls"
Case 2 Exists?
"T:\QE\Current SCARs\New Scar\2011001.xls"

In any case, you would want: "T:\QE\Current SCARs\2011001\2011001.xls"?

The incremental part might cause some other issues that need to be addressed.