Consulting

Results 1 to 5 of 5

Thread: If Statement issues

  1. #1
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    3
    Location

    If Statement issues

    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

  2. #2
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    Hi Sam,

    to check for the availability of a file I usually check the length of the Dir-Function: if its 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

  3. #3
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    3
    Location
    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

  4. #4
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    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
    Last edited by HaHoBe; 01-08-2021 at 06:38 AM.

  5. #5
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    3
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •