Solved

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

Posted on 2011-09-09
5
432 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
5 Comments
 
LVL 6

Assisted Solution

by:theKashyap
theKashyap earned 50 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 150 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 300 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 300 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now