5.5.4-代码解析：sub_excel文件解析

本Sub过程旨在解读excel文件，excel文件格式与上一节中的TXT很类似，也是逐点解读，然后写入数据库文件中

代码如下

1   Sub sub_excel文件解析(inputAddress, eachFileName, backupAddress)
2       Application.DisplayAlerts = False
3       
4       fileAddress = inputAddress & "\" & eachFileName
5
6       ' 数据库文件
7       currentPath = ThisWorkbook.Path
8       dbExcel = currentPath & "\数据库.xlsx"
9       Set wb = Workbooks.Open(dbExcel)
10      Set shtDB = wb.Worksheets(1)
11      rngPoint = shtDB.Range("A:A")
12      inputCol = shtDB.Cells(1, Columns.Count).End(xlToLeft).Column + 1
13      
14      shtDB.Cells(1, inputCol) = Split(eachFileName, ".")(0)
15      
16      ' 解读Excel文件
17      Set wb1 = Workbooks.Open(fileAddress)
18      Set sht1 = wb.Worksheets(1)
19      maxRow = sht1.Cells(Rows.Count, "A").End(xlUp).Row
20      For i = 2 To maxRow Step 1
21          pointName = sht1.Cells(i, "A")
22          devValue = sht1.Cells(i, "D")
23          inputRow = Application.Match(pointName, rngPoint, 0)
24          shtDB.Cells(inputRow, inputCol) = devValue
25      Next
26      
27      wb1.Close
28      
29      wb.Save
30      wb.Close
31      
32      ' 备份文件
33      backupFileAddress = backupAddress & "\" & eachFileName
34      FileCopy fileAddress, backupFileAddress
35      Kill fileAddress
36  End Sub

关键代码解读

1）第10行：Set shtDB = wb.Worksheets(1)。获取第1张工作表，当工作簿中有多张工作表时不建议这么使用，可以使用工作表名称来获取工作表。