Rhyfelwr
Rhyfelwr

Reputation: 329

How do I modify this code to import text file via FileDialog?

Hi there I am trying to import a text file from a folder based on FileDialog from a set path? I have a code that imports a text file but it only opens up a generic C:\\ path, How should I modify the code below in order to open a folder for a specified path?

Sub ImportTextFile()
Dim fName As String, LastRow As Long

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("A" & LastRow))
            .Name = "sample"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "" & Chr(10) & ""
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
End Sub

Any help would be greatly appreciated!

I did the following alteration to the beginning of the code, but now I get "1004 Application Defined Error"

Sub ImportTextFile()
Dim fName As FileDialog, LastRow As Long

Set fName = Application.FileDialog(msoFileDialogOpen)
fName.InitialFileName = "\\olscmesf003\gcm_emea\TCU_REPORTS\APPS\Reports\Regional\Pointsec for PC  Web RH\2017\"
If fName = "False" Then Exit Sub

Upvotes: 2

Views: 70

Answers (1)

Petrichor
Petrichor

Reputation: 1065

Using ChDir before opening the file might help. I would comment, but don't have enough reputation so posting here.

e.g.

Sub ImportTextFile()
ChDir "C:/yourpath/folder/etc"
Dim fName As String, LastRow As Long
.... 

Upvotes: 3

Related Questions