Facebook
From Lukasz, 6 Years ago, written in Visual Basic.
Embed
Download Paste or View Raw
Hits: 265
  1. Sub ImportTextFile()
  2. Dim fName As String, LastRow As Long
  3.  
  4. fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
  5. If fName = "False" Then Exit Sub
  6.  
  7. LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
  8.  
  9.     With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
  10.         Destination:=Range("A" & LastRow))
  11.             .Name = "sample"
  12.             .FieldNames = True
  13.             .RowNumbers = False
  14.             .FillAdjacentFormulas = False
  15.             .PreserveFormatting = True
  16.             .RefreshOnFileOpen = False
  17.             .RefreshStyle = xlInsertDeleteCells
  18.             .SavePassword = False
  19.             .SaveData = True
  20.             .AdjustColumnWidth = True
  21.             .RefreshPeriod = 0
  22.             .TextFilePromptOnRefresh = False
  23.             .TextFilePlatform = 437
  24.             .TextFileStartRow = 1
  25.             .TextFileParseType = xlDelimited
  26.             .TextFileTextQualifier = xlTextQualifierNone
  27.             .TextFileConsecutiveDelimiter = True
  28.             .TextFileTabDelimiter = False
  29.             .TextFileSemicolonDelimiter = False
  30.             .TextFileCommaDelimiter = True
  31.             .TextFileSpaceDelimiter = False
  32.             .TextFileOtherDelimiter = "" & Chr(10) & ""
  33.             .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
  34.                1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
  35.                1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
  36.                1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
  37.             .TextFileTrailingMinusNumbers = True
  38.             .Refresh BackgroundQuery:=False
  39.     End With
  40. Dim Rng As Range
  41.     Dim Loc As String
  42.     Dim Ch As Chart
  43.     With ActiveSheet
  44.         Set Rng = .Range("A1:B1").CurrentRegion
  45.         Loc = .Name
  46.         Set Ch = Charts.Add
  47.         With Ch
  48.             .ChartType = xlLineMarkers
  49.             .SetSourceData Source:=Rng
  50.             .Location Where:=xlLocationAsObject, Name:=Loc
  51.         End With
  52.     End With
  53. End Sub