通過Vba回圈保存Excel行

大家好，我想將我的 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

uj5u.com熱心網友回復：

將單元格內容匯出為文本檔案

• 假設第一列（“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

uj5u.com熱心網友回復：

text_comm 是每個回圈的合并前單元格

Sub ExportFile()

    Dim objFSO, objFile
    Dim fileName As String
    Dim RootPath As String
    Dim text_comm As String
    Dim OutputString: OutputString = ""
    Dim RowIndex As String
        
    RowIndex = 2
    
    Do
        fileName = Cells([RowIndex], [1])
        text_comm = Cells([RowIndex], [5])
        RootPath = "C:\Users\DELL\Desktop\PDM SOLID DOSYA YOLU\"
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFile = objFSO.CreateTextFile(RootPath   fileName   ".bat")
        OutputString = OutputString & Replace((text_comm), Chr(10), vbNewLine) & vbNewLine
        objFile.Write (OutputString)
        RowIndex = RowIndex   1
    Loop Until RowIndex = 6
    
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

uj5u.com熱心網友回復：

您的回圈需要遍歷所有行。目前它只處理第一行并無限地卡在那里。您必須記錄正在處理的當前行RowIndex并在每次之后增加它。

這是修復回圈的一種方法：

Dim RowIndex As Integer : RowIndex = 1
Do
    OutputString = OutputString & Replace((text_comm), Chr(10), vbNewLine) & vbNewLine
    objFile.Write (OutputString)
    fileName = Cells([2]   RowIndex, [1]) 
    text_comm = Cells([2]   RowIndex, [5])
    RowIndex = RowIndex   1 
Loop Until IsEmpty(text_comm)

標籤：擅长 vba

上一篇：VBA檔案操作

下一篇：從Excel到SQL實作SUMIF()函式