☰

我如何更改此 VBA 代码中的目录？VBA“Excel 转 TXT 或 CSV”

tag-icon • tag-icon microsoft-excel vba csv
我如何更改此 VBA 代码中的目录？VBA“Excel 转 TXT 或 CSV”

我确实想改变此代码的目录，Application.ActiveWorkbook.Path但我不知道把它放在哪里。

Public Sub CharacterSV()
    Const DELIMITER As String = "|"
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long
    Dim sOut As String

    nFileNum = FreeFile
    Open ActiveWorkbook.Name & ".txt" For Output As #nFileNum
    For Each myRecord In Range("A1:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Range(.Cells, _
                    Cells(.Row, Columns.Count).End(xlToLeft))
                sOut = sOut & DELIMITER & myField.Text
            Next myField
            Print #nFileNum, Mid(sOut, 2)
            sOut = Empty
        End With
    Next myRecord
    Close #nFileNum
End Sub

我试图弄清楚，并且真的想学习 VBA，但我无法让它发挥作用。

提前致谢！

答案1

将其添加到您的代码中：
Dim fname as string fname = "c:\documents and settings\desktop\" Open fname & ActiveWorkbook.Name & ".txt" For Output As #nFileNum

将桌面替换为您的目录，打开将在您的目录中打开新文件，注意在 fname 中写入真实且正确的路径。

答案2

感谢您的启发。

我刚刚传递了另一个代码，它使它变得更加简单。因为我只想将文件添加到我正在使用的目录中，但想使 vba 动态化。

所以我用了ActiveWorkbook.FullName。ActiveWorkbook.Name我还将工作表中的名称添加到了" - " & ActiveSheet.Name &

这是最终的代码（还添加了工作表的名称）：

Public Sub Worksheet_naar_TXT_Pipe_delimiter()
    Const DELIMITER As String = "|"
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long
    Dim sOut As String

    nFileNum = FreeFile
    Open ActiveWorkbook.FullName & " - " & ActiveSheet.Name & ".txt" For Output As #nFileNum
    For Each myRecord In Range("A1:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Range(.Cells, _
                    Cells(.Row, Columns.Count).End(xlToLeft))
                sOut = sOut & DELIMITER & myField.Text
            Next myField
            Print #nFileNum, Mid(sOut, 2)
            sOut = Empty
        End With
    Next myRecord
    Close #nFileNum
End Sub

感谢您的回复，这让我想到了这个想法！

相关内容