Hi ,
I am working on a macro to import text files into excel .but when I am executing it, I am getting an error message "Invalid procedure call or argument" at
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FileName _
, Destination:=Range("$A$1"))
pls help...
Sub ImportDataWithUI()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
Dim delimit
' Set up list of file filters
Filt = "Text Files (*.txt),*.txt,(*.csv),*.csv,(*.dat),*.dat," & _
"All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 1
' Set the dialog box caption
Title = "Select a File to Import"
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If
delimit = InputBox("Please specify the delimiter", "Delimiter", " ")
' Display full path and name of the file
' MsgBox "You selected " & FileName
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FileName _
, Destination:=Range("$A$1"))
.Name = FileName
.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 = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = True
.TextFileOtherDelimiter = delimit
'.Textfiledelimiter = "|"
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Bookmarks