insert file size into a table

[Mike]

I have the following module which reads all the names of a specified
folder into a table. I would like to also get the file size of those
files into a new column in my table. So far I found the filelen
function, but I don't know where to put the command in the module and
how to get the module to insert the size in the same table as the
filenames.

Any help appreciated. Thanks.

Here's the module I'm using:

Option Compare Database

Function LocateFile(strFileName As String)
Dim vItem As Variant
Dim db As DAO.Database
Dim strsourcefilepath As String
Dim Mysize
strsourcefilepath = DLookup("[source]", "filepath")
Set db = CurrentDb
With Application.FileSearch
.Filename = strFileName
.LookIn = strsourcefilepath
.SearchSubFolders = True
.Execute

CurrentDb.Execute "DELETE * from Import_songs_tbl",
dbFailOnError

For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Import_songs_tbl (Filename) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With
MsgBox "Done.", vbInformation
Set db = Nothing
End Function

 

[Guest]

I may be wrong but I think you're just looking for the .Size.

For Each vItem In .FoundFiles
FileSize = .Size

Then add the FileSize variable into your db.Execute line. Of course you'll
have to Dim it first (Dim FileSize as String).


HTH
Bryan

 

[Mike]

I may be wrong but I think you're just looking for the .Size.

For Each vItem In .FoundFiles
FileSize = .Size

Then add the FileSize variable into your db.Execute line. Of course you'll
have to Dim it first (Dim FileSize as String).

HTH
Bryan

I have the following module which reads all the names of a specified
folder into a table. I would like to also get thefilesizeof those
files into a new column in my table. So far I found the filelen
function, but I don't know where to put the command in the module and
how to get the module to insert thesizein the same table as the
filenames.

Any help appreciated. Thanks.

Here's the module I'm using:

Option Compare Database

Function LocateFile(strFileName As String)
Dim vItem As Variant
Dim db As DAO.Database
Dim strsourcefilepath As String
Dim Mysize
strsourcefilepath = DLookup("[source]", "filepath")
Set db = CurrentDb
With Application.FileSearch
.Filename = strFileName
.LookIn = strsourcefilepath
.SearchSubFolders = True
.Execute

CurrentDb.Execute "DELETE * from Import_songs_tbl",
dbFailOnError

For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Import_songs_tbl (Filename) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With
MsgBox "Done.", vbInformation
Set db = Nothing
End Function- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

I just tried to enter the filesize you suggested. Usually VBA suggests
entries after setting a "." . There is no filesize or size available
in the filesearch. So I tried to dim the filesize first and tried
FileLen. But here I have the problem that I don't know how to hand
over the filename.
As I mentioned before, I'm new to VBA and all my knowledge so far is
from this discussion group. I can somewhat understand the code above
and I could get it to work (and so far I'm really proud that I didn't
have to hardcode my filepath but instead found a way to have this
editable in a table in my database), but that's basically all.
I would really appreciate any other suggestion that would the FileLen
make work in the code.

Anyway, thanks for the help. At least it brought me to the FileLen
function.

 

[Mike]

I may be wrong but I think you're just looking for the .Size.

For Each vItem In .FoundFiles
FileSize = .Size

Then add the FileSize variable into your db.Execute line. Of course you'll
have to Dim it first (Dim FileSize as String).

HTH
Bryan

I have the following module which reads all the names of a specified
folder into a table. I would like to also get thefilesizeof those
files into a new column in my table. So far I found the filelen
function, but I don't know where to put the command in the module and
how to get the module to insert thesizein the same table as the
filenames.
Any help appreciated. Thanks.
Here's the module I'm using:
Option Compare Database
Function LocateFile(strFileName As String)
Dim vItem As Variant
Dim db As DAO.Database
Dim strsourcefilepath As String
Dim Mysize
strsourcefilepath = DLookup("[source]", "filepath")
Set db = CurrentDb
With Application.FileSearch
.Filename = strFileName
.LookIn = strsourcefilepath
.SearchSubFolders = True
.Execute
CurrentDb.Execute "DELETE * from Import_songs_tbl",
dbFailOnError
For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Import_songs_tbl (Filename) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With
MsgBox "Done.", vbInformation
Set db = Nothing
End Function- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

I just tried to enter the filesize you suggested. Usually VBA suggests
entries after setting a "." . There is no filesize orsizeavailable
in the filesearch. So I tried to dim the filesize first and tried
FileLen. But here I have the problem that I don't know how to hand
over the filename.
As I mentioned before, I'm new to VBA and all my knowledge so far is
from this discussion group. I can somewhat understand the code above
and I could get it to work (and so far I'm really proud that I didn't
have to hardcode my filepath but instead found a way to have this
editable in a table in my database), but that's basically all.
I would really appreciate any other suggestion that would the FileLen
make work in the code.

Anyway, thanks for the help. At least it brought me to the FileLen
function.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Made a nice discovery yesterday. As the code I posted earlier imports
all filenames complete with path and filename, I can just use the
filelen function in a make table query. I already had this query in
place and just have been looking for a way to also get the file sizes
to filter out files smaller than 1 mb. Works really nice.