Conversion to Text file

[Steph]

Hello all. I have to manupulate an excel sheet into a specific format, then
save it as a text file to allow for an EDI upload into SAP. The format the
text file has to be in is very specific, ie an exact number of spaces and
characters.

I have the outline of what needs to be done, my question is this - I am
going to do several concatenations to get the data in the correct format. A
few of the concatenations consist of 130 spaces (hitting the space bar 130
times). I'd like to write code to manipulate the data. Is there a way to
tell excel to insert 130 spaces, rather than me hitting the spacebar 130
times? So pseudo vba would look like:
=concatenate(0001,650010,X,1244689,{syntax for 130 spaces},X,650010)

Thanks!

 

[djh]

One way is to put the 130 spaces in a cell (say A1 ) the
your formula would be
=concatenate(0001,650010,X,1244689,$A$1,X,650010)

 

[Tim]

.... and if you want an easy / accurate way of putting 130 spaces into the
cell (or use it in other code): -

Private Sub CommandButton1_Click()
Dim i As String

For x = 1 To 130
i = i + Chr(32)
Next
ActiveCell.Value = i 'this is the cell where you want the spaces
End Sub

unless someone can think of an easier way

tim

 

[Myrna Larson]

How about the Spc function, i.e.

ActiveCell.Value = Left$(i & Space$(130), 130)

 

[Myrna Larson]

Another line that will work is

ActiveCell.Value = i & Space$(130-Len(i))