Finding Out if a File Exists and Doing Something Else if it Does.

Question

I have attempted to write code to allow my report generator use compressed picture files to embed in the report if they exist, if not use the full size version.

This is what I came up with:-

lines in main module

thumbphoto ="Z:\SURVEYdemo\Compressed photographs\" & fldref & ".jpg"

Call FileExists(thumbphoto)

Code in Function

Public Function FileExists(thumbphoto As String)
On Error GoTo Err_Handler

If FileExists = True Then

    thumbphoto = "Z:\SURVEYdemo\Compressed Photographs\" & fldref & ".jpg"

Else

    thumbphoto = "Z:\SURVEYdemo\PHOTOGRAPHS\" & fldref & ".jpg"

End If

Exit_Err_Handler:
    Exit Function

Err_Handler:
MsgBox "Unable To Complete Process, Full Size Images Will Be Used", vbExclamation, "Error Message:"
GoTo Exit_Err_Handler

End Function

However it always defaults to the error  and is not finding the compressed image and doesn't use the full size image

Any help greatly appreciated.

Mick

Answer 1

First of all: Don't use global variables when possible. Then you don't check for the file existence. Use Dir(), e.g.

Public Function CompleteFileName(AFileName As String) As Boolean

  Const BASE_PATH As String = "Z:\SURVEYdemo\"
  Const NORMAL_PATH As String = "Compressed Photographs\"
  Const COMPRESSED_PATH As String = "PHOTOGRAPHS\"

  On Local Error GoTo LocalError

  Dim FileName As String

  GetFileName = False

  FileName = BASE_PATH & COMPRESSED_PATH & AFilename
  If Len(Dir(FileName)) > 0 Then
    GetFileName = True
    AFileName = FileName
    Exit Function
  End If


  FileName = BASE_PATH & NORMAL_PATH & AFilename
  If Len(Dir(FileName)) > 0 Then
    GetFileName = True
    AFileName = FileName
    Exit Function
  End If

  Exit Function

LocalError:
  MsgBox "Unable To Complete Process, Full Size Images Will Be Used", vbExclamation, "Error Message:"

End Function

It takes the file name without path as parameter and returns True and the full path with file name.

Answer 2

Thanks very much Stefan, I get what you mean

I omitted to mention my report is generated by a VBA function which creates a word document and this is where I will display the thumbnail picture.

I only have one minor problem;

I know your code generates the correct file reference, because when I put a msgbox in to show me what 'AFileName' is, it is correct but when your function jumps back into my main report generating function I have placed another msgbox and  'AFileName' is blank.

I thought as these were both public functions the value of the string would remain

Any Thoughts ?

SOLVED: just had to make  one of the strings a public string outside the function.  - Thanks again Stefan.