Writing directory info to individual cells
 

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?

Writing directory info to individual cells
 

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

'_________________________________________________ ___________




" wrote:

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?

Writing directory info to individual cells
 

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


" wrote:

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?

Writing directory info to individual cells
 

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.

Writing directory info to individual cells
 

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



" wrote:

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.

Writing directory info to individual cells
 

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

Writing directory info to individual cells
 

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

getmeouttahe
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

getmeouttahe
folderSize = "Cannot find path """ & path & """"

End Function

'_________________________________________________ ___________


- Rm



" wrote:

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

Writing directory info to individual cells
 

You're a champion!

Thanks for that.


Robert Mulroney wrote:
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

getmeouttahe
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

getmeouttahe
folderSize = "Cannot find path """ & path & """"

End Function

'_________________________________________________ ___________


- Rm



" wrote:

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