Hi,各位同学好!我是吴明课堂的答疑老师之一陈婉。祝大家一切顺利,平安快乐!

工作场景描述:

前几天有位同学说,他们公司需要分析系统产生的日志文件(.LOG格式,Ps. TXT格式文档解决方式与log格式一致),文件非常多,需要能批量选择待处理文件,将数据导入Excel并归置到不同列中,以便后续统计分析。

  • 日志文件如下图:

准时下班系列!Excel合集之第9集—VBA导入文本文档数据_日志文件


  • 日志文件格式内容如下图:

准时下班系列!Excel合集之第9集—VBA导入文本文档数据_VBA_02


目标表格内容见下图:

准时下班系列!Excel合集之第9集—VBA导入文本文档数据_Excel_03


VBA编码要实现的步骤解析:

1.  支持用户自主选择文件路径,支持批量选择和处理多个文件;

2. 将每个LOG日志文件的数据导入到Excel工作簿中,新建一个跟日志文件同名的工作表,用来存放同名LOG文件中的数据;

3. 删除头部多余的说明文字,处理;

4. 将导入工作表的数据进行拆分,使其放置到对应的列中;

5. 调整工作表的列宽以完整展示所有数据;

6. 在导入数据前先清除以前的旧数据;

7. 在第一个工作表上创建一个按钮用来触发程序。

详细代码展示:

Option Explicit
'主程序入口
Sub MainFunc()
'删除除了程序入口(第一个工作表以外的其它所有工作表)
Call deleteDataSheets
'选择文件
Dim txtFullName As Variant, varType As String
txtFullName = Application.GetOpenFilename("请选择LOG文件,*.LOG", , "请选择待导入的日志文件", , True)
varType = TypeName(txtFullName)
If (varType = "Boolean") Then
Exit Sub
End If
Dim path As String, i As Integer
'循环处理所有选择的日志文件
For i = LBound(txtFullName) To UBound(txtFullName)
path = txtFullName(i)
Dim newSh As Worksheet
'新建工作表
Set newSh = newWorksheet(path)
newSh.Activate
'导入数据到工作表
Call ImportTxtData(path)
'处理当前激活工作表
Call DealWorkSheet(ActiveSheet)
Next i
End Sub
'删除已有数据工作表
Sub deleteDataSheets()
If (ThisWorkbook.Worksheets.Count > 1) Then
Dim i As Integer
Application.DisplayAlerts = False
For i = 1 To ThisWorkbook.Worksheets.Count - 1
ThisWorkbook.Worksheets(2).Delete
Next i
Application.DisplayAlerts = True
End If
End Sub
'创建新工作表
Function newWorksheet(pathName As String) As Worksheet
Set newWorksheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
Dim sheetNames() As String
sheetNames = VBA.Split(pathName, "\")
newWorksheet.Name = sheetNames(UBound(sheetNames))
End Function
'导入日志文件中的数据
Function ImportTxtData(pathName As String)
Dim str_txt() As String, line As Integer, i As Integer, txt As String
line = 1
Open pathName For Input As #1
Do While Not EOF(1)
Line Input #1, txt
Cells(line, 1).Value = txt
line = line + 1
Loop
Close #1
End Function
'处理工作表
Function DealWorkSheet(sheet As Worksheet)
'删除指定行和列
Call deleteRowsAndCols(sheet)
'删除空行
Call deleteBlankRows(sheet)
'删除第一列中内容为END的行
Call deleteEndRow(sheet)
'处理表头,为分列做准备
Call dealFirstRow(sheet)
'分列,拆分每列数据到对应的单元格
Call splitColumns(sheet)
'宽度自适应
Call colAutoFit(sheet)
End Function
'删除第一列中内容为END的行
Function deleteEndRow(sheet As Worksheet)
Dim endRng As Range
Set endRng = sheet.UsedRange.Columns(1).Find("END")
If (Not endRng Is Nothing) Then
endRng.EntireRow.Delete Shift:=xlUp
End If
End Function
'删除空行
Function deleteBlankRows(sheet As Worksheet)
sheet.Cells.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Function
'分列
Function splitColumns(sheet As Worksheet)
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, TrailingMinusNumbers:=True
End Function
'处理表头数据
Function dealFirstRow(sheet As Worksheet)
Dim val As String
val = Range("A1").Value
val = VBA.Replace(val, "STATE BLSTATE", "STATE_BLSTATE")
Range("A1") = VBA.Replace(val, "LMO BTS", "LMO_BTS")
End Function
'宽度自适应
Function colAutoFit(sheet As Worksheet)
sheet.Columns.AutoFit
End Function
'删除不需要的行和列
Function deleteRowsAndCols(sheet As Worksheet)
'删除前8行
sheet.Rows("1:8").Delete
End Function

执行宏动态效果图:

准时下班系列!Excel合集之第9集—VBA导入文本文档数据_导入数据_04


编程方案点评:

编程的目的是为了实现自定义功能,需要先把业务逻辑每一步安排得清清楚楚,再通过所学语法和技巧使用代码去实现每一步。本质很简单。

编程基本技能:

  • Excel软件功能要熟悉,了解Excel软件有哪些功能,能完成哪些工作;
  • 基础语法要熟悉;
  • 学会通过API了解对象属性和方法,这个技能对利用好录制宏生成的代码至关重要;
  • 学会调试程序的方法,这个技能贯穿整个编程生涯,必须熟练掌握;
  • 对问题的了解和解决方案的步骤是基础要求,逻辑线要十分清晰,每个细节都要想明白。

如需系统学习Excel使用,可查看课程链接:   《吴明老师Excel综合课程》

该课程可以使学员以最少的学习时间,搭建完善的Excel知识架构。