楼主
wise |
Q:如何将文本中的数字用空格隔开并且另存新命名的文本?
具体要求：文本“数据”中，每行为一个数字串，含有7个数字，数字间没有任何符号隔开。
希望用vba代码，将其中每个数字串的七个数字，用空格隔开，并另存为“数据2”文本。
A:ALT+F11→插入模块→输入以下代码:
方法1
- Sub Macro1()
- Dim myFileName As String
- '************************导入数据*******************
- With ActiveSheet.QueryTables.Add(Connection:= _
- "TEXT;" & ThisWorkbook.Path & "\数据.txt", Destination _
- :=Range("A1"))
- .Name = "数据"
- .TextFilePlatform = 936
- .TextFileTabDelimiter = True
- .Refresh
- End With
- '***********************格式化数据******************
- For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
- Cells(i, 1) = Format(Cells(i, 1), "0 0 0 0 0 0 0")
- Next i
- '***********************另存数据为txt***************
- myFileName = "数据2.txt"
- On Error Resume Next
- Kill ThisWorkbook.Path & "\" & myFileName
- Application.ScreenUpdating = False
- Worksheets("Sheet1").Copy
- ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
- & "\" & myFileName, _
- FileFormat:=xlCSV
- MsgBox "文件保存成功!"
- ActiveWorkbook.Close SaveChanges:=False
- Application.ScreenUpdating = True
- End Sub
方法2：
- Sub Macro2()
- wj = ThisWorkbook.Path & "\数据.txt"
- Open wj For Input As #1
- i = 1
- Do While Not EOF(1)
- Input #1, n
- Cells(i, 1) = n
- i = i + 1
- Loop
- Reset
- Close #1
- Open ThisWorkbook.Path & "\数据2.txt" For Output As #1
- For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
- p = ""
- For j = 1 To Len(Cells(i, 1))
- b = Mid(Cells(i, 1), j, 1)
- p = p & " " & b
- Next j
- n = Mid(p, 2)
- Print #1, n
- Next i
- Reset
- Close #1
- End Sub
方法3:
- Sub test()
- Dim i&, j&, str$, s, Filenumber
- Filenumber = FreeFile
- Open ThisWorkbook.Path & "\数据.txt" For Input As #Filenumber
- s = Split(Input(LOF(1), 1), vbCrLf)
- Close #Filenumber
-
- For i = 0 To UBound(s)
- str = Left(s(i), 1)
- For j = 2 To Len(s(i))
- str = str & " " & Mid(s(i), j, 1)
- Next
- s(i) = str
- Next
-
- Open ThisWorkbook.Path & "\数据2.txt" For Output As #Filenumber
- Print #Filenumber, Join(s, vbCrLf)
- Close #Filenumber
-
- End Sub
 数字用空格断开.rar
|
