In this long Excel tutorial, I'll show how we can organize and keep track of business vehicles, whether it's trucks or personal cars used by your company. This tutorial will consist of several separate websites that are linked. In the text below, see all parts of this tutorial on tracking kilometers or fuel consumption on a daily basis.
Note: Credit for all VBA macros, which are shown in this multiple tutorial belong to the authors and people who posted them on the internet. I have only performed a combination of various VBA macros to be functional for certain actions. For some VBA I have the specified source from where they are copied and for some not, so I apologize to the authors because the source is unknown to me and is in my collection.
You have to consider that this is just a simple example, but in essence it shows how you can arrange for high quality vehicle traffic costs. I believe you yourself know that a good organization is 50% of successful work. Of course, maybe I'm wrong, but this is my thinking and the way I would like to organize.
For example, we will take you to work in a company that uses a lot of vehicles daily, and you have the obligation to keep track of mileage, daily fuel consumption, vehicle servicing, and other vehicle-related parameters that require the company's bookkeeping.
So you need in Excel, organize records of the transport vehicles (trucks, cars, etc.) throughout the year for every day and every month. Below the list below, find out what actions I have used during this complete tutorial, which consists of several parts. So, this tutorial is based on one calendar year, 12 months, and records for each business day. Of course, for such things there are professional programs but for some reason you need to do it all in Excel.
To make the organization easier for you, some things need to be automated when you are working. Take for example that you have 100 traffic vehicles (trucks, cars) in your company. Your boss requires that you keep the same records for each vehicle. It also requires you to consider that at some point you want to know information about a particular vehicle, based on vehicle registration. It may also be required at the end of the year to have all the data in one workbook but so that each 'Sheet Tab' is named after the vehicle registration number. And who knows what else the boss wants (you know yourself what the bosses are :-)
The basic starting point for organizing work in Excel is the following:
- Creating 'Top Folders' and Multiple 'Sub Folders' on the 'D:\' hard disk partition (for the current year) where we will have 100 Excel files (since we have 100 traffic vehicles). Of course your ingenuity can organize sub-folders especially for trucks and especially for personal cars and the like.
- Create a workbook in the 'root top folder' named '2019-sumarum.xlsx' where we will enter all traffic vehicles and their necessary data. From this workbook we will pull data into other workbooks in sub-folders. So, this workbook will be used as a database, to which we will link other workbooks. Here you need to think well, that you will enter all the data into the database.
- Create a workbook template that will in principle be the basis for any traffic vehicle. The name of the Excel file should be named as the registration of a traffic vehicle. These workbooks will have 100 in one month for the records. Also within the workbook that serves as a template, the first worksheet should be appointed as the vehicle registration (ie. the same name as an Excel file). Also in the workbook there will be more worksheets in which we will keep a certain record. One worksheet will serve as 'helper' and I will have another temporary work sheet that will serve me to duplicate 100 times. Later, I will delete it in all the files in one move over VBA.
- Duplicate the template in 100 copies from the list. The list contains all the registration marks for all vehicles. For this purpose, we will use an additional worksheet that we will later delete. We will do this in one move by creating 100 Excel files in the same folder (copying the workbook 100 times).
- Rename Sheet1 to the workbook name in multiple workbooks that are in the same folder
- Deleting the last of the worksheet, in multiple workbooks from the same folder, which has served as a temporary worksheet, through which we make a duplication of the template.
- How to Remove VBA Modules in Multiple Workbooks. Since we no longer need the standard Module1 in each workbook, we will remove it using a VBA macro that will delete Module1 in every single workbook in the same folder.
- Copying finished templates into other sub-folders (by months). So after work done for the first month, we simply copy all 100 files into other sub-folders for other months of the year. All workbooks will be referenced to the base file in the 'root folder'.
- Backup - After the end of the business year, your boss may be required to archive all data without a formula. So you need to save multiple workbooks from all the sub-folders in the last year, but they do not contain formulas just values. So, we will create 'Backup Data' without formulas, so save all worksheets in each workbook as a values, in one step using VBA macros.
1. Create Top Folder, Sub Folder, and Sub-Sub Folder using VBA
In the first step of our organization list (see the list above) it is necessary to create multiple levels ie all folders and sub folders for the current year (for which we intend to keep track of). For this purpose we will use the VBA macro below. Copy this VBA macro to the standard Workbook Module and run it. After launching VBA macros, select the destination where you want to create folders and sub folders.
Copy this VBA macro to a VBA Standard Module1 with some newly opened workbook
'published by www.ic-ims.com
' Creates a folder structure using the text entered in column A, B and C of the active worksheet.
' If a cell in col B is populated a sub folder will be created in the previously created level 1 folder.
' If a cell in col C is populated, a sub-sub folder will be created in the previously created level 2 folder.
'Has a reference to Microsoft Scripting Runtime (VBE > Tools > References).
'published by rich007a
'Note! Set Tools -> References -> Microsoft Scripting Runtime
Dim fso As Scripting.FileSystemObject
Dim fldrStart As Scripting.Folder
Dim fldrL1 As Scripting.Folder
Dim fldrL2 As Scripting.Folder
Dim fldrL3 As Scripting.Folder
Dim strStartPath As String
Dim ws As Worksheet
Dim i As Long
strStartPath = GetFolder(Environ("USERPROFILE") & "\Desktop") 'after run vba you need select destination
If Right(strStartPath, 1) <> "\" Then strStartPath = strStartPath & "\"
Set fso = New FileSystemObject
Set fldrStart = fso.GetFolder(strStartPath)
Set ws = ActiveSheet
For i = 1 To ws.UsedRange.Rows.Count
If ws.Cells(i, "A").Value <> "" Then
Set fldrL1 = fldrStart.SubFolders.Add(ws.Cells(i, 1).Value)
Set fldrL2 = Nothing
Set fldrL3 = Nothing
ElseIf ws.Cells(i, "B").Value <> "" Then
If fldrL1 Is Nothing Then Stop
Set fldrL2 = fldrL1.SubFolders.Add(ws.Cells(i, "B").Value)
Set fldrL3 = Nothing
ElseIf ws.Cells(i, "C").Value <> "" Then
If fldrL2 Is Nothing Then Stop
Set fldrL3 = fldrL2.SubFolders.Add(ws.Cells(i, "C").Value)
Function GetFolder(Optional strInitialPath As String) As String
Dim fldrDiag As FileDialog
Dim strOutputPath As String
Set fldrDiag = Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the Folder where folder structure is to be created..."
.AllowMultiSelect = False
.InitialFileName = strInitialPath
If .Show <> -1 Then GoTo ExitPoint
strOutputPath = .SelectedItems(1)
GetFolder = strOutputPath
Set fldrDiag = Nothing
Before starting VBA macros, you must set Tools -> References -> Microsoft Scripting Runtime to enabled.
Creating multiple folders and subfolders using the BATCH command (BAT file)
If you do not want to waste time creating all the folders and subfolders in Excel, then you can do the same thing using the BAT file. This code below, copy it to Notepad. Save the file to 'Top Folder' such as 'create-subfolders' and after saving, change the extension from TXT to BAT. After starting, you have the same result.
SET RootDir=D:\The records of road vehicles\TopFolder\
FOR %%A IN (%SubA%) DO FOR %%B IN (%SubB%) DO IF NOT EXIST "%RootDir%\%%~A\%%~B" MD "%RootDir%\%%~A\%%~B"
And at the end of this first part of the tutorial, you can Download to your computer's ZIP on which I did this tutorial.
List all topics related to complete tutorials
- Creating Top Folders and Sub Folders in One Step
- How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
- Rename the first in the order of the worksheet, in multiple workbooks in the same folder
- How to remove the last Worksheet in multiple Workbooks in the same folder
- How to remove a particular VBA module in multiple workbooks in the same folder
- How to save all workbooks as values without formulas in all sub-folders
Click to continue tutorials: ------ >>>>>> The next part of the tutorial Item 2 from list above