File Size Code not working

[briank]

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

 

[Gary Brown]

Without using a reference to FSO, you can revise this code. The reference
may be goofing things up.

'/===============================================/
Sub GetMyFileNames()
Dim i As Long, y As Long
Dim r As Long
Dim strFileName As String
Dim strPath As String
Dim strExtension As String

r = 1
With Application.FileSearch
.NewSearch
.LookIn = ActiveWorkbook.Path & "\"
.Filename = "*.xls"
.SearchSubFolders = True
.Execute
For i = 1 To .FoundFiles.Count
strFileName = ""
strPath = ""
For y = Len(.FoundFiles(i)) To 1 Step -1
If Mid(.FoundFiles(i), y, 1) = "\" Then
Exit For
End If
strFileName = _
Mid(.FoundFiles(i), y, 1) & strFileName
Next y
strPath = Left(.FoundFiles(i), _
Len(.FoundFiles(i)) - Len(strFileName))
strExtension = ""
For y = Len(strFileName) To 1 Step -1
If Mid(strFileName, y, 1) = "." Then
If Len(strFileName) - y <> 0 Then
strExtension = Right(strFileName, _
Len(strFileName) - y)
strFileName = Left(strFileName, y - 1)
Exit For
End If
End If
Next y
Cells(r, 1) = .FoundFiles(i)
ActiveSheet.Hyperlinks.Add _
Anchor:=Cells(r, 1), Address:=.FoundFiles(i)
Cells(r, 2) = strPath
Cells(r, 3) = strFileName
Cells(r, 4) = strExtension
Cells(r, 5) = FileLen(.FoundFiles(i))
Cells(r, 6) = FileDateTime(.FoundFiles(i))
r = r + 1
Next i
End With

'formatting
Rows("1:1").Insert Shift:=xlDown
Range("A1").FormulaR1C1 = "Hyperlink"
Range("B1").FormulaR1C1 = "Path"
Range("C1").FormulaR1C1 = "Filename"
Range("D1").FormulaR1C1 = "File Ext"
Range("E1").FormulaR1C1 = "Size"
Range("F1").FormulaR1C1 = "Date"
Columns("A:F").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.Zoom = 75

End Sub
'/===============================================/

HTH,
Gary Brown

 

[Bob Phillips]

Without using a reference to FSO, you can revise this code. The reference
may be goofing things up.

How?

 

[briank]

Gary,
Your code works great! TY for your assistance.

 

[Bob Phillips]

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)

 

[Gary Brown]

Just a thought...but this way, you don't have to worry about it. I try to
follow the John Walkenbach/Chip Pearson examples of 'Don't use references
unless you absolutely have to.
Gary

 

[Bob Phillips]

But he did use it later in Set f = fs.GetFile(filespec)

The code seemed a bit of a mish-mash, but he did use FSO.

--

HTH

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

 

[Gary Brown]

He did...but I didn't. That's what I meant. Sorry for confusing the issue.
Sincerely,
Gary Brown

 

[Bob Phillips]

LOL. I did notice that.

Regards

Bob

He did...but I didn't. That's what I meant. Sorry for confusing the issue.
Sincerely,
Gary Brown

 

[briank]

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]

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]

Bob, thank you for your assistance.

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)