User supplied path to open text file with vba

isaacren94

New Member
Joined
May 18, 2019
Messages
6
Hello all I am having trouble with a code to let the user set a path in VBA

Right now I have
Code:
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
What's in Worksheets("Setup Tab").Range("K4") when you get the error?
 
Upvote 0
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]C:\Users\username\Desktop\folder\really long file name-numbers-morenumbers-export.las[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]C:\Users\username\Desktop\folder\really long file name-numbers-morenumbers-export.las
[/TD]
[/TR]
</tbody>[/TABLE]
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: / \ : * ? " < > |
 
Upvote 0
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 "
 
Upvote 0
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 "-"
 
Upvote 0
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.

Code:
    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!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top