使用情境如下圖,按下Loaf File 按鈕之後,依C4內的檔案路徑讀取bin檔,並分段填入C1及C2中
先上程式
1 Sub Load_File()
2
3 Dim FilePath As String
4
5 FilePath = Replace(Range("C4").Value, """", "")
6
7 'Debug.Print FilePath
8
9 Dim FileLen As Integer
10 Dim str As String
11
12 Dim byteArr() As Byte
13 Dim fileInt As Integer: fileInt = FreeFile
14
15 Open FilePath For Binary Access Read As #fileInt
16 FileLen = LOF(fileInt) - 1
17 ReDim byteArr(0 To FileLen)
18 Get #fileInt, , byteArr
19 Close #fileInt
20
21 str = ""
22 For i = 0 To 8191
23 If byteArr(i) < 16 Then
24 str = str + "0" + Hex(byteArr(i)) + " "
25 Else
26 str = str + Hex(byteArr(i)) + " "
27 End If
28
29 Next
30 Range("C1").Value = str
31
32 'Debug.Print str
33 'Debug.Print "========================================="
34
35 str = ""
36 For i = 8192 To FileLen
37 If byteArr(i) < 15 Then
38 str = str + "0" + Hex(byteArr(i)) + " "
39 Else
40 str = str + Hex(byteArr(i)) + " "
41 End If
42 Next
43 Range("C2").Value = str
44
45 'Debug.Print str
46 End Sub
讀取檔案的部份參考 how can I read a binary file using VBA?再做修改,以下幾個坑說明一下:
1.要分段是因為Excel一個儲存格能放的字數有限,所以才需要方兩段分別放到不同儲存格
2. 將路徑讀進來時雙引號「"」要去掉,用Replace函數可將雙引號取代掉,而表示雙引號字元的方法為「""""」四個雙引號,也就是在雙引號中間要以兩個雙引號來代表一個雙引號 (有夠繞口XD
3. 讀出來的檔案放在byteArr中,為16進位數值,用HEX()函數即可轉換為16進位字串
4. 24~26行是補0的判斷,讓轉出來的數值保持為兩位數,以及在每一個數值後面加空格