About Us

briank

Bob, thank you for your assistance.

"Bob Phillips" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Compiling Excel VBA code increases workbook file size!	Romuald[_3_]	Excel Programming	6	March 1st 05 08:23 AM
Code is working in one file but not in another.	shilps	Excel Programming	1	April 19th 04 12:20 PM
File size problem: standard remedies not working.	[email protected]	Excel Programming	2	December 31st 03 08:10 PM
Can we write VBA code to set all column/row's size back to default size?	Charles Williams	Excel Programming	0	July 8th 03 03:56 PM
Can we write VBA code to set all column/row's size back to default size?	Earle	Excel Programming	0	July 8th 03 02:46 PM

Menu

About Us