Report File Size in Excel

[Shinho62]

Excel Query

Morning everyone, wondered if someone could point me in the right direction, i need excel to be able to report on the size of a file in a specific folder on a remote computer and list that size in excel

For Example

an access file at location

\\ComputerName\Storage\DB1.mdb

All the folders are shared so access to them is ok, but i have many locations on one machine and lots of machines

would appreciate it if someone could advsie of the correct formula to use

thanks in advance

 

[wigi]

Hello there

For the size in bytes in cell A1:

Range("A1").Value = CreateObject("Scripting.FileSystemObject").GetFile("YOUR FILE").Size

Change YOUR FILE with the correct file location.

 

[Shinho62]

Hi

thanks, new to excel is this for VBA or for excel itself ?

 

[wigi]

Hello

This is a macro for VBA. Go to VBA, for instance using Alt-F11. Insert a module in your workbook (or use an existing module if you already have one). Then paste this code:

Sub PutFileSize()
   Range("A1").Value = CreateObject("Scripting.FileSystemObject").GetFile("YOUR FILE").Size
End Sub

Then, close VBA. In Excel, call the macros window, for instance using Alt-F8. Run the macro and look at cell A1 for the result. Do not forget to insert your path and filename in the code.

Alternatively, the macro could also be a function that you can use in a worksheet. If you want that, please indicate so.

 

[Shinho62]

Wigi,

thats great works well on just the one file, but i have multiple
is there a way to have it read a column in excel and report those file sizes ?

 

[wigi]

In many many other topics, you will find examples of For ... Next loops. Please browse the other topics to see other code examples.

For instance:

Sub PutFileSize()
   Dim l As Long
   With CreateObject("Scripting.FileSystemObject")
     For l = 2 To Range("A" & Rows.Count).End(xlUp).Row
       Range("B" & l).Value = .GetFile(Range("A" & l).Text).Size
     Next
   End With
End Sub

Column A has files and paths, column B will contain the file size.

 

[Shinho62]

Wigi,

you have no idea how much work you have saved me, it works fanatastic
thank you so much

 

[Shinho62]

Hi Wigi,

hope this post finds you well

from the code you provided previously, of which is still working fab thanks, is there a way to get it to list all the documents properties as well as its filename

thanks in advance

Darren

 

[wigi]

Hello

I think that a Google search action will yield quite some code at once.

For instance:

http://www.mrexcel.com/forum/showthread.php?t=73458

received by using search terms:

excel vba get file properties

or similar terms.