Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: Need Help With Input Box To Open File in Specified Path

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location

    Need Help With Input Box To Open File in Specified Path

    Greetings experts,

    I am trying to create an input box which will request for a file path to put into a code before running the code. Attached here is an example file:
    https://filedb.experts-exchange.com/incoming/2019/04_w14/1416803/example.xlsm

    Right now, I have created an example file with a button. When this button is pressed, the inputbox should appear.
    1.JPG

    After the user presses ok, the file path should be put in between the single apostrophes of this code:
    2.JPG

    If they didn't input anything, they should prompted to re-enter.

    After that, the form should appear. For subsequent uses, if there is already a file path saved, a different inputbox should appear asking if the saved file path is the correct file path which will also be able to change the file path if needed.

    Any help is much appreciated

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi ham123!
    Please refer to the attachment.
    Attached Files Attached Files

  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    A slight modification.
    Sub AddCodeToThisWorkbook()
        Dim i&, s$, s1$, pthTmp$
        s1 = "    Application.Run" & Chr(34) & "'" & pth & "'!ShowForm" & Chr(34)
        With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
            For i = 1 To .CountOfLines
                s = .Lines(i, 1)
                If s Like "*Application.Run*ShowForm""" Then
                    pthTmp = Split(s, "'")(1)
                    If pthTmp = "" Then
                        .ReplaceLine i, s1
                    Else
                       If MsgBox("Whether to replace the path?" & Chr(10) & pthTmp & Chr(10) & "to" & Chr(10) & pth, vbYesNo) = vbYes Then
                           .ReplaceLine i, s1
                       End If
                   End If
                   Exit For
                End If
            Next i
        End With
        ShowForm
    End Sub

  4. #4
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    Hi, thanks for your reply! I managed to do it like this.
    Sub OpenFile()Dim sFileName As Variant
    
    
     sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    If sFileName <> False And Dir(sFileName, vbNormal) <> "" Then
         Call ShowForm(sFileName)
    End If
    End Sub
    
    
    Sub ShowForm(ByVal FilePath As String)
       'Please add your error handlers
        Application.Run "'" & FilePath & "'!ShowForm"
    End Sub
    However when I press cancel at this step:
    Attachment 23988

    This appears:
    Attachment 23989

    Can you help me add in an "on error" procedure? As I do not want the user to see the run time error

  5. #5
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    if there is already a file path saved, a different inputbox should appear asking if the saved file path is the correct file path which will also be able to change the file path if needed.
    I did it according to your request. So I have to edit the code use vba.
    It must be very simple if it's not needed.
    Last edited by 大灰狼1976; 04-03-2019 at 12:54 AM.

  6. #6
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    I can't download you attachment at #4, but error handle is simple.
    like below:
    Sub ShowForm()
        On Error GoTo ErrHandle
        Application.Run "'" & FilePath & "'!ShowForm"
        Exit Sub
    ErrHandle:
        MsgBox "Error!"
    End Sub

  7. #7
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    I tried adding it in like this but it still showed it
    Sub OpenFile()Dim sFileName As Variant
    
    
     sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    If sFileName <> False And Dir(sFileName, vbNormal) <> "" Then
         Call ShowForm(sFileName)
    End If
    
    
    On Error GoTo ErrHandle
        Application.Run "'" & FilePath & "'!ShowForm"
        Exit Sub
    ErrHandle:
        MsgBox "Error!"
    End Sub
    
    
    Sub ShowForm(ByVal FilePath As String)
       'Please add your error handlers
        Application.Run "'" & FilePath & "'!ShowForm"
        
    
    
    End Sub
    I have also tried it in the the Showform code as well

  8. #8
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Sub OpenFile()
    Dim sFileName As Variant
    
     sFileName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    If sFileName <> False And Dir(sFileName, vbNormal) <> "" Then
         Call ShowForm(sFileName)    'Because error handle is not added here. 
    End If
    End Sub
    
    Sub ShowForm(ByVal FilePath As String)
        On Error GoTo ErrHandle
        Application.Run "'" & FilePath & "'!ShowForm"
        Exit Sub
    ErrHandle:
        MsgBox "Error!"
    End Sub

  9. #9
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Please refer to the attachment.
    You can change the code what you want. but error handle is ok.
    Attached Files Attached Files

  10. #10
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    Okay, thank you! can you give me an example of the file path?
    I tried putting in my own file path but it didn't work..
    For example: C:\Users\ESPZYONG\Documents\Projects\HM\HM01

  11. #11
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Put these two files in the same path, then run the code.
    Attached Files Attached Files

  12. #12
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    Hi, I managed to figure out the file path but I get this error
    Capture.JPG

  13. #13
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Trust Center Chooses Trust or change to your posted code.

  14. #14

  15. #15
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    Now it says this.. Maybe you can take a look at the file I am trying to open?
    Capture.JPG

  16. #16
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    test my posted files at #11

  17. #17
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    It says the same error as in #12

  18. #18
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    I explained the solution about error #12 at #13.
    I will post the picture later.

  19. #19
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location

  20. #20
    VBAX Regular
    Joined
    Mar 2019
    Posts
    30
    Location
    I think I will go for my original method and I will debug the on error procedure. I will post the final solution here later.

Tags for this Thread

Posting Permissions

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