0

I have a VBA script to import txt file. It works well on Excel 2013. On excel 2016, getopenfilename does not support argument anymore (excel crash). It works when removing all arguments of getopenfilename()

Any idea ?

 Sub Import_TXT()
On Error GoTo Err1

With Sheets("Sheet2").QueryTables.Add(Connection:= _
    "TEXT;" & GetTXT, Destination:=Sheets("Sheet2").Range("A1"))
    .Name = "logexportdata"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Exit Sub

Err1:
MsgBox "Data not imported. Error: " & Err.Number & vbCrLf & Err.Description

End Sub

Function GetTXT() As String

Dim filename__path As Variant


'  Get the filename
  filename__path = Application.GetOpenFilename(FileFilter:="TXT (*.txt), *.txt", Title:="Select txt file")

If filename__path = False Then Exit Function

GetTXT = filename__path
End Function
Community
  • 1
  • 1
Nicolas Weber
  • 1

1 Answers1

0

The solution I found is to replace by 

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text file", "*.txt", 1
.FilterIndex = 1
.Title = "Select txt file"
.Show
filename__path = .SelectedItems(1)
End With
Nicolas Weber
  • 1