If Statement issues

[smj_115]

Hi

I am hoping that someone can help me with an issue I am facing. I am trying to create code that will open a file when the file is present and if the file is not present for any reason a message box appears to inform the user to contact a team. I have tried various ways to get the code to function properly but on each occasion only one of the two options functions so for example the file opens if present but the message box doesnt open if not available.

The code I have at the moment is below, where attach1 is a hidden text box that holds the file pathname:


Private Sub Command13_Click()
Dim sFile As String
sFile = "Me.attach1"
If Dir(sFile) <> "" Then
Call OpenDocument(Me.attach1) ' Me.Attach1 holds the file pathname
Else
MsgBox "The file is not available - Please Speak to the Technical Team"
End If
End Sub

Im sure the solution will likely be obvious but unfortunately I am far from being an expert with vba coding.

Any help with this would be really appreciated.

Many thanks

Sam

[HaHoBe]

Hi Sam,

to check for the availability of a file I usually check the length of the Dir-Function: if it´s zero file not found else it would be equal to the number of characters of the filename.


If Len(Dir(sFile)) > 0 Then

Ciao,
Holger

[smj_115]

Hi Holger

Many thanks for replying to my thread. I tried this alternative as shown below but it results in the message box opening whether the file is present or not. Im not sure if there is a more elegant way of achieving the same outcome - any other ideas would be greatly appreciated.

Private Sub Command13_Click()
Dim sFile As String
sFile = "Me.attach1"


If Len(Dir(sFile)) > 0 Then
Call OpenDocument(Me.attach1) ' Me.Attach1 holds the file pathname
Else
MsgBox "The file is not available - Please Speak to the Technical Team"
End If
End Sub

Many thanks again

Sam

[HaHoBe]

Hi Sam,

in your code you declare a variable sFile and you assign a string ("Me.attach1") to it. If you want to relate to the value of the textbox named attach1 you should use


sFile = Me.Attach1
And you may use the variable to open the document as well. I wonder where you picked up the command OpenDocument.

Sorry for having missed the assignment of a string to the variable in my first answer.

Ciao,
Holger

[smj_115]

Hi Holger

Many thanks for your help with this - it now works. I cant remember where I found that line of code now.

Thanks again

Sam