Excel to text file

That certainly is possible.
If I get it right you want to minimize the amount of information stored in the text files.

if the columns with fixed data are always the same columns, you could completely take these out of the text file and insert constant values instead.
Otherwise, you could insert a number of definition lines such as e.g.

constant value 'H' column 1
constant value 'A' column 2, 3,  12, 26

You are almost there.

I want to remove in excel some columns where it is always the same value, but still have it in the text file when created.

Ex: "H" and "A" are default value. So i don't what them in the excel file, but when i will create the text file, it will add them in.

I'm not entirely sure I got the same from your question as akoster but I *think* what you need to do is add the constants into the 'i' loops where you are building the output string using a case statement:-

For  example:

For i = 0 To 41
  select Case i
    Case 12
       sOut = sOut & DELIMITER & "Test1"
    Case 6
       sOut = sOut & DELIMITER & "Test2"
    Case 13,14
      sOut = sOut & DELIMITER & "Test3"
    Case 21 to 25
      sOut = sOut & DELIMITER & "Test4"
    Case Else
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
Next i

Perhaps a before and after example (with a smaller array!) would help ?

Ah.

do you want to visually get rid of the columns or you want them to be removed ?

if visibility is the problem, the easiest way would be to hide the columns because you then would not have to change the macro.

when you want to remove the columns it might be wise to write out the export functionality like this :

for row = 11 to range("A" & rows.count).end(xlup).row
   sOut = process(cells(1,1), 1)                                      '-- first entry so no delimiter
   sOut = sOut & DELIMITER & process(cells(2,1), 1)     '-- second entry, dynamic
   sOut = sOut & DELIMITER & process(cells(3,1), 12)   '-- dynamic entry
   sOut = sOut & DELIMITER & process(cells(4,1), 6)     '-- dynamic entry
   sOut = sOut & DELIMITER & process(cells(5,1), 6)     '-- dynamic entry
   sOut = sOut & DELIMITER & process(cells(6,1), 12)   '-- dynamic entry   
   sOut = sOut & DELIMITER & "fixed text"                    '-- static entry
   sOut = sOut & DELIMITER & process(cells(26,1), 11)   '-- dynamic entry
   [....]

Select allOpen in new window

and include the process function

function process(value, size) as string
    process = left(value & string(size, " "), size)
end function

Select allOpen in new window

another option (less work when only a small amount of fixed columns involved) is to change the macro to

For Each myRecord In Range("A11:A" & _
   Range("A" & Rows.Count).End(xlUp).Row)
   With myRecord
      For i = 0 To 41
         select case i
            case 5
               sOut = sOut & DELIMITER & "fixed text for column 5"
            case 12
               sOut = sOut & DELIMITER & "fixed text for column 12"
            case else
               sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
         end select
      Next i

Select allOpen in new window

That would be an example of what i need to do.

Excel sheet, you will see the 3 column with data, and the .doc file, just remove the.doc and you will have the text file example.
ord.02111004105914.doc
ex.xlsx

with this updated version
example-excel-text-to-file.xls

i get this result
ordtemp

in order to use the ex.xlsx version as template, it would come down to

 ex.xlsm

Hello akoster

From the example you gave me, you have added the H and A in column A and B.

I want to be able to put Test1 in column A and it would still put in the text file HA before the Test1..

my bad, should have directly used the supplied ex.xlsx file to build upon.
Have you checked the ex.xlsm file ?

yes it is working.


If i need to add more automation like the H and A, but after column C, i'm not to sure how to manage that based on the code:

Public Sub GenerateFormattedOrderImportFile()
        Const DELIMITER As String = "" 'Normally none
        Const PAD As String = " "   'or other character
        Dim vFieldArray As Variant
        Dim myRecord As Range
        Dim nFileNum As Long
        Dim i As Long
        Dim sOut As String
        
        'vFieldArray contains field lengths, in characters, from field 1 to N
        vFieldArray = Array(36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)
        nFileNum = FreeFile
        Open "C:\Documents and Settings\All Users\Desktop\ordtemp." For Output As #nFileNum
        'Open "C:\Documents and Settings\All Users\Desktop\temp\ordtemp.txt" For Output As #nFileNum
        For Each myRecord In Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                .Select
                sOut = "H" & DELIMITER & "A"
                For i = 0 To 41
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
            End With
        Next myRecord
        Close #nFileNum
        
End Sub

Select allOpen in new window

if you want to place 'X' after column C and 'Q' after column E, use :

[...]
For i = 0 To 41
   sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
   '-- column C : i=2
   if i = 2 then sOut = sOut & DELIMITER & "X"
   if i = 4 then sOut = sOut & DELIMITER & "Q"
Next i
[...]

Select allOpen in new window

