Report File Size in Excel

Shinho62

New Member
Joined
Mar 16, 2011
Messages
26
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;)
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello there

For the size in bytes in cell A1:

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

Change YOUR FILE with the correct file location.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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:

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

New Member
Joined
Mar 16, 2011
Messages
26

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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:

Code:
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

New Member
Joined
Mar 16, 2011
Messages
26

ADVERTISEMENT

Wigi,

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

Shinho62

New Member
Joined
Mar 16, 2011
Messages
26
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;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,089
Members
414,122
Latest member
eazyyexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top