logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali - Kako prikazati podatke za sve datoteke u direktoriju i poddirektorijima, Kako kreirati popis svih datoteka iz direktorija i pododrektorija, Kako kreirati list za putanju foldera i subfoldera kao i ime datoteke sa ekstenzijom, Prikaz naziva datoteka i imena subfoldera u glavnom folderu sa potpunom putanjom do datoteke, Popis svih datoteka u direktoriju koristeći isključivo Excel formule

Popis svih mapa i datoteka u direktoriju i pod-direktorijima




sponzor



Kako prikazati podatke za sve datoteke u direktoriju i pod-direktorijima

Vjerujem da ste imali potrebu saznati koje sve datoteke se nalaze u nekom specifičnom direktoriju ili poddirektoriju. ja osobno kroz izradu mojih web tutorijala imao sam takav slučaj. Postoji više načina kako možete kreirati popis svih datoteka u 'Direktoriju' ili 'Poddirektoriju'.

O drugim slučajevima koji koriste razne programe, pisao sam u tutorijalima na ovom linku Popis svih mapa i datoteka u nekom direktoriju. Međutim, ovdje se radi o programu Excel i kako koristiti VBA ili formule da bi kreirali popis svih mapa, imena datoteka, sa njihovim putanjama, veličinom datoteke, vrstom datoteke, datumom kreiranja datoteke, datumom modificiranja datoteke, datumom zadnjeg pristupa i slično.

U ovom Excel tutorijalu sa primjerima VBA makronaredbi, prikazao sam različite varijante kako možete prikazati popis svih datoteka sa putanjama ili bez njih, koje se nalaze u folderu ili subfolderu. Sve zasluge za kolekciju prikazanih VBA makronaredbe, pripadaju autorima istih.

Kako kreirati popis svih datoteka iz direktorija i pododrektorija

Na slici ispod uočite koje sve podatke vraćaju VBA procedure ispod 'Slika 1a'.

  1. File Name
  2. File Size
  3. File Type
  4. Date Created
  5. Date Last Accessed
  6. Date Last Modified

Slika 1

Popis svih datoteka u direktoriju i poddirektorijima

Da bi vam VBA makronaredba radila ispravno potrebno je uključiti Microsoft Scripting Runtime. Na slici ispod uočite kako uključiti ovu Referencu.

Da bi uključili Microsoft Scripting Runtime učinite slijedeće:

  1. Kliknite Tools na tekstualnom izborniku
  2. Izaberite 'References...' na padajućem izborniku
  3. U novootvorenom dijalog prozoru uključite box ispred 'Microsoft Scripting Runtime'.
  4. Kliknite OK button

Slika 1a

Microsoft Scripting Runtime

VBA makronaredba koja daje rezultate prikazane na prvoj slici (Slika 1) je slijedeća. Za ovaj prvi primjer imamo dvije VBA procedure koje su vezane jedna za drugu. Ove dvije procedure ispod ili VBA makronaredbe kopirajte u Module vašeg VBE (Visual Basic Editor) za Excel.

Sub ListAllFilesFromFolderAndSubfolder()
'Set a reference to Microsoft Scripting Runtime by using
'Tools --> References in the Visual Basic Editor (Alt+F11)
'Declare the variables

Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
Dim strTopFolderName As String
'Insert the headers for Columns A through F
Range("A1").Value = "File Name"
Range("B1").Value = "File Size"
Range("C1").Value = "File Type"
Range("D1").Value = "Date Created"
Range("E1").Value = "Date Last Accessed"
Range("F1").Value = "Date Last Modified"
strTopFolderName = "C:\Temp\" 'Assign the top folder to a variable
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the top folder
Set objTopFolder = objFSO.GetFolder(strTopFolderName)
'Call the RecursiveFolder routine
Call RecursiveFolder(objTopFolder, True)
'Change the width of the columns to achieve the best fit
Columns.AutoFit
End Sub

