Kako organizirati vođenje evidencije o prometnim vozilima u Excelu

U ovom dugačkom tutorijalu prikazat ću kako možemo sami organizirati i voditi evidenciju o prometnim vozilima u tvrtki bilo da se radi o kamionima ili osobnim automobilima koje koristi vaša kompanija. Ovaj tutorijal sastojat će se od više zasebnih web stranica koje su povezane. U tekstu ispod uočite koje sve dijelove ima ovaj tutorijal o evidenciji prijeđenih kilometara ili potrošnji goriva na dnevnoj bazi.
Napomena: Zasluga za sve VBA makronaredbe koje su prikazane u ovom višestrukom tutorijalu pripadaju autorima i osobama koje su ih objavile na internetu. Ja sam izvršio samo kombinaciju raznih VBA makronaredbi da bi bile funkcionalne za određene radnje. Za neke VBA imam navedeni izvor odakle su kopirane a za neke ne, stoga se izvinjavam autorima jer mi je izvor nepoznat a nalazi se u mojoj kolekciji.

Morate uzeti u obzir da je ovo samo jednostavan primjer, ali u biti prikazuje kako se možete organizirati za kvalitetnu evidenciju troškova prometnih vozila. Vjerujem da i sami znate da je dobra organizacija 50% uspješnog posla. Naravno, možda ja griješim, ali ovo je moje razmišljanje i način na koji bi ja organizirao posao.

Uzet ćemo za primjer, da vi radite u kompaniji koja dnevno koristi puno vozila a vi imate obavezu da vodite evidenciju prijeđenih kilometara, potrošnje goriva na dnevnoj bazi, servisiranje vozila i ostale parametre vezane uz prometna vozila koje zahtjeva knjigovodstvo kompanije.

Dakle, vi trebate u Excelu organizirati evidenciju o prometnim vozilima (kamionima, automobilima i sl) tijekom cijele godine za svaki dan i svaki mjesec. U popisu ispod uočite koje radnje sam koristio tijekom izrade ovog kompletnog tutorijala, koji se sastoji od više dijelova. Dakle, ovaj tutorijal bazira se na jednoj kalendarskoj godini, 12 mjeseci i evidencija za svaki radni dan. Naravno, za ovakve stvari postoje profesionalni programi ali eto iz nekih razloga vi trebate sve to raditi u Excelu.

Da bi vi sebi olakšali organizaciju, neke stvari prilikom rada potrebno je automatizirati. Uzmimo za primjer da u kompaniji imate 100 prometnih vozila (kamiona, automobila). Vaš šef zahtjeva da za svako vozilo vodite istu evidenciju. Također od vas traži da uzmete u obzir da u određenom trenutku želi znati informacije o pojedinom vozilu, baziranom na registraciji vozila. također, možda na kraju godine bude zahtijevao da sve podatke ima u jednoj radnoj knjizi ali tako da svaki Sheet Tab bude imenovan registracijskom oznakom vozila. I tko zna što još šef želi (znate i sami kakvi su šefovi :-).

Osnovna polazna točka za organizaciju rada u Excelu je slijedeća:

1. Kreiranje 'Top Foldera' i višestrukih 'Subfoldera' na 'D:\' particiji hard diska (za trenutnu godinu) u kojima ćemo imati 100 Excel datoteka (jer imamo 100 prometnih vozila). Naravno vaša domišljatost može organizirati subfoldere posebno za kamione i posebno za osobne automobile i slično.
2. Izrada radne knjige u 'root folderu' pod nazivom '2019-sumarum.xlsx' u koju ćemo unijeti sva prometna vozila i njihove potrebne podatke. Iz ove radne knjige povlačiti ćemo podatke u ostale radne knjige u sub-folderima (pod-mapama). Dakle, ovu radnu knjigu koristiti ćemo kao bazu podataka, na koju ćemo linkati ostale radne knjige. Ovdje dobro trebate promisliti, koje sve podatke ćete unijeti u bazu.
3. Kreirati predložak radne knjige koja će u principu, biti osnova za svako prometno vozilo. Naziv Excel datoteke treba biti imenovan kao registracija prometnog vozila. Ovakvih radnih knjiga imat ćemo 100 u jednom mjesecu za evidenciju. Također unutar radne knjige koja služi kao predložak, prvi radni list treba biti imenovan kao registracija vozila (dakle, isto ime kao i Excel datoteka). Također unutar radne knjige nalazit će se još radnih listova u kojima ćemo voditi određenu evidenciju. Jedan radni list služit će nam kao 'pomoćni' (helper Sheet) a imat ću još jedan privremeni radni list koji će mi poslužiti za dupliciranje 100 puta. Kasnije ću ga obrisati u svim datotekama u jednom potezu preko VBA.
4. Dupliciranje predloška u 100 primjeraka iz popisa. Na popisu se nalaze sve registracijske oznake za sva vozila. Za ovu svrhu koristit ćemo dodatni radni list koji ćemo kasnije obrisati. Ovu radnju izvršit ćemo tako da u jednom potezu kreiramo 100 Excel datoteka u istom folderu (kopiranje radne knjige 100 puta).
5. Preimenovanje Sheet1 u naziv radne knjige u višestrukim radnim knjigama koje se nalaze u istoj mapi
6. Brisanje zadnjeg radnog lista u višestrukim radnim knjigama iz iste mape, koji nam je služio kao privremeni radni list, preko kojeg smo izvršili dupliciranje predloška.
7. Kako ukloniti VBA module u višestrukim radnim knjigama. S obzirom da nam više ne treba standardni Module1 u svakoj radnoj knjizi, uklonit ćemo ga koristeći VBA makronaredbu koja će u jednom potezu obrisati sve Module1 u svakoj radnoj knjizi koja se nalazi u istoj mapi.
8. Kopiranje gotovih predložaka u ostale sub foldere (po mjesecima). Dakle nakon odrađenog posla za prvi mjesec, jednostavno kopiramo svih 100 datoteka u ostale sub foldere za ostale mjesece u godini. Sve radne knjige referencirat će se na baznu datoteku u root folderu.
9. Backup - nakon završetka radne godine, možda vaš šef bude tražio da sve podatke arhivirate bez formula. Dakle potrebno je spremiti višestruke radne knjige iz svih sub-foldera u prošloj godini ali tako da ne sadrže formule već samo vrijednosti. Dakle, izradit ćemo 'Backup podataka' bez formula tj. spremiti kao vrijednosti, u jednom potezu koristeći VBA makronaredbe.

