Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date conversion on text file import | Excel Discussion (Misc queries) | |||
Disable conversion from text to number from .csv file | Excel Discussion (Misc queries) | |||
Text file conversion to excel | Excel Discussion (Misc queries) | |||
File conversion-text encoding issue | Excel Discussion (Misc queries) | |||
Conversion to Text file format error | Excel Discussion (Misc queries) |