Excel如何用VBA把A1列的内容写入到TXT文本里

sub 宏1()

    dim arr,i

    arr=range("a1").currentregion

    open "1.txt" for output as #1

    for i=1 to ubound(arr)

        print #1, arr(i,1)

    next i

    close #1

end sub

建一个文件夹，把目标txt文件和excel文件放入其中，在excel的宏编辑器中写入以下代码，有些地方可根据你的实际情况做相应改动：

Sub import_from_txt()

Dim file_name As String, my_path As String

Dim lines, cols

Dim i As Integer, j As Integer, k As Integer, q As Integer

Application.ScreenUpdating = False

Worksheets("Sheet1").Range("A1:Z65536").ClearContents

my_path = ThisWorkbook.Path

file_name = "test.txt"

'读取文件

Open my_path &"\" &file_name For Input As #1

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

Close #1

k = UBound(lines) + 1 '文件的行数

'遍历每一行

For i = 1 To k

cols = Split(lines(i - 1), ",") '以逗号作为分隔，将每行字符分割，分隔符可根据实际情况自己修改

q = UBound(cols) + 1 '分隔成的列数

For j = 1 To q '遍历该行的每一列

Worksheets("Sheet1").Cells(i, j) = cols(j - 1) '输出到表格中

Next

Next

MsgBox ("文件" &file_name &"读取完成,共" &k &"行")

Application.ScreenUpdating = True

End Sub

在多个txt文件的文件夹中新建一个EXCEL，在其VBA中输入ReadTextFiles()，然后运行：

Sub ReadTextFiles()

Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x

Dim myF As String, i As Long

myDir = ThisWorkbook.Path &Application.PathSeparator

myF = Dir(myDir &"*.txt")

Do While myF <>""

ff = FreeFile

Open myDir &myF For Input As #ff

Do While Not EOF(ff)

Line Input #ff, txt

x = Split(txt, "|")

n = n 1

ReDim Preserve a(1 To n)

a(n) = x

Loop

Close #ff

myF = Dir()

Loop

Cells.Clear

With ThisWorkbook.Worksheets("Sheet1").Range("a1")

For i = 1 To UBound(a)

.Offset(i - 1).Resize(, UBound(a(i)) 1) = a(i)

Next

End With

End Sub

---