import a text file with user input

[Corbana]

i want to import a text file with the user choosing twhere the file comes from not just having to rename the file they want to import

'
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Jim\import.txt" _
        , Destination:=Sheets("Sheet2").Range("A1"))

is what a record gives me but i want to change this so that the user can choose the name of the file or prefrably get the chance to navigate to it.

ta

 

[Von Pookie]

Sub test()
Dim txtfile As Variant

With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Add "Text files", "*.txt", 1 'default to show only txt files
    
    If .Show = -1 Then 'show file select dialog
        For Each txtfile In .SelectedItems
            'code to import selected text file
            With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & txtfile, Destination:=Sheets("Sheet2").Range("A1"))
            'any other code you want to run
        Next txtfile
    End If
End With

End Sub

 

[Corbana]

when i run this I get a

compile error

next without for.

any ideas

 

[Von Pookie]

Oops! I forgot an End With in there I took that out of a project I just did at work, so apparently just missed it. Try:

Sub test() 
Dim txtfile As Variant 

With Application.FileDialog(msoFileDialogFilePicker) 
    .Filters.Add "Text files", "*.txt", 1 'default to show only txt files 
    
    If .Show = -1 Then 'show file select dialog 
        For Each txtfile In .SelectedItems 
            'code to import selected text file 
            With ActiveSheet.QueryTables.Add(Connection:= _ 
            "TEXT;" & txtfile, Destination:=Sheets("Sheet2").Range("A1")) 
                 'any other code you want to run
            End With
        Next txtfile 
    End If 
End With 

End Sub