Sub RecursiveFolder(objFolder As Scripting.Folder, _
IncludeSubfolders As Boolean)
'Declare the variables
Dim objFile As Scripting.File
Dim objSubFolder As Scripting.Folder
Dim NextRow As Long
'Find the next available row
NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'Loop through each file in the folder
For Each objFile In objFolder.Files
Cells(NextRow, "A").Value = objFile.Name
Cells(NextRow, "B").Value = objFile.Size
Cells(NextRow, "C").Value = objFile.Type
Cells(NextRow, "D").Value = objFile.DateCreated
Cells(NextRow, "E").Value = objFile.DateLastAccessed
Cells(NextRow, "F").Value = objFile.DateLastModified
NextRow = NextRow + 1
Next objFile
'Loop through files in the subfolders
If IncludeSubfolders Then
For Each objSubFolder In objFolder.SubFolders
Call RecursiveFolder(objSubFolder, True)
Next objSubFolder
End If
End Sub

Kako kreirati list za putanju foldera i subfoldera kao i ime datoteke sa ekstenzijom

U ovom drugom primjeru, UDF funkcija kreira popis svih naziva datoteka i njihove putanje kao i veličinu svake datoteke koja se nalazi u folderu ili subfolderu. Ovu UDF funkciju ispod slike 2 kopirajte u Module vašeg VBE za Excel. Na radnom listu na kojem želite kreirati popis sa naslovima u stupcima upišite naslove Path, File name, Size. Workbook u koju ste smjestili ove dvije procedure ispod obavezno snimite u top folder iz kojeg želite vratiti popis svih datoteka sa njihovim putanjama i veličinama datoteke.

Ove VBA makronaredbe ispod vraćaju slijedeće podatke:

  1. Path
  2. File Name
  3. Size

