iT邦幫忙

0

Excel VBA 匯出文字txt檔案問題

爬文看到的VBA,執行結果只能一行排序,請問有辦法能仿照手動複製貼上的排序組合嗎
https://ithelp.ithome.com.tw/upload/images/20210624/20131103z9as0MapjM.jpg

以下為爬文所修改的VBA

Sub Ex()
    Dim Rngx As Range, fs As Object, a As Object, exR As Range
    Set Rngx = Range("A1:C3")
    Set fs = CreateObject("Scripting.FileSystemObject")

    Set a = fs.CreateTextFile("X:\123.txt", True)
    For Each exR In Rngx
        a.WriteLine (exR.Text)
    Next
    a.Close
End Sub

1 個回答

0
rogeryao
iT邦大師 1 級 ‧ 2021-06-24 04:27:23
最佳解答
Private Sub CommandButton1_Click()
Dim Rngx As Range, fs As Object, a As Object, exR As Range
    Set Rngx = Range("A1:C3")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("D:\123.txt", True)
Dim k As Integer
Dim s As String
k = 1
s = ""
    For Each exR In Rngx
      If (k Mod 3) = 0 Then
        s = s & exR.Text
        a.WriteLine (s)
        s = ""
      Else
        s = s & exR.Text & vbTab
      End If
      k = k + 1
    Next
    a.Close
End Sub

謝謝你的回答,對我很有幫助

我要發表回答

立即登入回答