我贴上我的代码 你自己改改吧
Result = Clipboard.GetText
z = 0
s = 0
Row_l = Split(Result, vbCrLf)For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), Chr(9))
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
' Debug.Print Col_L(t),
Next
' Debug.Print
Next
Result = Clipboard.GetText
z = 0
s = 0
Row_l = Split(Result, vbCrLf)For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), Chr(9))
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
' Debug.Print Col_L(t),
Next
' Debug.Print
Next
z = 0
s = 0
Row_l = Split(st, "|")For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), Chr(9))
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
Debug.Print Grid1(0, 0)
Next
Next输出结果:
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a002
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a003
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a004
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a005
EDFS01a006
EDFS01a006
EDFS01a006
EDFS01a006
EDFS01a006
EDFS01a006
EDFS01a006
EDFS01a006
Private Sub Command1_Click()
Dim z, s, i, Grid1, Row_l, Col_L, stz = 0
s = 0
st = Clipboard.GetText
Row_l = Split(st, "|")For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), Chr(9))
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
Debug.Print Grid1(0, 0)
NextNext
End Sub
我这里的输出结构是
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
EDFS01a001
把 Debug.Print 语句放在循环外面
Sub test1()Dim st As String
Dim st1, i As Integer, n As Integer
Dim k As Integer
Dim p As StringOpen ThisWorkbook.Path & "\a.txt" For Input As #1Do Until EOF(1)
Line Input #1, st
z = 0
s = 0Row_l = Split(st, vbCrLf)For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), "|")
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
NextNextLoopDebug.Print Grid1(1, 1)
Close #1Sheets(1).Cells.EntireColumn.AutoFit
End Sub
还是显示下标越界。
Line Input 事一行行读 我的代码事直接处理一段数据Sub test1() Dim st As String
Dim st1, i As Integer, n As Integer
Dim k As Integer
Dim p As String
dim txtlineOpen ThisWorkbook.Path & "\a.txt" For Input As #1 Do Until EOF(1)
Line Input #1, txtline
st=st& txtline & vbcrlf
Loop
Close #1 z = 0
s = 0 Row_l = Split(st, vbCrLf) For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), "|")
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
Next Next Debug.Print Grid1(1, 1)
End Sub