Looping help

[MiskaTorn]

I am very new to this programming, and I have taken a pre-written script and am having troubles trying to figure out methods on how I can make this script bend to my needs.

I need for this script to automatically set the length value, but this value is differen't for each column.

I have about 30 columns and each one needs to be a different length. Though it is standard for each time I run the macro so it can be a set variable.

BTW, this script exports all the active colums and rows into a text-delimited text file.

Sub Export()
  Dim Length
  Dim z%, s%
  Dim TMP$
  Length = InputBox("FieldLength:")
  If Length = "" Then Length = 20
  Open "test.txt" For Output As #1
  For z = 1 To ActiveSheet.UsedRange.Rows.Count
    For s = 1 To ActiveSheet.UsedRange.Columns.Count
      TMP = TMP & CStr(Cells(z, s).Text) & String(Length - Len(Cells(z, s).Text), " ")
      Next s
      Print #1, TMP
      TMP = ""
  Next z
  Close 1
  MsgBox "The data names are stored under the following address:" & Chr(13) & _
    CurDir() & "\test.txt"
End Sub

[DRJ]

Hi - Welcome to the board

What columns do you want to loop this for and what are the lengths going to be for each?

[Joe4]

I need for this script to automatically set the length value, but this value is differen't for each column.

I have about 30 columns and each one needs to be a different length.

Please describe the logic/process. What is it going to set the length to? Do you want it to set each column to the maximum length needed to display all the contents in that column, or is it going to be some pre-defined value?

[MiskaTorn]

I am recieving info in a tab delimited format which I can open in excel easily.

But it needs to be entered into our system as text delimited. I tried saving it as a prn file then changing the extension to .txt but it is to long, and the Microsoft script doesn't seem to work for me.

I just need the script to writre all the info like it does now to a text file. Only let me specify a different width for each column rather than just one width for them all.

There are about 30 fields.

So the lengths are

1, 24, 24, 24, 13, 2, 6, 50, 50, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 10, 10, 9, 3, 6, 1, 1, 1, 17, 4, 1, 21, 17, 2

[Joe4]

As you have discovered, Excel limits creating prn files to 240 characters per line. There is a workaround for it. See:
http://www.support.microsoft.com/def...b;en-us;131554

[MiskaTorn]

Yeah, I saw that, only problem is that script does it to the form of the column.

It's a very lengthy process to go through each day for every file and set that width on the colums over and over again. This script is so close to doing what I need it to do, I am just not good enough at excel to make it happen yet, but I am trying.

[Joe4]

I believe that the workaround uses the column width of the columns in the worksheet to determine how many spaces to use when creating the export file.

Hence, all you would need to do is either create another macro that sets the column width, or just include the code at the beginning of the workaround macro. Your additional code would simply look like:

    Columns("A").ColumnWidth = 1
    Columns("B").ColumnWidth = 24
    Columns("C").ColumnWidth = 24
    etc.

[MiskaTorn]

That worked perfect. Thank you very much!

[MiskaTorn]

I was wrong. Does not work perfect.

The problem is now. All of the boxes are adding an extra character space at the end.

So if I put 1, it text delimits 2
.
I tried putting 0 width and then it shows nothing

[Joe4]

At the end of every field, or just one at the end of each row?