Excel to space delimited txt file with 900 characters

[ginny777]

Hi! I am a newbie and have searched for an answer to my question on the messages boards, but don't see one that quite fits.

I will be receiving a file in Excel which contains a column for the first name, last name, a date and an email address (it could possibly have more - street address, etc.- but this is the norm). This file will need to be turned into a space delimited .txt file with no tabs. The text file will need 42 "columns" each a specific length for a total of 900 characters in each row. A lot of the columns will be blank, but the spaces must be there, and some of the columns will need to be populated with the same default data in each cell.

Some help with this would be greatly appreciated, right now I'm doing this by hand, but we will have thousands in upcoming files, and it will be too time consuming!

Extra help - one of the columns will need to be a ascending number sequence - 11 characters and prepadded with zeros (00000000001,00000000002 etc.), and the date column will need to be in CCYY-MM-DD (2011-04-08).

Ginny

 

[ginny777]

The sequence is in the fifth column from the end (11 characters) and the date is in the 17th from the start (10 characters)

 

[RSXchin]

also, if you put all the files in the same folder, I can make it do the macro on all workbooks in the folder. But I need to know where you want them to be saved, and how they're named.

 

[RSXchin]

Is the date TODAY'S date? or there already a date in it?

same goes for the ID number

 

[ginny777]

I have a folder on my desktop for them (C:\Documents and Settings\Ginny\Desktop\OOFolder)

The name is the date with underscores and a digit holder in case there is more than one for a given date: 4_15_11_1.txt and a second would be 4_15_11_2.txt etc.

 

[ginny777]

OOFolder - these are the letter O not zeros, if that matters.

 

[RSXchin]

I mean the date column. are there dates in it? or do you want to put in today's date?

 

[ginny777]

The dates will be in the excel file in this format: 4/15/11

 

[RSXchin]

The dates will be in the excel file in this format: 4/15/11

so the date's are already in there?

 

[ginny777]

Yes, just in the wrong format.

 

[RSXchin]

this is some pretty dirty code... but let me know if it works for you. let me know if you don't understand what to do with this

Sub XL_to_text()
    Dim i As Long, j As Long, k As Long, l As Long, lastrow As Long
    Dim oFS As FileSystemObject
    Dim oTS As TextStream
    Dim SpaceArray As Variant, DataLine As String
    DataLine = Empty
    SpaceArray = Array("", 1, 10, 30, 30, 1, 40, 40, 40, 30, 3, 6, 4, 9, 1, 1, 3, 10, 10, 1, 1, 1, 1, 1, 2, 128, 10, 10, 6, 10, 283, 10, 25, 9, 10, 25, 1, 4, 11, 10, 2, 30, 40)
    If Not DirExists("C:\Documents and Settings\Ginny\Desktop\OOFolderTEXTS\") Then
        MkDir ("C:\Documents and Settings\Ginny\Desktop\OOFolderTEXTS\")
    End If
    FilesName = InputBox("What is the name of the text you are creating?" & vbNewLine & _
                        "(don't forget to include the "".txt"" part)")
    
    Set oFS = New FileSystemObject
    Cells.Replace What:=Chr(160), replacement:=Chr(32)
    Application.ScreenUpdating = False
    For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            Cells(j, i) = Trim(Cells(j, i))
        Next i
    Next j
    Application.ScreenUpdating = True
    
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("am:am").NumberFormat = "00000000000"
    Range("am1").Select
    Range("am1") = "00000000001"
    Selection.AutoFill Destination:=Range("e1:e" & lastrow), Type:=xlFillSeries
    Range("q:q").Select
    Selection.NumberFormat = "yyyy/mm/dd"
    '
    '
    For k = 1 To lastrow
        For l = 1 To UBound(SpaceArray)
             DataLine = DataLine & Spacer(Cells(k, l), "", (SpaceArray(l)))
        Next l
        Set oTS = oFS.OpenTextFile("C:\Documents and Settings\Ginny\Desktop\OOFolderTEXTS\" & FilesName, ForAppending, True)
        oTS.Write (DataLine & vbCrLf)
        Set oTS = Nothing
        DataLine = Empty
    Next k
    Set oFS = Nothing
    Set oTS = Nothing
End Sub

Function Spacer(Left_Side As String, Right_Side As String, Total_Length As Long) As String
    Left_Side = Trim(Replace(Left_Side, Chr(32), " "))
    Right_Side = Trim(Replace(Right_Side, Chr(32), " "))
    Spacer = Left_Side & Space(Total_Length - (Len(Left_Side) + Len(Right_Side))) & Right_Side
End Function
Function DirExists(sSDirectory As String) As Boolean
    If Dir(sSDirectory, vbDirectory) <> "" Then DirExists = True
End Function