Excel VBA .txt Output Line Break Issue

[JustCaz]

Hi All,

I have an excel document tab which pulls a bunch of information from other tabs. It then formats the information such that every cell is exactly 14 characters long - either by appending spaces at the end with rept(char(16), x) or concatenating.
I do that for approximately 500 rows for a single "unit" and then repeat for each column for additional "units" up to 25. See attached Examble_Tab

Now the macro that I have creates a .txt file and outputs all the cells in the individual column creating a neatly laid out Config.txt file and repeats creating a different Config.txt for each additional column if required.
See attached Output_Config.txt

This Output_Config.txt file should act as a configuration file for my embedded product which then reads certain rows depending on what the device is trying to do. However with the Output_Config.txt file the embedded systems file_seek is going to the wrong locations. When I compare the excel VBA generated Output_Cofig.txt to a my manually created txt file (see attached Manual_Config.txt) which works, I noticed it is formatted as Macintosh(CR) whereas the non working one is formatted as Windows(CRLF). I am guessing this is the issue.

Is there a way to resolve this in VBA? I tried a couple different methods and it appears the end of a cell automatically does this (crlf). Could I go back one character

Sub uSD_Config()

Dim CurrentDirectory As String 'Current directory variable
Dim NameDirectory As String 'New directory variable
Dim SubDirectory As String '"Unit #" subdirectory
Dim unitNo As Integer 'Column position integer
unitNo = 1 'Set initial column value to 1

'''''''''''''''''''''' MAKE MASTER "uSD Files" DIRECTORY (do once) ''''''''''''''''''''''
CurrentDirectory = Application.ActiveWorkbook.Path 'Set filepath to save to as current directory
NameDirectory = Cells(1, 1).Value 'Create filepath name WO# & "uSD Files"

If Len(Dir(CurrentDirectory & "\" & NameDirectory, vbDirectory)) = 0 Then
MkDir CurrentDirectory & "\" & NameDirectory 'Create new directory from above
Else
'MsgBox "uSD Directory Alread Exists", vbOKOnly, "Okay"
'Exit Sub
End If
''''''''''''''''''''''''''''''' End do once section '''''''''''''''''''''''''''''''''''''

'''''''''''''''''' Loop to write out to each Unit # Config.txt file '''''''''''''''''''''
For unitNo = 1 To 25
If Cells(2, unitNo).Value = "Yes" Then
n = n + 1
SubDirectory = Cells(3, unitNo).Value 'Define new name directory for unit 1
If Len(Dir(CurrentDirectory & "\" & NameDirectory & "\" & SubDirectory, vbDirectory)) = 0 Then
MkDir CurrentDirectory & "\" & NameDirectory & "\" & SubDirectory 'Create new diretory for unit 1
Else
'Do nothing, or warning, or give notice and skip, or overwrite existing?
End If

'Config.txt file portion
myFile = CurrentDirectory & "\" & NameDirectory & "\" & SubDirectory & "\Config.txt" 'Create txt file in directory filepath and new unit number directory
Range(Cells(4, unitNo), Cells(259, unitNo)).Select

Open myFile For Output As #1 'Set txt file as data buffer output
Set Rng = Selection
'Define data to buffer output
For i = 1 To 254
For j = 1 To Rng.Columns.Count
cellValue = Rng.Cells(i, j).Value
If j = Rng.Columns.Count Then
Print #1, cellValue
Else
Print #1, cellValue,
End If
Next j
Next i
Close #1
Close #1

End If

Next unitNo


End Sub

[TMS]

Maybe this

Please Login or Register  to view this content.

should be this

Please Login or Register  to view this content.

[JustCaz]

Thanks for the response!

Unfortunately not, the output then becomes:

1.000 ,
500.00 ,
42000 ,

and has the same Windows(CRLF) encoding.

If we think about the raw output for those first three lines about, wouldnt it be something like:
1.000 ,/n/r500.00 ,/n/r42000/n/r
Is there a way to delete those last two characters?
Or a way to move the cursor back two?

[Marc L]

as CrLf is the Windows standard end of line, for Mac it should be Lf

so amend the Print statement just after the If codeline as Print #1, cellValue; vbLf;

[JustCaz]

I believe Linux is Lf and Macintosh is Cr. I just swapped the vbLf; with vbCr; and it worked perfectly!
I must admit I am surprised the solution was so easy... I really need to learn VBA syntax.

Thank you all for the help!

[TMS]

Thanks for the rep.