时间戳

首页 > 解决方案 > 将 Excel 行导出到文本文件

excel - 将 Excel 行导出到文本文件

问题描述

我见过一些解决方案，但它们并没有专门做我想做的事情。

我需要能够做什么：

我会发布代码，但我不知道从哪里开始。有没有人有任何想法？

标签： excelvba

解决方案

例如，我使用了我在评论中发布的链接的答案。
我在里面放了一个简单的循环来循环范围，在文本文件中为每个值创建一行。

然后我从一个循环中的另一个子（不是你必须做的事情）调用，该循环遍历所有行，并且对于每一行，传递所述行中所有已使用列的范围。此特定代码要求您添加对Microsoft Scripting Runtime.

Option Explicit

Sub SaveNfo()
Dim ws As Worksheet, rng As Range, LastColumn As Range, rngRow As Variant
Set ws = Worksheets(1)
Set rng = ws.Range("A3:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) 'start on row 3, include all rows with a filepath

For Each rngRow In rng
    If Not rngRow = "" Then
        SaveTextToFile rngRow & rngRow.Offset(, 1), _
        ws.Range(rngRow.Offset(, 2), Cells(rngRow.Row, ws.Cells(rngRow.Row, ws.Columns.Count).End(xlToLeft).Column))
    End If
Next
End Sub

Private Sub SaveTextToFile(filePath As String, rng As Range)
    Dim cell As Variant
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim fileStream As TextStream
    ' Here the actual file is created and opened for write access
    Set fileStream = fso.CreateTextFile(filePath)
    ' Write something to the file
    For Each cell In rng
            fileStream.WriteLine cell
    Next
    ' Close it, so it is not locked anymore
    fileStream.Close
End Sub

如果文件名列不包含.nfo，您可以手动将其添加到代码中：

SaveTextToFile rngRow & rngRow.Offset(, 1), _ 变成
SaveTextToFile rngRow & rngRow.Offset(, 1) & ".nfo", _

rngRow指向路径的“A”列。
rngRow.Offset(, 1)然后是“B”列，用于名称。
rngRow.Offset(, 2)然后是ofc“C”，我们开始寻找要放入文件的数据。

或者，如果您想要真正简短的版本：

Sub SaveNfo()
Dim rngRow As Variant, cell As Variant, fso As Object, fileStream As Object
For Each rngRow In Range("A3:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Not rngRow = "" Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fileStream = fso.CreateTextFile(rngRow & rngRow.Offset(, 1))
        For Each cell In Range(rngRow.Offset(, 2), Cells(rngRow.Row, Cells(rngRow.Row, Columns.Count).End(xlToLeft).Column))
            fileStream.WriteLine cell
        Next
        fileStream.Close
    End If
Next
End Sub

推荐阅读