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
Solved

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

790 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