Dunk

Public sourceRawData As Variant

Private Sub cmb_ClearImportResult_Click()
  Worksheets("來源資料").Columns.Delete
  Worksheets("轉換後資料").Columns.Delete
  MsgBox "清除成功!"
End Sub

Private Sub cmb_Exit_Click()
 ThisWorkbook.Close SaveChanges:=True
End Sub

Private Sub cmb_ReadFile_Click()
 讀取來源檔到來源資料
 根據格式切割內容
 Worksheets("轉換後資料").Activate
 MsgBox "匯入成功!"
    
End Sub


Private Sub 讀取來源檔到來源資料()

Dim v, qt
Dim i As Integer
'Dim lastRowNumber As Integer
Dim tempFileCache As Variant

Worksheets("來源資料").Columns.Delete

'將儲存格格式設為文字格式
Dim myRange As Range
Set myRange = Worksheets("來源資料").Range("A1:A65536")
myRange.NumberFormatLocal = "@"
        
    
Dim rowCounter As Integer


    
    With Application.FileDialog(1)
        .Filters.Clear
        .Filters.Add "All Text Files", "*.txt"
        .AllowMultiSelect = False
        .Title = "請選擇來源檔案"


        If .Show Then
            For Each v In .SelectedItems
                tempFileCache = FileIOUtility.ReadFile(v)
                For i = 1 To UBound(tempFileCache)
                    Worksheets("來源資料").Range("A" + CStr(i)).Value = tempFileCache(i - 1)
                Next
            Next
        End If
    End With
    
'    lastRowNumber = Worksheets("來源資料").Range("A65536").End(xlUp).row
'
'    MsgBox lastRowNumber

End Sub

Private Sub 根據格式切割內容()
Dim definedColumnCount As Integer
Dim i, j, k As Integer
Dim offsetCount As Integer
Dim definedRange As Range
Dim sourceRange As Range
Dim destRange As Range
Dim lastRowNumber As Integer
Dim tempstring As String

'取得來源定義檔欄位總數
definedColumnCount = Worksheets("來源檔定義").Range("A65536").End(xlUp).row

'取得來源資料的總列數(total row numbers)
lastRowNumber = Worksheets("來源資料").Range("A65536").End(xlUp).row

Worksheets("轉換後資料").Columns.Delete

For j = 1 To lastRowNumber
    tempstring = Worksheets("來源資料").Range("A" & CStr(j)).Value
    offsetCount = 1
    For i = 2 To definedColumnCount
        Set definedRange = Worksheets("來源檔定義").Range("C" & CStr(i))
            Worksheets("轉換後資料").Cells(i - 1)(j).NumberFormatLocal = "@"
            Worksheets("轉換後資料").Cells(i - 1)(j).Value = Mid(tempstring, offsetCount, CInt(definedRange.Value))
            offsetCount = offsetCount + CInt(definedRange.Value)
    Next
Next


End Sub

Sub WriteFile(ByVal filename As String, ByVal content As String)
Open filename For Output As #1
Print #1, content
Close #1
End Sub

Function ReadFile(ByVal infilename As String)
    'Microsoft Scripting Runtime設定引用項目
    Dim myFso As Scripting.FileSystemObject
    Dim myTxt As Scripting.TextStream
    Dim myStr As String
    Dim resultString() As Variant
    ReDim resultString(65536)
    Dim rowNumber As Integer
    rowNumber = 0
    
    Set myFso = CreateObject("Scripting.FileSystemObject")
    '指定檔案名稱
    Set myTxt = myFso.OpenTextFile(filename:=infilename, _
        IOMode:=ForReading)
   
    With myTxt
        Do Until .AtEndOfStream
         
            resultString(rowNumber) = CStr(.ReadLine)
            rowNumber = rowNumber + 1
         
        Loop
        .Close
    End With
    
    ReDim Preserve resultString(rowNumber)
    
    Set myTxt = Nothing                            '釋放物件
    Set myFso = Nothing
    ReadFile = resultString
    
End Function