VBA to find file attributes

[Neil Jordan]

I want to use VBA in Access 2003 to return the file size of a specified
file.

Is there an easy way without resorting to yet more dlls?

Thanks
Neil

 

[Arvin Meyer]

Filelen(strPathIn As String) As Long

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Nikos Yannacopoulos]

Neil,

Yes, there is. Here's some simple code I wrote to collect file
attributes in a folder into a table. If you only want to check a file's
size, without storing, then you can get rid of the recordset operation.

Sub Read_Files_In_Folder()
Dim rs As DAO.Recordset
strSQL = "DELETE * FROM List_Of_Files"
CurrentDb.Execute strSQL
Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder("C:\temp\")
Set fls = fldr.Files
Set rs = CurrentDb.OpenRecordset("List_Of_Files")
For Each fl In fls
rs.AddNew
rs.Fields(0) = fl.Name
rs.Fields(1) = fl.DateCreated
rs.Fields(2) = fl.DateLastModified
rs.Fields(3) = fl.Size \ 1024 + 1
rs.Update
Next fl
rs.Close
Set rs = Nothing
End Sub

HTH,
Nikos

 

[Neil Jordan]

Thanks for that.

Is there any way to find the size of a known filename, or will I just have
to loop through until the filename matches the one I want?

Neil

 

[Nikos Yannacopoulos]

Neil,

It comes down to:

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\MyFolder\MyFile.ext")
lngFileFize = f.Size \ 1024 + 1

(divided by 1024 for size in KB, because f.Size is returned in bytes)

Yet, for just the size of a particular file, Arvin's suggestion is simpler!

Nikos

 

[FSt1]

Nikos,
with all those sets, you might need to throw in a few more
DIMs to get that to run.
neat code though

 

[Neil Jordan]

Great

Thanks to all - works a treat now!

Neil

Nikos,
with all those sets, you might need to throw in a few more
DIMs to get that to run.
neat code though

 

[Nikos Yannacopoulos]

Depends on whether one has Require variable Declaration checked. I know
I should, but I usually don't, especially not while testing.