Save Excel Row Via Vba Loop

user_16704510 0
Viewed 52

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
vba excel
3 Answers

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
user_167045100

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
user_98140690

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)
user_7361720
Related