File Size Function

[Guest]

Is there any way of determining the size of an external file from within a
spreadsheet? Ideally, what I would like to be able to do is something like

IF(FILESIZE(filename)>1000,do nothing,access file)

 

[Ardus Petus]

You need an UDF (User Defined Function).
Paste the following code into a module.
Then you can use the syntax you described.

HTH
--
AP

'-------------------------
Function FILESIZE(fname As String) As Variant
On Error GoTo errHandler
FILESIZE = FileLen(fname)
Exit Function

errHandler:
FILESIZE = CVErr(xlErrNA)
End Function
'-----------------------------

 

[Guest]

Many thanks!

You need an UDF (User Defined Function).
Paste the following code into a module.
Then you can use the syntax you described.

HTH
--
AP

'-------------------------
Function FILESIZE(fname As String) As Variant
On Error GoTo errHandler
FILESIZE = FileLen(fname)
Exit Function

errHandler:
FILESIZE = CVErr(xlErrNA)
End Function
'-----------------------------

 

[Guest]

I would like to have this also, but don't know much about vba. I can record
and use macros and locate my user defined functions, but I don't understand
the syntax of what you have...

I just want to see the file size... not matter what size of the file. How
would I write that?

 

[Rick Rothstein \(MVP - VB\)]

It would be better if you posted your replies in the original thread rather
than start a new thread like you did here.

Now, for how to use the code AP posted. Go into the VBA editor (Alt+F11),
click on Insert/Module on the menu bar, paste AP's code...

Function FILESIZE(fname As String) As Variant
On Error GoTo errHandler
FILESIZE = FileLen(fname)
Exit Function
errHandler:
FILESIZE = CVErr(xlErrNA)
End Function

into the code window that appears, then go to the spreadsheet and type this
into a cell...

=FILESIZE("c:\temp\test.txt")

substituting your own drive/path/filename for the sample one I show. Because
the function is located in a module and because it is not declared Private,
you can use it on your spreadsheet just like you do for built-in functions.
So, for your original question, just put this in a cell...

=IF(FILESIZE(filename)>1000,do nothing,access file)

and it will work as you want it to.

Rick