Exporting As Text with X Number of Spaces Between Data

Exporting As Text with X Number of Spaces Between Data

Post by Fonz » Fri, 03 Oct 2003 13:00:15


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!
 
 
 

Exporting As Text with X Number of Spaces Between Data

Post by J.E. McGim » Fri, 03 Oct 2003 15:05:37

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


In article <166f01c38899$af9dbcf0$ XXXX@XXXXX.COM >,

 
 
 

Exporting As Text with X Number of Spaces Between Data

Post by Fonz » Fri, 03 Oct 2003 22:39:51

Thanks!


B, 4
between columns
_
_
_

data
as "Fixed
exported
 
 
 

Exporting As Text with X Number of Spaces Between Data

Post by Dave Peter » Sat, 04 Oct 2003 09:08:42

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.






--

Dave Peterson
XXXX@XXXXX.COM