?
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
?
473 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

777 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