Save Excel Row Via Vba Loop-CodePudding

Hello everyone ı want to save each row of my excel file as a batch file. I did it for second row but cant do it in loop.

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

CodePudding user response：

text_comm is merge former cell for each loop

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

CodePudding user response：

Export Cell Contents As TextFiles

It is assumed that the first column ("A") contains the file base names, and that the fifth column ("E") contains the codes (each code in one cell).

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

CodePudding user response：

Your loop needs to iterate through all rows. Currently it only processes the first row and is stuck there infinitly. You will have to keep record of the current row being processed RowIndex and increment it after each time.

Here is one way to fix your loop:

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)