Conversion to Text file
 

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!

Conversion to Text file
 

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)

-----Original Message-----
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!


.

Conversion to Text file
 

.... 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

"djh" wrote in message
...
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)

-----Original Message-----
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!


.

Conversion to Text file
 

How about the Spc function, i.e.

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


On Tue, 17 Aug 2004 19:16:44 +0100, "Tim"
wrote:

... 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

"djh" wrote in message
...
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)

-----Original Message-----
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!


.

Conversion to Text file
 

Another line that will work is

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


On Tue, 17 Aug 2004 19:16:44 +0100, "Tim"
wrote:

... 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

"djh" wrote in message
...
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)

-----Original Message-----
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!


.