File Size Function
 

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)

File Size Function
 

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

"Mike McLellan" a écrit dans le
message de news: ...
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)

File Size Function
 

Many thanks!

"Ardus Petus" wrote:

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

"Mike McLellan" a écrit dans le
message de news: ...
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)

File Size Function
 

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?


"Ardus Petus" wrote:

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

"Mike McLellan" a écrit dans le
message de news: ...
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)

File Size Function
 

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


"jenn" wrote in message
...
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?


"Ardus Petus" wrote:

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

"Mike McLellan" a écrit dans le
message de news: ...
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)