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

  • 2
    I don't fully understand the problem. Can you post some example data, and the .bat file output? – Jonno Feb 26 '16 at 9:29
  • 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. – DavidPostill Feb 26 '16 at 9:32
  • 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. – Jonno Feb 26 '16 at 9:45
  • all to the same batch file – D.chan Feb 26 '16 at 9:52
up vote 1 down vote accepted

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
  • 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 Feb 29 '16 at 1:17
  • was it possible to find the last value (not empty cell) within the column K – D.chan Feb 29 '16 at 1:20
  • as I contain empty cell in between rows. – D.chan Feb 29 '16 at 3:31
  • @D.chan Have added a version if there are blank rows. – Jonno Feb 29 '16 at 13:38
  • Thanks again Jonno. thumbs up for your all-rounded answer – D.chan Mar 1 '16 at 1:09

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.