Wednesday, October 10, 2012

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


No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...