Create Bat File With Excel Data with VBA

Question

I took some research from google

Sub ExportRangetoFile()

    Dim wb As Workbook
    Dim saveFile As String
    Dim WorkRng As Range
    Set WorkRng = Sheets("Match").Range("K:K")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wb = Application.Workbooks.Add
    WorkRng.Copy
    wb.Worksheets(1).Paste
    wb.SaveAs Filename:="C:\Users\xxxx\Desktop\newcurl.bat", FileFormat:= _
    xlTextPrinter, CreateBackup:=False
    wb.Close
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

But the result appear in bat format was oversize. As each cell on that column contain 295 character long (string).

Hence, some part of the ending were shown at the bottom of the batch file, result the bat can't be run.

Any ideas or work around?

Example.

in one cell (excel)

curl abcd....ef

curl ghij....kl

in .bat

curl abcd....

curl ghij....

ef

kl

Solution

I've taken your question to mean:

I wish to loop through every cell in column K, saving the contents of each cell to a single batch file.

---

Try the following VBA, obviously specifying your own output path:

Sub ExportRangetoFile()

    Dim ColumnNum: ColumnNum = 11   ' Column K
    Dim RowNum: RowNum = 1          ' Row to start on
    Dim objFSO, objFile

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\Users\Jonno\Documents\test\newcurl.bat")    'Output Path

    Dim OutputString: OutputString = ""

    Do
        OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine
        RowNum = RowNum + 1
    Loop Until IsEmpty(Cells(RowNum, ColumnNum))

    objFile.Write (OutputString)

    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

Or if your sheet contains empty rows:

Sub ExportRangetoFile()

    Dim ColumnNum: ColumnNum = 11   ' Column K
    Dim RowNum: RowNum = 0
    Dim objFSO, objFile

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\Users\Jonno\Documents\test\newcurl.bat")    'Output Path

    Dim OutputString: OutputString = ""

    Dim LastRow: LastRow = Application.ActiveSheet.Cells(Application.ActiveSheet.Rows.Count, ColumnNum).End(xlUp).Row

    Do
nextloop:
        RowNum = RowNum + 1
        If (IsEmpty(Cells(RowNum, ColumnNum).Value)) Then
            GoTo nextloop:
        End If
        OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine


    Loop Until RowNum = LastRow

    objFile.Write (OutputString)

    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

Comments

[Jonno]

I don't fully understand the problem. Can you post some example data, and the .bat file output?

[DavidPostill]

See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question.

[Jonno]

Should each cell in Column K be output to the same batch file, or different ones? This is quite an odd use for Excel if I'm honest, and the approach is a little round-about.

[D.chan]

all to the same batch file

[D.chan]

Thanks Jonno, that what i am working up to, but i miss the output part. Some issue maybe there is a Loop until IsEmpty which had some limitation. I guess that will fine tune later.*Hope

[D.chan]

was it possible to find the last value (not empty cell) within the column K

[D.chan]

as I contain empty cell in between rows.

[Jonno]

@D.chan Have added a version if there are blank rows.

[D.chan]

Thanks again Jonno. thumbs up for your all-rounded answer