Post File Size In Cell

[WayneK]

Hello. I am fairly new to using VBA with Excel.

I have a Data Validation list of a filenames with a directory.
Once I make a filename selection from the list, I'd like the
file's size to be placed into a specific cell.

For example, if I chose from the Data Validaton List
"C:\Temp\Parts.xls", I'd like it's equivalent file size to be
placed into cell B1 on Sheet1.

What VBA code would accomplish this?

Thank you for your help.

Wayne

 

[Guest]

assume the dropdown is in B9 and the results go to C9

right click on the sheet tab and select view code. Put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sPath as String
On Error Resume next
spath = "C:\Myfolder\"
if Target.Address = "$B$9" then
if Target.Value <> "" then
s = sPath & target.Value
if dir(s) <> "" then
target.offset(0,1).Value = filelen(s)
end if
end if
end if
End Sub

 

[WayneK]

Thank you, Tom, for your helpful input.

Wayne