VBA 讀檔貼至工作表

1. 使用Application.FileDialog(msoFileDialogFilePicker)選取檔案,

2. 指定選取檔案檔案名

     filename = fDialog.SelectedItems(1),

3. 讀取檔案並用vbCrLf分行

    a = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)

4. 使用","來分隔

    b = Split(a(i), ",")

    其他分隔符號有 chr(9) tab空格

    https://blog.csdn.net/liqfyiyi/article/details/6936478

 Dim fDialog As FileDialog

 Dim Total_Datarows, Total_Datacolumns

 Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

If fDialog.Show = -1 Then

  

    Dim filename As String

     

    filename = fDialog.SelectedItems(1)

    

    Dim a, b, k%, i%, j%, q%, i_2

    Open filename For Input As 1

 

    a = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)

   

    Close #1

    

    k = UBound(a)

    For i = 0 To k

        b = Split(a(i), ",")

        q = UBound(b)

        For j = 0 To q

        Worksheets("工作表1").Cells(i + 1, j + 1) = b(j)

    Next

 Next

   

    

End If