What is wrong is that you have an argument to the Workbook_Open routine, and
if this is to be a startup event then the ThisWorkbook Workbook_Open
doesn';t take an argument.

Remove the argument, and hardcode the startup directory in the event
procedure.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"briank" wrote in message
...
Bob,
I'm sure that my basic background in VBA is hampering my understanding on
this so please bear with me. When I insert your code into the StartUp tab
and click run I get a popup box that asks me for a macro name. It is my
intention to run this code upon the opening of the spreadsheet without the
user knowing it. With all of this in mind, what am I doing wrong? Your
thoughts?
Brian

"Bob Phillips" wrote:

This works

Sub Workbook_Open(dir_test)
Dim fs, f, s, n, file
Dim filespec As String
Dim count As Integer
count = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(dir_test)
For Each file In f.Files
If file.Type = "Microsoft Excel Worksheets" Then
s = file.Size
n = file.Name
Range("A" & count) = n
Range("B" & count) = s & " KB"
count = count + 1
End If
Next file
End Sub

use like

Workbook_Open "C:\myTest"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"briank" wrote in message
...
A few weeks back I posted a question about getting code to list file
sizes
in
a worksheet upon startup. A response was posted and even worked a few
times
but since then I have not been able to keep this code working. For
what it
is
worth, the code is in a worksheet called StartUp and ideally I want
the
files
to started populating at cell A1. Any assistance on what I am doing
incorrectly would be appreciated.

Sub Workbook_Open(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