Solved

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

Posted on 2011-09-09
5
471 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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

696 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