How to make a folder or directory in Excel using VBA?

Microsoft Excel has an option that helps to make many more options and make the ones it already had become more powerful, this is thanks to the use of Macros and creating them from Excel VBA. Knowing how to use this aspect is already part of an advanced level, since it requires knowledge of programming and codes on the part of the person.

You can download Excel from its official website  or also use the online version of Microsoft Office 365. Today we will teach you how to create folders using macros and VBA.

Index( )

1. How to create a folder with VBA in Excel?
2. How to save all the macros in a single Excel workbook to make the folders?
   1. On my Windows PC
   2. with a mac
3. What procedure to follow to create a folder with a copy of the Excel workbook?
4. What is the routine that disables the message in the Excel status bar?

How to create a folder with VBA in Excel?

Although the process of creating folders on a PC is not complicated at all, it can become tedious if the work we are doing requires creating several copies of the same file and saving it in different folders, so doing it manually does represent a hassle that would take time.

Excel’s Visual Basic Application, in addition to the many other functions it has, allows you to generate a macro to save an Excel file (in this case) within specific folders, very useful if the previous case is exposed, in which you have to save different copies of a spreadsheet workbook, handy in case you need to share your Excel files  with someone else.

By entering some code in the VBA window and executing it in the spreadsheet by pressing a button that we can create ourselves, the macro will automatically save the workbook we are working on in different folders, all with different names, to avoid mishandling.

How to save all the macros in a single Excel workbook to make the folders?

When doing a macro in a spreadsheet, in the VBA code window, in the taskbar of this dialog, you will see a floppy disk symbol that if you press it will save all the macros in that workbook. However, in order to do this, your sheet must have a configuration that we will explain below.

On my Windows PC

It is important to save the sheet with a special format so that the macros and the use of VBA have an effect and can be saved correctly in your Excel workbook and for this you have to do the following:

1. Click on “File” in your Excel document.
2. Then go to the “Save As” option and choose the “Macro-enabled Excel Workbook” option.
3. The File Explorer of your computer will appear, choose the path and the folder where you want your project to be saved.
4. Finish the process by clicking “OK” or “Save” and that’s it.

with a mac

The process of a MacOS computer is similar to that of Windows and it is also mandatory to do it so that the macros that you are going to create have an effect on your spreadsheet. That said, follow the steps given in the previous section. Remember that in Excel, as well as in Word, you can also add two links or hyperlinks in a cell , you just have to specify which are the phrases with those hyperlinks.

What procedure to follow to create a folder with a copy of the Excel workbook?

Ok, once you have saved your macro enabled file , open the document again and proceed to enter all the data and produce the list or record, if you already have all this, then skip this above. To open the Excel VBA, you go to the “Developer” tab and click on “Visual Basic”; the keyboard shortcut “Alt + F11” also works.

In the Visual Basic window of Excel we go to the “Insert” tab and then to the “Module” option, if you wish, you can change the name of this module, we recommend it so that you can have an easier way to identify it in case you are going to originate more macros within the same spreadsheet.

In the new dialog box that appeared thanks to Module, we are going to write the following code, for each “semicolon” ​​in it, it means that it must be written on a separate line:

• Sub Create_Folder_And_Save(); Dim sPath As String; Dim sFolderName As String; Dim sPathSeparator As String; Dim sCurrentBookName As String; sCurrentWorkbookName = Application.ActiveWorkbook.Name; sPathSeparator = Application.PathSeparator; sPath = Application.ActiveWorkbook.Path; sFolderName = CStr(Format(Date, ‘dd-mm-yyyy’)) _; & ‘-‘ & CStr(Format(Time, ‘hh-mm-ss’)); If Dir(sPath & sPathSeparator & sFolderName, vbDirectory) = Empty Then; MkDir (sPath & sPathSeparator & sFolderName); EndIf; Application.ActiveWorkbook.SaveCopyAs Filename:=sPath _; & sPathSeparator & sFolderName & sPathSeparator & sCurrentBookName; Application.StatusBar = ‘A copy was saved to: ‘ & sPath _; & sPathSeparator & sFolderName & sPathSeparator & sCurrentBookName; End Sub

What is the routine that disables the message in the Excel status bar?

If it is the case that you want to deactivate the entire effect of the macro executed in the previous section, you can use the following code that we will give you below; the input method is the same. Likewise, we remind you that with Excel it is also possible to import coordinates and addresses to Google , this is the code to deactivate the routine :

• Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range); If Not (Application.StatusBar = False) Then; Application.StatusBar = False; EndIf; End Su