Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How create a VBA macro that given a path will get back file size and properties

Posted on 2011-09-09
5
Medium Priority
?
476 Views
Last Modified: 2012-05-12
Hello everyone,
what I'd like to create is a excel spread sheet that given a path in column for example \\servername\folder\subfolder\subfolder\subfolder\subfolder\filename will get me back as a results in some other columns of the same spread sheet file size creation date and modification date? May anyone do it or explain me in simple words how to create this VBA script? It would be better an example of the code itself... I've just started studying  to study VBA so let's say that I'm a newbie but I have some programming experience in other programming languages. Thank you in advance!  
0
Comment
Question by:nassio1985
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 6

Assisted Solution

by:theKashyap
theKashyap earned 200 total points
ID: 36511898
Sub Get_File_Size()

File1 = "c:\temp\Sample.txt"

MsgBox "The Size of the File is " & FileLen(File1) & " bytes"

End Sub

From here. :)
0
 
LVL 9

Assisted Solution

by:hitsdoshi1
hitsdoshi1 earned 600 total points
ID: 36511915
This macro will give you total directory listing with filesize, date etc.

Good Luck!
DirectoryTree.xls
0
 
LVL 5

Assisted Solution

by:slycoder
slycoder earned 1200 total points
ID: 36511988
I would create a macro/function:

1) access the Macro IDE with Alt+F11
2) insert a new module - Insert - Module
3) insert a new function - Insert - Procedure - type "GetFileProperty"and click type "Function" click on OK

Then replace the function with this:


Public Function GetFileProperty(myFile As String, myType As String) As String

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

   
    If UCase(Trim(myType)) = "CREATED" Then
        GetFileProperty = oFS.GetFile(myFile).DateCreated
    ElseIf UCase(Trim(myType)) = "MODIFIED" Then
        GetFileProperty = oFS.GetFile(myFile).DateLastModified
    ElseIf UCase(Trim(myType)) = "ACCESSED" Then
        GetFileProperty = oFS.GetFile(myFile).DateLastAccessed
    ElseIf UCase(Trim(myType)) = "SIZE" Then
        GetFileProperty = oFS.GetFile(myFile).Size
    Else
        GetFileProperty = "#Need Type#"
    End If
End Function





To use the function - in Column  A = you would have your list of files, then

just use the function in the appropriate columns:
=GetFileProperty(A1,"created")
=GetFileProperty(A1,"modified")
=GetFileProperty(A1,"accessed")
=GetFileProperty(A1,"size")



0
 
LVL 5

Accepted Solution

by:
slycoder earned 1200 total points
ID: 36512075
okay, okay . . .

I should teach best practices - a Select Case should be used for simplicity and less coding:


Public Function GetFileProperty(myFile As String, myType As String) As String

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Select Case UCase(Trim(myType))
        Case "CREATED"
            GetFileProperty = oFS.GetFile(myFile).DateCreated
        Case "MODIFIED"
            GetFileProperty = oFS.GetFile(myFile).DateLastModified
        Case "ACCESSED"
            GetFileProperty = oFS.GetFile(myFile).DateLastAccessed
        Case "SIZE"
            GetFileProperty = oFS.GetFile(myFile).Size
        Case Else
            GetFileProperty = "#Need Type#"
    End Select
End Function
0
 

Author Closing Comment

by:nassio1985
ID: 36512095
Thanks a lot, amazing job guys
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question