VBA - Open file from current directory

[Phosphonothioic]

I've created a macro to import a text file that the user can select. Problem is, the prompt starts on the desktop and they have to navigate to the proper directory/file manually.

Is there a way, when I use the GetOpenFilename application to start the search in the directory that the excel workbook is in?

Here's some of my code:

Sub Import()
Dim qry             As QueryTable
Dim strFilFulNam    As String
Dim strQryName      As String
Dim LastRow         As Long
Dim ContainerWB     As Workbook
    
    strFilFulNam = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _
                                               Title:="Select Textfile to Import", _
                                               MultiSelect:=False)
    
    If strFilFulNam = "False" Then
            Exit Sub
        'Else
            'Set ContainerWB = Workbooks.Open(strFilFulNam)
    End If
    
    strFilFulNam = "TEXT;" & strFilFulNam
        
    
    With ActiveSheet
    
        On Error GoTo ErrorCatch:
        
        'Append to previous data, if applicable
        If Range("A" & Rows.Count).End(xlUp).Row = 1 Then
            LastRow = Range("A" & Rows.Count).End(xlUp).Row
            Else
            LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
        End If
        
        'MsgBox "LastRow value is:" & LastRow 'verification test
        
        Set qry = .QueryTables.Add(Connection:=strFilFulNam, _
                                   Destination:=.Range("A" & LastRow))
        With qry
            .Name = "Filename"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = xlWindows
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(14, 12, 11, 6, 6, 9, 7, 7)
            .Refresh BackgroundQuery:=False
            'DoEvents
            'strQryName = .Name
            '.Delete
            'ThisWorkbook.Names(strQryName).Delete
        End With
    End With
Exit Sub
ErrorCatch:
MsgBox "Unexpected Error. Type:" & Err.Description
End Sub

 

[shg]

You could add as the first line of code

ChDir ThisWorkbook.Path
'or
ChDir ActiveWorkbook.Path