1. Kreiranje Top Foldera, Sub foldera i Sub-Sub foldera koristeći VBA

U prvom koraku našeg popisa organizacije (vidi popis iznad) potrebno je kreirati višestruke nivoe tj. sve foldere i su bfoldere za trenutnu godinu (za koju namjeravamo voditi evidenciju). Za ovu svrhu koristiti ćemo VBA makronaredbu ispod. Kopirajte ovu VBA makronaredbu u standard Module radne knjige i pokrenite je. Nakon pokretanja VBA makronaredbe izaberite destinaciju, gdje želite kreirati foldere i subfoldere.

Ovu VBA makronaredbu kopirajte u novo otvorenu radnu knjigu u standardni Module1.

Option Explicit
'published by www.ic.ims.hr
Sub CreateFolderStructure()
' 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
'https://answers.microsoft.com/
'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)
End If
Next i
End Sub
Function GetFolder(Optional strInitialPath As String) As String
Dim fldrDiag As FileDialog
Dim strOutputPath As String

Set fldrDiag = Application.FileDialog(msoFileDialogFolderPicker)
With fldrDiag
.Title = "Select the Folder where folder structure is to be created..."
.AllowMultiSelect = False
.InitialFileName = strInitialPath
If .Show <> -1 Then GoTo ExitPoint
strOutputPath = .SelectedItems(1)
End With

ExitPoint:
GetFolder = strOutputPath
Set fldrDiag = Nothing
End Function

Obavezno prije pokretanja VBA makronaredbe postavite Tools -> References -> Microsoft Scripting Runtime na enabled.

Kreiranje višestrukih mapa i podmapa koristeći BATCH naredbu (BAT datoteka)

Ako ne želite gubiti vrijeme kreiranja svih mapa i podmapa u Excelu, tada istu radnju možete odraditi koristeći BAT datoteku. Ovaj kod ispod, kopirajte u Notepad. Spremite datoteku u 'Top Folder' kao npr. 'create-subfolders' i nakon spremanja promijenite ekstenziju iz TXT u BAT. Nakon pokretanja imate isti rezultat.

@ECHO ON
SET RootDir=D:\The records of road vehicles\TopFolder\
SET SubA=2019-god
SET SubB=2019-1,2019-2,2019-3,2019-4,2019-5,2019-6,2019-7,2019-8,2019-9,2019-10,2019-11,2019-12
FOR %%A IN (%SubA%) DO FOR %%B IN (%SubB%) DO IF NOT EXIST "%RootDir%\%%~A\%%~B" MD "%RootDir%\%%~A\%%~B"
EXIT

I na kraju ovog prvog dijela tutorijala, na ovom linku možete spremiti na vaše računalo radnu knjigu na kojoj sam radio ovaj tutorijal.

Popis svih tema vezane za kompletan tutorijal

1. Kreiranje Top Foldera i Sub foldera u jednom potezu
2. Kako kopirati radnu knjigu 100 puta i stvoriti višestruke radne knjige iz popisa u 'A' stupcu
3. Preimenovanje prvog po redoslijedu radnog lista u višestrukim radnim knjigama unutar jedne mape
4. Kako ukloniti posljednji radni list u višestrukim radnim knjigama unutar jedne mape
5. Kako ukloniti određeni VBA modul u višestrukim radnim knjigama unutar jedne mape
6. Kako spremiti sve radne knjige kao vrijednosti bez formula u svim pod-mapama

Kliknite za nastavak tutorijala: ------>>>>>> Slijedeći dio tutorijala