Microsoft Excel has an option to help create more options and make it already more powerful thanks to using macros and creating them from Excel VBA. Knowing how to use this aspect is already part of the advanced level as it requires human programming and coding knowledge.
You can download Excel from the official website or use the online version of Microsoft Office 365.Today we will teach you how Generate folders using macros and VBA.
How to create a folder in Excel using VBA?
While the process of creating a folder on a PC is not complicated at all, it can become tedious if we are doing work that requires creating multiple copies of the same file and saving them in different folders, so manually It’s really troublesome to create. Take your time.
The Visual Basic application for Excel, among many other features it has, allows Generate a macro to save an excel file (In this case) in a specific folder, useful if you have to save different copies of your spreadsheet workbook before making it public, useful if you have to share your Excel file with others.
by entering a Code in a VBA window And execute it in the spreadsheet by pressing a button that we can create ourselves, the macro will automatically save the workbooks we are working on in different folders, all with different names to avoid misplacement.
How can I save all macros in a single Excel workbook to create a folder?
When executing a macro in a spreadsheet, in vba code window, In the taskbar of this dialog you will see a floppy disk symbol which if you press it will save all the macros in the book. However, in order to do this, your sheet must have the configuration we will explain below.
on my Windows PC
It is important to save the worksheet in a special format so that the use of macros and VBA will have an effect and be saved correctly in the Excel workbook, for this you must do the following:
- Click File in the Excel document.
- Then go to the «Save As» option and select the «Macro-Enabled Excel Workbook» option.
- Your computer’s file explorer will appear, select the path and folder where you want to save the project.
- Complete the process by clicking OK or Save and that’s it.
with mac
The process for MacOS computers is similar to the process for Windows computers, and it must be done so that the macros you will create have an effect on your spreadsheet. That said, follow the steps given in the previous section.Remember that in Excel as well as Word you can also add two links or hyperlinks in one cell, you just need to specify what the sentence is with those hyperlinks.
What procedure should I follow to create a folder with a copy of an Excel workbook?
ok, after saving Your macro-enabled file, open the document again and continue to enter all the data and generate the list or record, skip this one above if you already have all of them. To open Excel VBA, go to the «Developer» tab and click «Visual Basic»; the keyboard shortcut «Alt+F11» also works.
inside Excel Visual Basic window We go to the «Insert» tab, then to the «Module» option, you can change the name of this module if you want, we recommend it so you can identify it more easily in case you initiate the same More macros in spreadsheets.
In the new dialog that appears due to Module, we will write the following code, for each «semicolon» in it, meaning 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); in case; Application.ActiveWorkbook.SaveCopyAs filename:=sPath_; & sPathSeparator & sFolderName & sPathSeparator & sCurrentBookName; Application.StatusBar=»Copy saved to:» & sPath _; & sPathSeparator & sFolderName & sPathSeparator & sCurrentBookName; end sub
What is the routine to disable messages in Excel’s status bar?
If you want to deactivate the entire effect of the macro executed in the previous section, you can use the following code which we will give you below; the input method is the same.Likewise, we remind you that it is also possible to import coordinates and addresses into Google using Excel, which is code to disable routine:
- Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range); if not (Application.StatusBar = False) then; Application.StatusBar = False; in case; end sub