Nadalje, uradite slijedeće korake:

  1. Selektirajte raspon ćelija u kojem očekujete popis. Imajte na umu da trebate selektirati broj redaka, koliko očekujete da može biti broj datoteka. Najbolje je selektirati veći broj redaka od očekivanog broja datoteka. (npr. Selektirajte 'A2:C50' raspon ćelija.
  2. Pritisnite tipku F2 (sada vam je aktivna prva ćelija selektiranog raspona)
  3. Ovu CSE formulu =ListFiles("C:\Temp") unesite u prvu ćeliju selektiranog raspona. Ova formula aktivira UDF funkciju, zbog toga vaša Workbook treba biti snimljena u formatu *.xls ili *.xlsm ili *.xlsb. Naravno vi možete postaviti drugi glavni folder iz kojeg želite vratiti popis svih datoteka.
  4. Pritisnite CTRL+SHIFT+ENTER odjednom
  5. Sada imate popis svih datoteka i njihove putanje

Slika 2

Prikaži popis svih datoteka i mapa u glavnoj mapi koristećiu Excel UDF

VBA makronaredba za UDF funkciju i popis svih datoteka sa njihovim putanjama. Ove dvije VBA procedure ispod vezane su jedna za drugu i rade u paru. Ovaj način korištenja UDF funkcije vraća popis iz foldera i subfoldera u kojem je smještena Workbook u kojoj se nalaze ove dvije procedure.

Public Tmp() As String
Function ListFiles(FolderPath As String)
Dim k As Long, i As Long
ReDim Tmp(2, 0)
If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
End If
Recursive FolderPath
k = Range(Application.Caller.Address).Rows.Count
If k < UBound(Tmp, 2) Then
MsgBox "There are more rows, extend user defined function"
Else
For i = UBound(Tmp, 2) To k
ReDim Preserve Tmp(UBound(Tmp, 1), i)
Tmp(0, i) = ""
Tmp(1, i) = ""
Tmp(2, i) = ""
Next i
End If
ListFiles = Application.Transpose(Tmp)
ReDim Tmp(0)
End Function

Function Recursive(FolderPath As String)
Dim Value As String, Folders() As String
Dim Folder As Variant, a As Long
ReDim Folders(0)
If Right(FolderPath, 2) = "\\" Then Exit Function
Value = Dir(FolderPath, &H1F)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If GetAttr(FolderPath & Value) = 16 Then
Folders(UBound(Folders)) = Value
ReDim Preserve Folders(UBound(Folders) + 1)
Else
Tmp(0, UBound(Tmp, 2)) = FolderPath
Tmp(1, UBound(Tmp, 2)) = Value
Tmp(2, UBound(Tmp, 2)) = FileLen(FolderPath & Value)
ReDim Preserve Tmp(UBound(Tmp, 1), UBound(Tmp, 2) + 1)
End If
End If
Value = Dir
Loop
For Each Folder In Folders
Recursive FolderPath & Folder & "\"
Next Folder
End Function

Kako napraviti popis svih imena datoteka i potpunom putanjom do foldera i subfoldera

Ovaj treći primjer je drugačiji od dva prethodna primjera. Na radnom listu postavljate putanju do glavnog foldera iz kojeg želite vratiti popis svih datoteka s nazivima i putanje za dotične datoteke. također imate mogućnost izbora, da li želite kreirati popis svih datoteka samo u glavnom folderu ili uključiti i subfoldere (vidi sliku ispod). Uočite na slici ispod da prvi red u kojem će se prikazati popis je 'redak 5'. Ova linija naredbe postavlja se u VBA makronaredbi. Također uočite da se uvjeti nalaze u 'B1' i 'B2' ćelijama. Workbook u kojoj se nalaze ove VBA procedure ne mora biti smještena u istom folderu iz kojeg povlačimo popis datoteka.

  • B1 ćelija --> Putanja do glavnog foldera
  • B2 ćelija --> Uvjet TRUE = Uključuje Top folder i subfolder
  • B2 ćelija --> Uvjet FALSE = Samo Top folder

Slika 3

Prikaz popisa svih datoteka i svih putanja za specifičan glavni folder

VBA procedure koje vraćaju rezultat sa Slike 3 su slijedeće. Ove dvije VBA procedure ispod vezane su jedna za drugu i rade u paru. Kopirajte ove u Module vašeg VBE za Excel.

Dim iRow
Sub ListPathNameFiles()
iRow = 5 'start first row where will be list
Call ListMyFiles(Range("B1"), Range("B2")) 'conditions
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 1
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub


Prikaz naziva datoteka i imena subfoldera u glavnom folderu sa potpunom putanjom do datoteke

U ovom četvrtom primjeru VBA makronaredbe prikazane ispod 'Slike 4', vraćaju popis imena svih datoteka sa ekstenzijama i naziva sub-direktorija, kao i dodatne informacije.

  1. File Name,
  2. Size,
  3. Modified Date,
  4. Last Accessed,
  5. Created Date,
  6. Full Path,
  7. Size

Uočite da u ovom primjeru VBA makronaredbe ne vraćaju popis datoteka iz subfoldera, već samo kreiraju popis subfoldera koji se nalaze u glavnom 'Top folderu'.

Slika 4

Popis svih datoteka i naziva subfoldera u određenom glavnom folderu

U ovom primjeru nalazi se paket VBA makronaredbi. Tako možete vidjeti da se tu nalaze dvije VBA procedure i jedna UDF funkcija. Sve ove VBA procedure povezane su i jedna bez druge ne rade ispravno.

Sub DirectoryListAllFiles()
'dimension variables
Dim objFSO As Object
Dim objFolder As Object
Dim objElement As Object
Dim objFile As Object
Dim strSourceFolder As String
Dim lngRow As Long
ToggleStuff False
strSourceFolder = BrowseForFolder 'call up the browse for folder routine
If strSourceFolder = "" Then Exit Sub
Cells.Select
Selection.ClearContents
With Range("A1:F1")
.Value = Array("File", "Size", "Modified Date", "Last Accessed", "Created Date", "Full Path", "Size")
.Interior.ColorIndex = 6 'cell background of the column header
.Font.Bold = True
.Font.Size = 12
End With
lngRow = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strSourceFolder)
For Each objElement In objFolder.SubFolders
Cells(lngRow, 1).Value = objElement.Name
'Cells(lngRow, 2).Value = Format(objElement.Size, "0,000") & " KB"
Cells(lngRow, 3).Value = objElement.DateLastModified
Cells(lngRow, 4).Value = objElement.DateLastAccessed
Cells(lngRow, 5).Value = objElement.DateCreated
Cells(lngRow, 6).Value = objElement.Path
lngRow = lngRow + 1
Next
For Each objElement In objFolder.Files
Cells(lngRow, 1).Value = objElement.Name
Cells(lngRow, 2).Value = Format(objElement.Size, "0,000") & " KB"
Cells(lngRow, 3).Value = objElement.DateLastModified
Cells(lngRow, 4).Value = objElement.DateLastAccessed
Cells(lngRow, 5).Value = objElement.DateCreated
Cells(lngRow, 6).Value = objElement.Path
lngRow = lngRow + 1
Next
Columns("A:F").AutoFit
Set objElement = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
ToggleStuff True 'turn events back on
End Sub

