Post File Size In Cell
 

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

Post File Size In Cell
 

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

--
Regards,
Tom Ogilvy


"WayneK" wrote:

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

Post File Size In Cell
 

Thank you, Tom, for your helpful input.

Wayne