when you place the additional code below the sout = sout  & delimiter & left([...] line, entries will be placed after the indicated column.
when you place the additional code above the sout = sout  & delimiter & left([...] line, entries will be placed before the indicated column.

Hello akoster

Sorry for the delay,

OK, let me try this. I will give you the feed back.

Thanks again

no problem !

Hello akoster,

It is working. But small question, If for example, after the 3\4 of the excel row that as been transferred in the text file, i need to transfer the last columns in the row just under the first on in the text file, how can i do that:

Ex:

HAMH2440446001WESTOBHOST  24          Example                   20100925110020100926170020100925000120100926170024                                             COL                                                                       M                                                                                                                                                                                                                                                                                  
P MH2440446001WESTOB100   1100                                  15                               

Select allOpen in new window

In the excel sheet, this is only 1 row, but in the text file, it is 2, the second, starting with "P".

Is that possible?

Ohh that is working like that

 For Each myRecord In Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                .Select
                sOut = "H" & DELIMITER & "A"
             For i = 0 To 4
   sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
   '-- column C : i=2
   If i = 2 Then sOut = sOut & DELIMITER & "H"
   If i = 4 Then sOut = sOut & DELIMITER & "A"
   If i = 6 Then sOut = sOut & DELIMITER & Format(Now, "yyyymmddhhmmss")
     Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
   

                sOut = "P" & DELIMITER
    For i = 5 To UBound(vFieldArray)
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                            
                             Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
                
        End With

Select allOpen in new window

Hello again,

I'm doing lots of test right now and i dont see that it follow these criterias:

vFieldArray = Array(36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

Select allOpen in new window

The arrays should be where the column data / value starts on the text file.

But is does not if i use :

   If i = 0 Then sOut = sOut & DELIMITER & Range("E1").Text
   If i = 1 Then sOut = sOut & DELIMITER & "HOST"
   

Select allOpen in new window

If i = 0 Then sOut = sOut & DELIMITER & Range("E1").Text should be just after column A, at position 19 on the test file,

and

 If i = 1 Then sOut = sOut & DELIMITER & "HOST" should be just after but at position 25 on the text file.

after these 2, then it return to column B of the excel sheet.

but if i remove this conditions, everything is in place, based on Array(36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

In a quick word,  all if condition does nor respect the vFieldArray . It just follow right away the column data result.

But if a result from a column is transferred in the text file, the IF condition may not be just right after it.

ex:

column 2 = Test 1
If condition = Test2

The result could be: "Test1            Test 2" and not automatically "Test1Test2"

That is way i need to follow the vFieldArray : (36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

Does it make sense?

that is correct. If you want to have the added texts to also follow the vfieldarray text lengths, you can update lines like

If i = 2 Then sOut = sOut & DELIMITER & "H"

Select allOpen in new window

to

If i = 2 Then sOut = sOut & DELIMITER & Left("H" & String(vFieldArray(i), PAD), vFieldArray(i))

Select allOpen in new window

pay attention though that the use of variable i to link to the 'i' est column in the worksheet requires carefull consideration on the setup of the vFieldArray.
when the i=2 variable inserts contents of cell C2 with length 12 characters, the "H" letter will also have a length of 12 characters.

instead it might be a good idea to insert defined texts with hardcoded lengths like this :

[...]
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
[...]
If i = 0 Then sOut = sOut & DELIMITER & left(Range("E1").Text & string(10, PAD), 10)
if i = 1 Then sOut = sOut & DELIMITER & left("HOST" & string(20, PAD), 20)
If i = 2 Then sOut = sOut & DELIMITER & Left("H" & String(12, PAD), 12)
[...]

Select allOpen in new window

Hello akoster

Something is strange here

if i do this:
If i = 0 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(16, PAD), 6)
If i = 0 Then sOut = sOut & DELIMITER & Left("HOST" & String(21, PAD), 4)

or

If i = 0 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(20, PAD), 6)
If i = 0 Then sOut = sOut & DELIMITER & Left("HOST" & String(28, PAD), 4)

I have the same result.


Question for you, is there a way to use this for everything? even to manage the filed for the column?

If i = 0 Then sOut = sOut & DELIMITER & Left("H" & String(1, PAD), 1)
If i = 1 Then sOut = sOut & DELIMITER & Left("A" & String(2, PAD), 1)
If i = 2 Then sOut = sOut & DELIMITER & Left(COLUMN A.Text & String(3, PAD), 12)
If i = 2 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(15, PAD), 6)
If i = 3 Then sOut = sOut & DELIMITER & Left("HOST" & String(22, PAD), 4)


Ex: (0, PAD), 1) = Space 1 on the file, with a length of 1 digit.
(1, PAD), 1) =  Space 2 on the file, with a length of 1 digit.
(3, PAD), 12) =  Space 3 on the file, with a length of 12 digit......

this is not strange, it's the way it is supposed to be.

the part after the delimiter consists of two functions :

 - left
 - string

for simplicity, let's say that E2 contains 'A'
if you say range("E2").text & string(16, "*"); thus command is 'add 16 characters after text in E2'; you will get
[A****************]
if you then use left(range("E2").text & string(16, "*"), 6); thus command is 'take the first 6 characters of this'; you will get
[A*****]

which is the same as saying 'take the first 6 characters of (add 20 characters after text in E2)'

Thanks,

I will try this and let you know the result.

Hello,

Sorry for the delay. Thanks again for your help, now it work.