Dunk

Private Sub cme_ReadSourceFile_Click()


Dim RowCount As Integer
Dim i As Integer
Dim Separator As String

Separator = ThisWorkbook.Sheets(1).Range("C2").Value

ThisWorkbook.Sheets(2).Columns.Delete
'設定欄位名稱
RowCount = CellFunction.NotSpaceRow("B")
For i = 2 To RowCount
    ThisWorkbook.Sheets(2).Cells(i - 1)(1).Value = ThisWorkbook.Sheets(1).Range("B" & CStr(i)).Value
Next

'根據分隔符號呈現內容
Dim v, qt
    With Application.FileDialog(3)
        .Filters.Clear
        .Filters.Add "All Text Files", "*.txt"
        .AllowMultiSelect = True
        .Title = "請選擇來源檔!"


        If .Show Then
            For Each v In .SelectedItems
                Set qt = ThisWorkbook.Sheets(2).QueryTables.Add("TEXT;" & v, ThisWorkbook.Sheets(2).Range("A2"))
                qt.TextFileOtherDelimiter = Separator
                qt.Refresh True
            Next
        End If
    End With

ActiveWorkbook.Sheets(2).Select
MsgBox "讀取檔案完畢"

End Sub

'選取表格
Sub SelectAllTable()
    ActiveCell.CurrentRegion.Select
End Sub
'取得最後一列的長度(row length)
Function NotSpaceRow(ByVal columnName As String)
    Dim columnindex As String
    '舊版excel最多65535
    columnindex = columnName & "65536"
    Dim myRange As Range
    Set myRange = ThisWorkbook.Sheets(1).Range(columnindex).End(xlUp)
    myRange.Select
    NotSpaceRow = myRange.Row
    Set myRange = Nothing
End Function
'取得最後一欄的長度(column length)
Function NotSpaceColumns(ByVal rowIndex As Integer)
    Dim selectedRow As String
    selectedRow = "IV" & rowIndex
    Dim myRange As Range
    Set myRange = ThisWorkbook.Sheets(1).Range(selectedRow).End(xlToLeft)
    myRange.Select
    NotSpaceColumns = myRange.Column
    Set myRange = Nothing
End Function