Importing text to excell without especifying path

  • Thread starter Thread starter cecilia12345
  • Start date Start date
C

cecilia12345

i do this to import a file, but i have to specify the path:
With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\DATA.txt", _
Destination:=Range("A1"))
.Name = "DATA"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "="
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2)
.TextFileFixedColumnWidths = Array(8, 13, 7, 11, 16)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

how do i do this so the user can choose the document, a different pat
and name every time?
 
Sub GetFile()
Dim fName as String
fName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt")
if fName <> "False" then

With ActiveSheet.QueryTables.Add(Connection:= _
fname, _
Destination:=Range("A1"))
..Name = "DATA"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..TextFilePromptOnRefresh = False
..TextFilePlatform = 850
..TextFileStartRow = 1
..TextFileParseType = xlFixedWidth
..TextFileTextQualifier = xlTextQualifierDoubleQuote
..TextFileConsecutiveDelimiter = False
..TextFileTabDelimiter = True
..TextFileSemicolonDelimiter = False
..TextFileCommaDelimiter = False
..TextFileSpaceDelimiter = False
..TextFileOtherDelimiter = "="
..TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2)
..TextFileFixedColumnWidths = Array(8, 13, 7, 11, 16)
..TextFileTrailingMinusNumbers = True
..Refresh BackgroundQuery:=False
End With
End If
End Sub
 
are you sure that fName should be string? because every time i get t
the
-->
" With ActiveSheet.QueryTables.Add(Connection:= _
NombreArchivo, _
Destination:=Range("A1")) " - part

it stops!!!!
the alert it gives me says : "error defined by application or object
 
oh now i realized what the problem was.. i just had to add
NombreArchivo = "TEXT;" & NombreArchivo
after the condition...!!!
well, just in case somebody wanted to know...
 
Can someone please explain what the outcome of the "nombre...."
modification was here. I've been playing around with it but cant seem
to get this macro working!

Many thanks
 
Turn on you macro recorder and do
Data=>Get External Data and select import text file . . .
 
Back
Top