Excel VBA 匯出文字txt檔案問題

excel vba

longtimetalk 2021-06-24 00:37:29 ‧ 1065 瀏覽

爬文看到的VBA，執行結果只能一行排序，請問有辦法能仿照手動複製貼上的排序組合嗎

以下為爬文所修改的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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 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