|
有下述210个宏代码,从\文件1\里分别将001.txt 002.txt...210.txt导入001 002 ...210工作表的A列,如何将这210个宏代码合写在一起?
Sub 导入001()
Sheets("001").Select
Range("A1:A100").ClearContents
Application.DisplayAlerts = False '关闭报警
Dim s() As String '定义字串数组
Dim s2() As String '定义字串数组
On Error Resume Next
'Range("a1", Cells.Find("*", , , , , xlPrevious)) = ""
Open ThisWorkbook.Path & "\文件1\001.txt" For Input As #1 '打开文本文件
s = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf) 'InputB(LOF(1), 1)读入内存,StrConv 编码转换;Split()分割成行
Close #1 '关闭文件
For x = 0 To UBound(s)
s2 = Split(s(x), " ")
ss = UBound(s2)
Range("a" & x + 1).Resize(, ss + 1).NumberFormat = "@"
Range("a" & x + 1).Resize(, ss + 1) = s2
Next
Application.DisplayAlerts = True '打开报警
End Sub
Sub 导入002()
Sheets("002").Select
Range("A1:A100").ClearContents
Application.DisplayAlerts = False '关闭报警
Dim s() As String '定义字串数组
Dim s2() As String '定义字串数组
On Error Resume Next
'Range("a1", Cells.Find("*", , , , , xlPrevious)) = ""
Open ThisWorkbook.Path & "\文件1\002.txt" For Input As #1 '打开文本文件
s = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf) 'InputB(LOF(1), 1)读入内存,StrConv 编码转换;Split()分割成行
Close #1 '关闭文件
For x = 0 To UBound(s)
s2 = Split(s(x), " ")
ss = UBound(s2)
Range("a" & x + 1).Resize(, ss + 1).NumberFormat = "@"
Range("a" & x + 1).Resize(, ss + 1) = s2
Next
Application.DisplayAlerts = True '打开报警
End Sub
...
Sub 导入210()
Sheets("210").Select
Range("A1:A100").ClearContents
Application.DisplayAlerts = False '关闭报警
Dim s() As String '定义字串数组
Dim s2() As String '定义字串数组
On Error Resume Next
'Range("a1", Cells.Find("*", , , , , xlPrevious)) = ""
Open ThisWorkbook.Path & "\文件1\210.txt" For Input As #1 '打开文本文件
s = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf) 'InputB(LOF(1), 1)读入内存,StrConv 编码转换;Split()分割成行
Close #1 '关闭文件
For x = 0 To UBound(s)
s2 = Split(s(x), " ")
ss = UBound(s2)
Range("a" & x + 1).Resize(, ss + 1).NumberFormat = "@"
Range("a" & x + 1).Resize(, ss + 1) = s2
Next
Application.DisplayAlerts = True '打开报警
End Sub
又提新要求啦!
Sub 导入()
For I = 1 To 210
SHNam = Format(I, "000")
With Sheets(SHNam)
.Range("A1:G100").ClearContents
Application.DisplayAlerts = False
Dim s() As String
Dim s2() As String
On Error Resume Next
Open ThisWorkbook.Path & "\文件1\" & SHNam & ".txt" For Input As #1
s = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)
Close #1
For x = 0 To UBound(s)
s2 = Split(s(x), " ")
ss = UBound(s2)
.Range("a" & x + 1).Resize(, ss + 1).NumberFormat = "@"
.Range("a" & x + 1).Resize(, ss + 1) = s2
Next
End With
Next
Application.DisplayAlerts = True
End Sub
|
|