通过录制宏的方法将txt数据导入Excel中

2018年3月9日12:06:46 评论 3,627 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

熟悉Excel的朋友都应该知道在Excel中是有导入txt数据的功能的,比如在Excel2007中,通过数据当中的“自文本”选项,按照提示一步一步的操作就可以将一个txt格式的数据导入到Excel中。既然有这样的方法我们自然就应该想到,如果我将导入txt的这个步骤通过录制宏功能录下来,然后再稍加修改,加个循环,不就可以将大量的txt文本一次性的导入excel中了吗?在示例文件中我们有4个txt文件,都是由机器在日常工作中产生的数据,现在需要将这些数据导入到Excel中以进行分析。

好,现在我们先来完成一个录制宏的步骤,先在开发工具中打开录制宏,数据中选择“自文本",选择任意一个txt数据文件,导入,步骤中选分隔符号选项,下一步,分隔符号选tab键和空格键,会在数据预览中看到效果,再下一步,然后点完成,选择A1单元格作为数据起始位置。下面是我得到的宏代码:

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:/VBA20140209/1文件系统操作/txt数据导入excel/1.txt", Destination:=Range("$A$1"))
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

上面的代码看起来很复杂,其实我们没必要看懂其中所有的参数,我们要改的地方只是这句With ActiveSheet.QueryTables.Add(Connection:= "TEXT;F:/VBA20140209/1文件系统操作/txt数据导入excel/1.txt", Destination:=Range("$A$1")),可以很容易看出其中的路径就是我们txt文件的完整路径,而Range("$A$1")就是数据的起始单元格。要想将txt文件批量导入那我们仍要利用Dir函数来获得每个txt文件的路径,下面是我改过的代码

Sub Macro1()
'
' Macro1 Macro
'

Dim mypath, myfile, sht
mypath = ThisWorkbook.Path & "/"                                 '获得文件夹路径
myfile = Dir(mypath & "*.txt")                                           '获得txt路径
Do While myfile <> ""
Set sht = Worksheets.Add(after:=ActiveSheet)               '新建一个sheet
sht.Name = Split(myfile, ".")(0)                                         '以txt的名称命名该sheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & mypath & myfile, Destination:=Range("$A$1"))           '将原代码的路径改为mypath & myfile
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
myfile = Dir
Loop
End Sub

上面的代码中仍然利用Dir依次获得txt文件的路径,然后将录制代码中的F:/VBA20140209/1文件系统操作/txt数据导入excel/1.txt用我们获得的路径mypath & myfile代替,注意不要多加或漏了引号,其他的参数都不需要我们去改。上面的代码看似更繁琐了,但是写起来却是更方便了。

本节示例文件下载地址:http://pan.baidu.com/s/13wp5O

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin