reference FIleLen funtion

[Guest]

I am attempting to run a query to return the size of a file.

When I use this code in a colum of a query : vba.FileSystem.FileLen([fileLoc])
I receive an error "Undefined function......bla.....bla....bla."

I have checked for missing function in the Tools_References and, all is O.K.

I can run the method from the VBA immediate window with the code:

?vba.FileSystem.FileLen("\\COMP-AP-x\Inetpub\Ftproot\Files\Orphaned\1\00\00\27\00002778.pdf")

The above works fine and returns the correct file size value.

Why cant I run this method from a Query??? I could do it before on anotehr
PC. Its really baffling me at present.

Please help.

 

[Duane Hookom]

You may need to create a user-defined function to use in the query:

Function LenFile(pstrFileName As String) As Long
LenFile = FileSystem.FileLen(pstrFileName)
End Function

In your query, use:
FileLength: LenFile([fileLoc])

 

[Guest]

Thank you very much.

However, I have one more issue.

If the file cannot be found for some reason I receive an error message to
halt the VBA procedure. Even when I code basic error handling into the
function I receive an error message for each doc that cannot be found.

Is their a method to ignore the errors and move onto the next record even if
the file address cannot be resolved?
--
Learning SQL and Access

You may need to create a user-defined function to use in the query:

Function LenFile(pstrFileName As String) As Long
LenFile = FileSystem.FileLen(pstrFileName)
End Function

In your query, use:
FileLength: LenFile([fileLoc])
--
Duane Hookom
MS Access MVP

I am attempting to run a query to return the size of a file.

When I use this code in a colum of a query :
vba.FileSystem.FileLen([fileLoc])
I receive an error "Undefined function......bla.....bla....bla."

I have checked for missing function in the Tools_References and, all is
O.K.

I can run the method from the VBA immediate window with the code:

?vba.FileSystem.FileLen("\\COMP-AP-x\Inetpub\Ftproot\Files\Orphaned\1\00\00\27\00002778.pdf")

The above works fine and returns the correct file size value.

Why cant I run this method from a Query??? I could do it before on
anotehr
PC. Its really baffling me at present.

Please help.

 

[Duane Hookom]

I can try something like:

Function LenFile(pstrFileName As String) As Long
If Len(Dir(pstrFileName))>0 Then
LenFile = FileSystem.FileLen(pstrFileName)
End If
End Function

--
Duane Hookom
MS Access MVP

Thank you very much.

However, I have one more issue.

If the file cannot be found for some reason I receive an error message to
halt the VBA procedure. Even when I code basic error handling into the
function I receive an error message for each doc that cannot be found.

Is their a method to ignore the errors and move onto the next record even
if
the file address cannot be resolved?
--
Learning SQL and Access

You may need to create a user-defined function to use in the query:

Function LenFile(pstrFileName As String) As Long
LenFile = FileSystem.FileLen(pstrFileName)
End Function

In your query, use:
FileLength: LenFile([fileLoc])
--
Duane Hookom
MS Access MVP

"sebastian stephenson" <[email protected]>
wrote
in message news:[email protected]...

I am attempting to run a query to return the size of a file.

When I use this code in a colum of a query :
vba.FileSystem.FileLen([fileLoc])
I receive an error "Undefined function......bla.....bla....bla."

I have checked for missing function in the Tools_References and, all is
O.K.

I can run the method from the VBA immediate window with the code:

?vba.FileSystem.FileLen("\\COMP-AP-x\Inetpub\Ftproot\Files\Orphaned\1\00\00\27\00002778.pdf")

The above works fine and returns the correct file size value.

Why cant I run this method from a Query??? I could do it before on
anotehr
PC. Its really baffling me at present.

Please help.