Hi,
this is a tutorial on how to create a folder from excel using VBA as scripting language.
The main command is called MkDir and it represents the basic function in almost all operating systems for creating a folder from shell.Altought we could only write the function with MkDir(path & file_name), what happens if the folder already exists? Well it will overwrite the whole folder, including its sub-files! That's why we need to add some error handling for the program to function properly..
To give you an idea of what you might need this function for, I used to develop a program in which I was creating these folders by clicking a mere button(which had a macro with this function behind) because I was filling fields, automatically, of word templates from excel cells.
Every set of resulted word files needed to be categorised by its request number, so I would create this folder and then dump the set of files correspondent to that folder request number.
If you need to learn how to set up the module, where you can insert the code in excel, and other basic means to develop a macro, look at this article.
The button for the user to run the script can be added by selecting:
Code:
Sub new_folder()
Dim folderName
folderName= "File" 'The name of the folder. In the application that I developed I used ThisWorkbook.Sheets("Sheet1").Range("B2") because I wanted to modify directly from the application sheet and even change it dynamically with dependency to other fields.
If Len(Dir("c:\Documents" & "\" & folderName, vbDirectory)) = 0 Then ' If the length of the returned value of the variable is zero then it's clear that the folder does not exist, so we can go forward and use the MkDir function to create a new folder.
MkDir "c:\Documents" & "\" & folderName
Else
MsgBox ("The file already exists!") ' so otherwise the result is not 0 and that means that the folder already exists. We need to stop stop the function MkDir from running, announce the user and close the sub.
End If
End Sub
PS: If you have problems configuring, running this program you can contact me in the comments or privately :-) .
this is a tutorial on how to create a folder from excel using VBA as scripting language.
The main command is called MkDir and it represents the basic function in almost all operating systems for creating a folder from shell.Altought we could only write the function with MkDir(path & file_name), what happens if the folder already exists? Well it will overwrite the whole folder, including its sub-files! That's why we need to add some error handling for the program to function properly..
To give you an idea of what you might need this function for, I used to develop a program in which I was creating these folders by clicking a mere button(which had a macro with this function behind) because I was filling fields, automatically, of word templates from excel cells.
Every set of resulted word files needed to be categorised by its request number, so I would create this folder and then dump the set of files correspondent to that folder request number.
If you need to learn how to set up the module, where you can insert the code in excel, and other basic means to develop a macro, look at this article.
The button for the user to run the script can be added by selecting:
Code:
Sub new_folder()
Dim folderName
folderName= "File" 'The name of the folder. In the application that I developed I used ThisWorkbook.Sheets("Sheet1").Range("B2") because I wanted to modify directly from the application sheet and even change it dynamically with dependency to other fields.
If Len(Dir("c:\Documents" & "\" & folderName, vbDirectory)) = 0 Then ' If the length of the returned value of the variable is zero then it's clear that the folder does not exist, so we can go forward and use the MkDir function to create a new folder.
MkDir "c:\Documents" & "\" & folderName
Else
MsgBox ("The file already exists!") ' so otherwise the result is not 0 and that means that the folder already exists. We need to stop stop the function MkDir from running, announce the user and close the sub.
End If
End Sub
PS: If you have problems configuring, running this program you can contact me in the comments or privately :-) .
Comments
Post a Comment