Excel VBA extra Newline getting inserted through print statement

Question

Through a Excel VBA macro, I'm trying to print up to 10 space seperated arguments for a selected range in excel.

For example, I have the 24 values in my selection range A1:A24 - (say Val1, Val2, Val3, Val4, etc.) Using the following VBA code, I want to get the output in the "outfile.bat" as

"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" Val1 Val2.... Val10

"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" Val11 Val2.... Val20

"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" Val21 Val22 Val23 Val24

i.e. each line should get printed with maximum of 10 argument values (seperated by a space). Anything above that should be moved to next line (again max of 10 space seperated arguments)

Somehow, the following code is (1) NOT keeping the output to the same line and (2) Inserts a newline at the 10th value, but not at the 20th, 30th and other values.

It produces the following:

"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
Val1
"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
Val2
C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
Val3

and so on....

Here is my code:

Private Sub GetChromeFile_Click()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer, a As Integer
myFile = "C:\Users\User1\" & "outfile.bat"
Set rng = Selection

Open myFile For Output As #7
a = 0
For i = 1 To rng.Rows.Count
    Print #7, Chr(34) & "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" & Chr(34)

        a = a + 1
        cellValue = rng.Cells(i).Value
         If (a = 10) Then
            Print #7, " " & cellValue & vbNewLine
        Else
            Print #7, " " & cellValue
        End If
Next i


Close #7

Range("F5").Value = " Done!"
End Sub

Please let me know where this may be going wrong. Thanks

Quick test, try just Print #7, """C:\ProgramFiles\...\chrome.exe"""? (in other words, use double quotes to print a quote instead of the CHR()) — BruceWayne
Thanks for the input @BruceWayne. This is included in DavidZemens's answer. Appreciate your help! — Aquaholic

David Zemens David Zemens · Accepted Answer · 2017-05-04T15:45:02

The print statement prints a line to the file, so adding vbNewLine at the end of each is redundant. You're also making calls to Print for each argument value (cellValue in your code), which is why those are appearing on their own line.

You can most likely construct the entire file contents as a single string, and then use a single Print statement to write the whole file. If you're dealing with an enormous amount of data, you may need to segment it but for most cases this should work:

Option Explicit
Sub writebat()
Const pathTxt$ = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"" "
Dim lineTxt As String
Dim cellValue As String
Dim fname As String
Dim ff As Long
Dim a As Long
Dim i As Long
Dim rng As Range

Set rng = Selection ' Range("A1:A37")

fname = "C:\Users\User1\" & "outfile.bat"    ' "C:\debug\output.txt"

ff = FreeFile()

Open fname For Output As #ff
    lineTxt = pathTxt
    a = 1
    For i = 1 To rng.Rows.Count
        '## Add the cell value to the string
        lineTxt = lineTxt & rng.Cells(i).Value & " "
        If a Mod 10 = 0 Then
            '## Start a new line with the executable path
            lineTxt = lineTxt & vbNewLine & pathTxt
        End If
        a = a + 1
    Next
    Print #ff, lineTxt
Close #ff
End Sub

This yields the following output:

Excel VBA extra Newline getting inserted through print statement

1 Answers