Exporting As Text with X Number of Spaces Between Data

[Fonz]

Is there a way to export data in a MS Excel spreadsheet
as text and define the amount of spaces between the data
being exported? The number of spaces need to vary.

This concept is the opposite of importing text as "Fixed
Width" and creating column break lines. Instead, I'd
like to export columns into a text file and specify the
amount of spaces between the text that is being exported
from each column. The amount spaces in the text file
needs to be different between each set of column data.

Thanks!

 

[J.E. McGimpsey]

One way:

Say you have 5 columns, and you want 2 spaces between A & B, 4
between B & C, 3 between C & D and 5 between D & E:

Public Sub VariableSpaces()
Dim dArr As Variant
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

dArr = Array(2, 4, 3, 5) 'number of spaces between columns
Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
Print #1, .Item(1).Text; Spc(dArr(0)); _
.Item(2).Text; Spc(dArr(1)); _
.Item(3).Text; Spc(dArr(2)); _
.Item(4).Text; Spc(dArr(3)); _
.Item(5).Text
End With
Next myRecord
Close #1
End Sub

 

[Fonz]

Thanks!

-----Original Message-----
One way:

Say you have 5 columns, and you want 2 spaces between A & B, 4
between B & C, 3 between C & D and 5 between D & E:

Public Sub VariableSpaces()
Dim dArr As Variant
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

dArr = Array(2, 4, 3, 5) 'number of spaces between columns
Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
Print #1, .Item(1).Text; Spc(dArr(0)); _
.Item(2).Text; Spc(dArr(1)); _
.Item(3).Text; Spc(dArr(2)); _
.Item(4).Text; Spc(dArr(3)); _
.Item(5).Text
End With
Next myRecord
Close #1
End Sub



.

 

[Dave Peterson]

I would have guessed that you wanted each field to have the same width.

So column A would be padded with spaces to make each the beginning of column B
line up pretty.

If yes, then a minor modification to J.E.'s code would do it:

Option Explicit
Public Sub FixedWidth()
Dim dArr As Variant
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

dArr = Array(12, 14, 13, 15) 'length of each field
Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
Print #1, Left(.Item(1).Text & Space(dArr(0)), dArr(0)); _
Left(.Item(2).Text & Space(dArr(1)), dArr(1)); _
Left(.Item(3).Text & Space(dArr(2)), dArr(2)); _
Left(.Item(4).Text & Space(dArr(3)), dArr(3)); _
.Item(5).Text
End With
Next myRecord
Close #1
End Sub


But if all the values in each column are the same number of characters, then
never mind.