software

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)gt;1000,do nothing,access file)

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
'-----------------------------

quot;Mike McLellanquot; gt; a écrit dans le
message de news: ...
gt; Is there any way of determining the size of an external file from within a
gt; spreadsheet? Ideally, what I would like to be able to do is something
gt; like
gt;
gt; IF(FILESIZE(filename)gt;1000,do nothing,access file)
Many thanks!

quot;Ardus Petusquot; wrote:

gt; You need an UDF (User Defined Function).
gt; Paste the following code into a module.
gt; Then you can use the syntax you described.
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; '-------------------------
gt; Function FILESIZE(fname As String) As Variant
gt; On Error GoTo errHandler
gt; FILESIZE = FileLen(fname)
gt; Exit Function
gt;
gt; errHandler:
gt; FILESIZE = CVErr(xlErrNA)
gt; End Function
gt; '-----------------------------
gt;
gt; quot;Mike McLellanquot; gt; a écrit dans le
gt; message de news: ...
gt; gt; Is there any way of determining the size of an external file from within a
gt; gt; spreadsheet? Ideally, what I would like to be able to do is something
gt; gt; like
gt; gt;
gt; gt; IF(FILESIZE(filename)gt;1000,do nothing,access file)
gt;
gt;
gt;

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?quot;Ardus Petusquot; wrote:

gt; You need an UDF (User Defined Function).
gt; Paste the following code into a module.
gt; Then you can use the syntax you described.
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; '-------------------------
gt; Function FILESIZE(fname As String) As Variant
gt; On Error GoTo errHandler
gt; FILESIZE = FileLen(fname)
gt; Exit Function
gt;
gt; errHandler:
gt; FILESIZE = CVErr(xlErrNA)
gt; End Function
gt; '-----------------------------
gt;
gt; quot;Mike McLellanquot; gt; a écrit dans le
gt; message de news: ...
gt; gt; Is there any way of determining the size of an external file from within a
gt; gt; spreadsheet? Ideally, what I would like to be able to do is something
gt; gt; like
gt; gt;
gt; gt; IF(FILESIZE(filename)gt;1000,do nothing,access file)
gt;
gt;
gt;

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(quot;c:\temp\test.txtquot;)

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)gt;1000,do nothing,access file)

and it will work as you want it to.

Rickquot;jennquot; gt; wrote in message
...
gt;I would like to have this also, but don't know much about vba. I can record
gt; and use macros and locate my user defined functions, but I don't
gt; understand
gt; the syntax of what you have...
gt;
gt; I just want to see the file size... not matter what size of the file. How
gt; would I write that?
gt;
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt;gt; You need an UDF (User Defined Function).
gt;gt; Paste the following code into a module.
gt;gt; Then you can use the syntax you described.
gt;gt;
gt;gt; HTH
gt;gt; --
gt;gt; AP
gt;gt;
gt;gt; '-------------------------
gt;gt; Function FILESIZE(fname As String) As Variant
gt;gt; On Error GoTo errHandler
gt;gt; FILESIZE = FileLen(fname)
gt;gt; Exit Function
gt;gt;
gt;gt; errHandler:
gt;gt; FILESIZE = CVErr(xlErrNA)
gt;gt; End Function
gt;gt; '-----------------------------
gt;gt;
gt;gt; quot;Mike McLellanquot; gt; a écrit dans le
gt;gt; message de news: ...
gt;gt; gt; Is there any way of determining the size of an external file from
gt;gt; gt; within a
gt;gt; gt; spreadsheet? Ideally, what I would like to be able to do is something
gt;gt; gt; like
gt;gt; gt;
gt;gt; gt; IF(FILESIZE(filename)gt;1000,do nothing,access file)
gt;gt;
gt;gt;
gt;gt;