Macro for importing and formatting a text file

   1: Sub QueryfileImport()

   2: '

   3: ' QueryfileImport Macro

   4: ' To automate the process of importing  and formating query result text file in desired format

   5: '

   6:     ActiveWindow.Zoom = 80

   7:     Dim strFileName

   8:     ChDir "C:\Users\Nauman Khan\Desktop\"

   9:     strFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

  10:     If TypeName(strFileName) <> "Boolean" Then

  11:         With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _

  12:                    strFileName, Destination:=Range _

  13:             ("$A$1"))

  14:             .FieldNames = True

  15:             .RowNumbers = False

  16:             .FillAdjacentFormulas = False

  17:             .PreserveFormatting = True

  18:             .RefreshOnFileOpen = False

  19:             .RefreshStyle = xlInsertDeleteCells

  20:             .SavePassword = False

  21:             .SaveData = True

  22:             .AdjustColumnWidth = True

  23:             .RefreshPeriod = 0

  24:             .TextFilePromptOnRefresh = False

  25:             .TextFilePlatform = 65001

  26:             .TextFileStartRow = 1

  27:             .TextFileParseType = xlDelimited

  28:             .TextFileTextQualifier = xlTextQualifierDoubleQuote

  29:             .TextFileConsecutiveDelimiter = False

  30:             .TextFileTabDelimiter = True

  31:             .TextFileSemicolonDelimiter = False

  32:             .TextFileCommaDelimiter = False

  33:             .TextFileSpaceDelimiter = False

  34:             .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _

  35:             2, 2, 2, 2, 2, 2, 2, 2)

  36:             .TextFileTrailingMinusNumbers = True

  37:             .Refresh BackgroundQuery:=False

  38:         End With

  39:         Rows("1:1").Select

  40:         Selection.AutoFilter

  41:         With ActiveWindow

  42:             .SplitColumn = 0

  43:             .SplitRow = 1

  44:         End With

  45:         ActiveWindow.FreezePanes = True

  46:     End If

  47: '

  48: End Sub

References:

http://www.ozgrid.com/forum/showthread.php?t=63307

http://www.your-save-time-and-improve-quality-technologies-online-resource.com/vba-excel-importing-file.html

-urShadow