Thursday, May 31, 2012 11:29 AM
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"
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"
thumbphoto = "Z:\SURVEYdemo\PHOTOGRAPHS\" & fldref & ".jpg"
MsgBox "Unable To Complete Process, Full Size Images Will Be Used", vbExclamation, "Error Message:"
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.
Thursday, May 31, 2012 11:42 AM
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.
Thursday, May 31, 2012 2:49 PM
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.