User supplied path to open text file with vba

[isaacren94]

Hello all I am having trouble with a code to let the user set a path in VBA

Right now I have

Dim filePathRop_Gamma As Variant
    filePathRop_Gamma = ThisWorkbook.Worksheets("Setup Tab").Range("K4").Value 'user supplied path
'code to open the file
Workbooks.OpenText Filename:=filePathRop_Gamma, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=False

It keeps giving me a 1004 error code and I was wondering if there was any special rules for the naming scheme.

Thanks for the help

 

[JoeMo]

What's in Worksheets("Setup Tab").Range("K4") when you get the error?

 

[isaacren94]

C:\Users\username\Desktop\folder\really long file name-numbers-morenumbers-export.las

<tbody>
</tbody>

 

[JoeMo]

C:\Users\username\Desktop\folder\really long file name-numbers-morenumbers-export.las

<tbody>
</tbody>

I'm looking for characters that are forbidden for use in file names - the part in red is not helpful for that.
You cannot use the following characters anywhere in a file name: / \ : * ? " < > |

 

[LMacD]

I am not able to post links yet.. or I would.

But you want them to browse and select a file by clicking it. Especially if it is a long name. This also ensures the file exists.

Search MrExcel using "

Browse a file in excel vba"​

 

[isaacren94]

The part in red might not be the same letters but it has the same number of spaces. An example would be (aaa aaaaaaa aaa ##-######-#aaa.las) no special characters other than spaces and "-"

 

[isaacren94]

Would I be able to save that file name with a spreadsheet and not have to select it again?

 

[Domenic]

The following macro will first try to open the file specified in cell K4. If it can't open the file, it will prompt the user to select the file. If the user selects a file, the path and filename of the selected file is entered in K4, and then the file is opened. If the user cancels, it exits the sub.

    Dim filePathRop_Gamma As Variant
    
    filePathRop_Gamma = ThisWorkbook.Worksheets("Setup Tab").Range("K4").Value 'user supplied path
    
    On Error Resume Next
    Do
        'code to open the file
        Workbooks.OpenText _
            Filename:=filePathRop_Gamma, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=True, _
            Tab:=True, _
            Semicolon:=False, _
            Comma:=False, _
            Space:=True, _
            Other:=False, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
            TrailingMinusNumbers:=False
        If Err = 0 Then Exit Do
        filePathRop_Gamma= Application.GetOpenFilename(FileFilter:="LAS Files (*.las), *.las",Title:="Select LAS file")
        If filePathRop_Gamma = False Then Exit Sub
        ThisWorkbook.Worksheets("Setup Tab").Range("K4").Value = filePathRop_Gamma
        Err = 0
    Loop
    On Error GoTo 0

Hope this helps!

 

[isaacren94]

This Works beautifully, thank you.