excel - 通过 Vba 循环保存 Excel 行

Question

大家好，我想将我的 excel 文件的每一行保存为批处理文件。
我为第二排做了，但不能循环做。

Sub ExportFile()

Dim objFSO, objFile
Dim fileName As String
Dim RootPath As String
Dim text_comm As String
Dim OutputString: OutputString = ""

fileName = Cells([2], [1])
text_comm = Cells([2], [5])
RootPath = "C:\Users\DELL\Desktop\PDM SOLID DOSYA YOLU\"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(RootPath + fileName + ".bat")

Do
OutputString = OutputString & Replace((text_comm), Chr(10), vbNewLine) & vbNewLine
objFile.Write (OutputString)
fileName = Cells([2] + 1, [1]) #Wrong
text_comm = Cells([2] + 1, [5]) #Wrong
Loop Until IsEmpty(text_comm)

Set objFile = Nothing
Set objFSO = Nothing

End Sub

Answer 1

将单元格内容导出为文本文件

假设第一列(“A”)包含文件基本名称，第五列(“E”)包含代码(每个代码在一个单元格中)。

Option Explicit

Sub ExportFiles()
    
    Const RootPath As String = "C:\Users\DELL\Desktop\PDM SOLID DOSYA YOLU\"
    Const FirstRow As Long = 2
    Const NameCol As Long = 1
    Const CodeCol As Long = 5
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim r As Long: r = FirstRow
    Dim FileBaseName As String: FileBaseName = ws.Cells(r, NameCol)
    Dim text_comm As String: text_comm = ws.Cells(r, CodeCol)
    
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim fsoFile As Object
    Dim OutputString As String
    
    Do Until Len(text_comm) = 0
        Set fsoFile = fso.CreateTextFile(RootPath & FileBaseName & ".bat")
        OutputString = Replace(text_comm, Chr(10), vbNewLine) & vbNewLine
        fsoFile.Write OutputString
        r = r + 1
        FileBaseName = ws.Cells(r, NameCol)
        text_comm = ws.Cells(r, CodeCol)
    Loop

    MsgBox "Files created.", vbInformation

End Sub