Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litlib demo: Directory and file contents in Excel (Read 2228 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litlib demo: Directory and file contents in Excel
15.12.09 at 09:08:37
 
litLIB demo: Directory and file contents in Excel
 
To demonstrate the use of the file system functions in the litLIB power functions library for Excel this directory viewer was created. Without writing one line of macro or VBA code this file was created. The demo file is attached to this topic so you can try this out yourself!  
 
Using litLIB's FileNames function, the directory file list is created. The FileContents function is used to display the contents of the file, each line in a separate cell. The size and file date are retrieved in Excel using the FileLength and FileDateTime functions. For a more in depth explanation see the text below the image.
 

 
Retrieving the directory listing in Excel
In cells C6 to C10 the FileNames function is used to retrieve the names of the files in the directory. To create this function select C6 to C10 and type =FileNames(D3,E11) Ctrl+Alt+Enter (it is an array formula)
Note that the directory name is picked up from D3 and that the listing is started at the file number displayed in E11. E11 is linked to the scroll bar that was created from the Excel Forms toolbar.
 
Retrieve the file properties in Excel
In cells D6 to D10 the file size is shown. Enter the following formula in D6 and copy it to D7:D10
=FileLen($D$3 & "\"& C6)
Note that the director name is in D3 and that the file name is in C6. The \ is added as the directory separator to build the full path to the file.
Similarly in cells E6 to E10 the file modification date/time is shown. It was created using the formula:
=FileDateTime($D$3 & "\"& C6)
 
Showing file contents in Excel
The file contents of the selected file are shown using the FileContents formula. The array formula variant was used. To apply it select cells C14 to C26 and enter the formula below. Press Ctrl+Shift+Enter when done:
 =FileContents(D3 &"\"& INDEX(C6:C10,D13,1),G27)
 
A little trickery was performed using the index function. First the FileContents function was used to retrieve the contents of the file. The first parameter is the full name of the file including the path in D3. The INDEX function is used to retrieve the selected file. The option buttons are lined to cell G27. The number in G27 is used to pick the correct file name from the range C6:C10.
 
 
Basically this was all that is needed to create this file viewer. Enter a new directory name and all cells will be recalculated instantly.
 
Further reading:
The litLIB project pages
www.oraxcel.com/projects/litlib
Back to top
« Last Edit: 15.12.09 at 09:28:17 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1