Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Properties
How can I reference the size of a spreadsheet (not necessarily the active
one) in an automated way? Ideally I want a cell in my active file to show the size (i.e. 50k) and will change each time the active spreadsheet is opened as the external file grows in size. Any thoughts? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Properties
This gives you names and sizes for all .xls files in the directory
containing the open workbook: Sub dir_test() Dim fs, f, s Dim filespec As String Dim count As Integer count = 1 Set fs = CreateObject("Scripting.FileSystemObject") filespec = Dir(ActiveWorkbook.Path & "\*.xls") Do While filespec < "" Set f = fs.GetFile(filespec) s = f.Size n = f.Name Range("A" & count) = n Range("B" & count) = s & " KB" filespec = Dir count = count + 1 Loop End Sub For one specific file: Sub dir_test() Dim fs, f Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("H:\excel\Dir1.xls") Range("A1") = f.Name Range("B1") = f.Size & " KB" End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Properties
I should have added, double click ThisWorkbook in the VB Editor and use:
Private Sub Workbook_Open() to run code when a workbook is opened. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File properties | Excel Worksheet Functions | |||
File Properties | Excel Discussion (Misc queries) | |||
File Properties | Excel Discussion (Misc queries) | |||
Get File Properties | Excel Programming | |||
Set properties in an XLS file.. | Excel Programming |