Writing directory info to individual cells

[sdjones]

Hi
I am looking to write selected file group sizes to specific cells in an
Excel worksheet.
For example I'd like to have a monthly cell range, with multiple file
size details from various areas pasted into these cells.

Layout like this

January February March
Server1
files 1 1024kb 1096kb 2048kb
files 2 1044kb 3196kb 4048kb

Server 2
files 1 1000kb 1096kb 2048kb
files 2 1024kb 1096kb 2048kb

etc..

Can anyone suggest the best way to go about this?

 

[Guest]

How abouit this. It outputs the file name and size of all the files in the
current folder and each sub-folder to an excel sheet. Don't run this on "c:\"
!

If you're only interested in a single folder then remove the recursive call
"Call outputFileStructure(CStr(dr))".

Have a serch for "Scripting.FileSystemObject" there's heaps of sites that
will help with vbscript file manipulation.

'____________________________________________________________

Public upto As Integer

Public Sub runMe()
upto = 1
Call outputFileStructure("C:\forcasting")
End Sub

Public Sub outputFileStructure(dir As String)

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set Folder_C = filesys.GetFolder(dir)
Set Files_C = Folder_C.Files
Set subFolders_C = Folder_C.SubFolders

Range("A1") = "Name"
Range("B1") = "Size"

'loop through each Subfolder
For Each dr In subFolders_C
Call outputFileStructure(CStr(dr))
Next

'loop through each file
For Each file In Files_C
upto = upto + 1
Range("A" & upto) = CStr(file)
Range("B" & upto) = CStr(file.Size)
Next

End Sub

'____________________________________________________________

 

[Guest]

What about this:

'___________________________________________________________

Public Sub deleteEmptyRow()

For Each rw In Selection.Cells

If rw.Cells(1, 1) = "" Then
rw.Cells(1, 1).EntireRow.Delete
End If
Next

End Sub

'___________________________________________________________

Of course that requires the "test" column to be highlighted. It's best to
only highlight a single column on the rows that you want to test (ie not the
whole column). You could also replace:

For Each rw In Selection.Cells

with

For Each rw In range("A1:A100")

or some other range as needed. If you want to test for a particular value
just put that value between the " ".

- Rm

 

[sdjones]

Thanks I'll give them a try.

One more thing. Can a macro be assigned to a single cell for this
purpose? So, can I say cell A6 retrieves one file value, then cell A7
another, etc..?

Thanks again.

 

[Guest]

My delete row macro seems to have gotten attacked to the wrong thread, sorry
about that.


So you want to have a file name in a cell and then have excel find out how
big the file is?


- Rm

 

[sdjones]

Well, it's more a range of files really. Like a range of files, in a
particular directory, for a given month.

For example, iis log files. Getting the size of a month's worth of log
files, then writing them to a specific cell, for multiple logs on
multiple servers.

Thanks again

 

[Guest]

Ok well you just need to use similar code and turn it into a function. These
can be used on the worksheet like this

=filesize("c:\AUTOEXEC.BAT")

or

=folderSize("c:\windows")

'____________________________________________________________

Public Function fileSize(filePath As String) As String
On Error GoTo getmeouttahere

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set file = filesys.GetFile(filePath)

'Get the size of the file
fileSize = CStr(file.Size)
Exit Function

getmeouttahere:
fileSize = "Cannot find File """ & filePath & """"
End Function


Public Function folderSize(path As String)
On Error GoTo getmeouttahere

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set Folder_C = filesys.GetFolder(path)

'Get the size of the folder
folderSize = CStr(Folder_C.Size)

Exit Function

getmeouttahere:
folderSize = "Cannot find path """ & path & """"

End Function

'____________________________________________________________


- Rm

 

[sdjones]

You're a champion!

Thanks for that.

Ok well you just need to use similar code and turn it into a function. These
can be used on the worksheet like this

=filesize("c:\AUTOEXEC.BAT")

or

=folderSize("c:\windows")

'____________________________________________________________

Public Function fileSize(filePath As String) As String
On Error GoTo getmeouttahere

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set file = filesys.GetFile(filePath)

'Get the size of the file
fileSize = CStr(file.Size)
Exit Function

getmeouttahere:
fileSize = "Cannot find File """ & filePath & """"
End Function


Public Function folderSize(path As String)
On Error GoTo getmeouttahere

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set Folder_C = filesys.GetFolder(path)

'Get the size of the folder
folderSize = CStr(Folder_C.Size)

Exit Function

getmeouttahere:
folderSize = "Cannot find path """ & path & """"

End Function

'____________________________________________________________


- Rm