1

OK, I've just written a very simple VBA script that goes off and grabs the file size of a file found at a specified location (edit update code from dscarr):

Public Function FileSize(path As String) As Variant 
    On Error GoTo Err_FileSize:
    Dim retVal As Variant 
    Dim filesys As Object 
    Dim file As Object 
    retVal = "" 
    Set filesys = CreateObject("Scripting.FileSystemObject") 
    Set file = filesys.GetFile(path) 
    retVal = file.Size 

    Exit_FileSize: On Error Resume Next 
    FileSize = retVal 
    Exit Function 

    Err_FileSize: retVal = "Error: " & Err.Description 
    Resume Exit_FileSize 
End Function

If I import this into a new workbook, save the document as a "Excel Macro-Enabled Workbook" alongside an empty file called readme.txt and then put "./readme.txt" in A1 and "=FileSize(A1)" in A2, A2 evaluates properly to the file size of readme.txt, 0 bytes. Great. If I then save the document, close and reopen it, I'm warned that macros are disabled. If I enable them, the content of A2 has changed to #VALUE! and nothing I do will make the function work again. Has anyone seen this before, can anyone point out the mistake that I'm making here?

edit: the issue seems to be caused by me using relative paths. I can't explain why it works for a new workbook, but not once saved and reopened, but using absolute paths solves the problem, which as dscarr identified, was a "File Not Found" issue.

Community
  • 1
  • 1
ninesided
  • 23,085
  • 14
  • 83
  • 107

2 Answers2

3

I duplicated your code in an Excel 2007 Macro Enabled workbook and found that it worked just fine with the following caveats

  1. It does not automatically update the value (e.g. run the FileSize function) when you open the workbook. This can be compensated for by adding some code to the Worrkbook_Open event handler that calculates the workbook.

  2. I get the #VALUE error when it cannot find the file that is specified. Indeed, when the file is missing or incorrectly named the line "Set file = filesys.GetFile(path)" throws the error number 53 "File Not Found". In this case the return value is never set because the line of code that sets it is never called. You could try setting a default value of "File Not Found" before actually attempting to retrieve the file size.

dscarr
  • 1,830
  • 2
  • 17
  • 21
  • ahh progress! excellent work my friend! The problem seems to be the resolution of relative paths. If I use a full path, it works perfectly (without re-evaluation on open, as you point out). How do I get the error code back? I tried to "watch" filesys object, but it just goes out of scope when the GetFile fails. – ninesided Jul 27 '11 at 21:04
  • Also, It appears that the cell is not re-calculating because the cells that it is dependent onhave not changed. To force Excel to recalculate everyting, reguardless of whether it has changed or not, add the statement "Application.CalculateFull" to the Workbook_Open event. – dscarr Jul 27 '11 at 21:08
  • The error number can be retrieved using the Err object. Example: Err.Number – dscarr Jul 27 '11 at 21:09
  • I took the liberty of rewriting your function a bit. – dscarr Jul 27 '11 at 21:10
  • is there any way I can get around the relative path issue? are you able to reproduce that behaviour, or am I going insane? – ninesided Jul 27 '11 at 21:10
  • Public Function FileSize(path As String) As Variant On Error GoTo Err_FileSize Dim retVal As Variant Dim filesys As Object Dim file As Object retVal = "" Set filesys = CreateObject("Scripting.FileSystemObject") Set file = filesys.GetFile(path) retVal = file.Size Exit_FileSize: On Error Resume Next FileSize = retVal Exit Function Err_FileSize: retVal = "Error: " & Err.Description Resume Exit_FileSize End Function – dscarr Jul 27 '11 at 21:11
  • Not sure about the relative path where the FileSystem Object is concerned. You can get the path of the Workbook with this statement "strSomeStringVariable = ThisWorkbook.Path" – dscarr Jul 27 '11 at 21:19
0

In Excel 2010, Go File->Options->Trust Center->Macro Settings and make sure the Enable option button has been selected.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130