Sub ToggleStuff(ByVal x As Boolean)
Application.ScreenUpdating = x
Application.EnableEvents = x
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'VBAExpress.com..portion of Knowledge base submission
'codeguru.com

Dim ShellApp As Object
ReStart:
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
MsgBox "Not a valid folder"
GoTo ReStart
End Function

Popis svih datoteka u direktoriju koristeći isključivo Excel formule

U ovom petom primjeru ovog Excel tutorijala, pokazat ću kako koristeći Excel formule, možemo prikazati popis svih datoteka u 'Top folderu' gdje je smještena Workbook u koju želimo vratiti popis svih datoteka.

Dakle, Workbook smjestite u željeni folder, kopirajte navedene formule ispod prikazane u dotičnu workbook i imat ćete popis svih datoteka koje se nalaze u dotičnom specifičnom folderu. Ipak, prije nego što kopirate prikazane Excel formule, potrebno je da u Excel Name Manageru imenujete pomoćnu formulu.

Slika 5

Imenovanje formule u Excel Name Manager

Na 'Slika 5' iznad možete vidjeti rezultat imenovane pomoćne formule. Ako vam nije jasno, pogledajte detaljne upute na linku kako se imenuje formula u Name Manageru.

Dakle, ovu formulu ispod unesite u Name Manager (Excel će automatski dodati ime radnog lista na kojem ste postavili formulu).

  • Name: FileNameList
  • Refers to: =FILES($A$1)

U 'A1' ćeliju postavite slijedeću formulu:
=SUBSTITUTE(CELL("filename");RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("@";SUBSTITUTE(CELL("filename");"\";"@";LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename");"\";"")));1));"*")

U 'C2' ćeliju postavite slijedeću formulu (formulu kopirajte dole do željenog zadnjeg reda)
=IFERROR(INDEX(FileNameList;ROWS($A$1:A1));"")

Sada imate popis svih datoteka koje se nalaze u istom folderu kao i Workbook u koju ste postavili navedene formule. Workbook obavezno snimite u *.xls ili *.xlsm ili *.xlsb formatu.

Slika 5a

Popis svih datoteka u folderu u kojem je smještena Workbook

Nadam se da će kolekcija VBA makronaredbi i UDF funkcija, prikazanih u ovom Excel tutorijalu, pomoći vam da kreirate popis svih datoteka i putanja, kao i dodatnih informacija o datotekama, koje se nalaze u folderu i subfolderu.

I na kraju u prilogu ovog tutorijala imate mogućnost Download datoteke na kojoj sam radio ovaj tutorijal.




     

sponzor



COPYRIGHT © - 2005 - 2020 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela