如何使用VBA代码将Excel中的一列数据导出到文本文件?

huangapple go评论95阅读模式
英文:

How to export a column data in excel to a text file using a vba code?

问题

I want to export the data contained in column H in an excel file using a vba code. I managed to get the code, but I want to modify it to allow me to choose the name and the directory to save it before creating it. Thanks in advance.

The code is:

Sub Export()

Dim i As Long, derLig As Long, tabl

derLig = Range("H" & Cells.Rows.Count).End(xlUp).Row
tabl = Range("H1:H" & derLig)
Open "D:\CCP.txt" For Output As #1
For i = 1 To UBound(tabl, 1)
If tabl(i, 1) <> "" Then
Print #1, tabl(i, 1)
End If
Next
Close #1
MsgBox ("Le Fichier CCP A Été Créé Dans Le Disque D")
End Sub

(Note: The code you provided is already in English, and it exports data from Excel to a text file. If you have specific modifications in mind, please let me know.)

英文:

I want to export the data contained in column H in an excel file using a vba code. I managed to get the code, but i want to modify it to allow me to choose the name and the directory to save it before creating it.Thanks in advance
The code is

Sub Export()

Dim i As Long, derLig As Long, tabl

derLig = Range(&quot;H&quot; &amp; Cells.Rows.Count).End(xlUp).Row
tabl = Range(&quot;H1:H&quot; &amp; derLig)
Open &quot;D:\CCP.txt&quot; For Output As #1
For i = 1 To UBound(tabl, 1)
If tabl(i, 1) &lt;&gt; &quot;&quot; Then
Print #1, tabl(i, 1)
End If
Next
Close #1
MsgBox (&quot;Le Fichier CCP A Et&#233; Cr&#233;&#233; Dans Le Disque D&quot;)
End Sub

答案1

得分: 1

Here's the translated VBA code:

Option Explicit

Sub Export()

    Dim i As Long, n As Long, lastRow As Long, table
    Dim filename As String
    
    With ActiveSheet
        If WorksheetFunction.CountA(.Range("H:H")) = 0 Then
            MsgBox "No data to save", vbCritical
            Exit Sub
        End If
        lastRow = .Range("H" & Cells.Rows.Count).End(xlUp).Row
        table = .Range("H1:H" & lastRow)
    End With
    
    filename = Application.GetSaveAsFilename
    If Len(filename) = 0 Then
        MsgBox "Invalid file name", vbCritical
        Exit Sub
    End If
    
    Open filename For Output As #1
    For i = 1 To UBound(table, 1)
        If table(i, 1) <> "" Then
            Print #1, table(i, 1)
            n = n + 1
        End If
    Next
    Close #1
    MsgBox n & " lines written to " & filename, vbInformation

End Sub

Please note that I've translated the code, but I didn't provide an answer to your translation request. If you have any other questions or need further assistance, feel free to ask.

英文:
Option Explicit

Sub Export()

    Dim i As Long, n As Long, derLig As Long, tabl
    Dim filename As String
    
    With ActiveSheet
        If WorksheetFunction.CountA(.Range(&quot;H:H&quot;)) = 0 Then
            MsgBox &quot;pas de donn&#233;es &#224; sauvegarder&quot;, vbCritical
            Exit Sub
        End If
        derLig = .Range(&quot;H&quot; &amp; Cells.Rows.Count).End(xlUp).Row
        tabl = .Range(&quot;H1:H&quot; &amp; derLig)
    End With
    
    filename = Application.GetSaveAsFilename
    If Len(filename) = 0 Then
        MsgBox &quot;nom de fichier non valide&quot;, vbCritical
        Exit Sub
    End If
    
    Open filename For Output As #1
    For i = 1 To UBound(tabl, 1)
        If tabl(i, 1) &lt;&gt; &quot;&quot; Then
            Print #1, tabl(i, 1)
            n = n + 1
        End If
    Next
    Close #1
    MsgBox n &amp; &quot; lignes &#233;crites dans &quot; &amp; filename, vbInformation
  
End Sub

</details>



huangapple
  • 本文由 发表于 2023年8月5日 02:23:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/76838365.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定