VBA Code to convert xls to txt file

[perry_fawn]

Hi,

I need to write a code to convert an excel spreadsheet to a txt file. I have found macro's that will do this in one shot but some column need to be formatted differently that others. For example col B needs to allow for 6 characters, left justified, with trailing blanks; col G needs to be 8 characters long in YYYYMMDD format; col I is a number that should be formatted to have no decimal points but needs to include 5 places before the decimal and 4 positions after, also it should be padded with leading and trailing zeros. Can anyone steer me on the right path?
Here is a bit of code that I found that preforms the conversion uniformly:

Public Sub FixedFieldTextFile()
        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(1, 6, 10, 2, 1, 1, 8, 8, 9, 9, 9, 2, 9, 2, 9, 1, 2, 2, 9, 8, 11, 9, 9, 9, 9, 3)
        nFileNum = FreeFile
        Open "Test.txt" For Output As #nFileNum
        For Each myRecord In Range("A1:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For i = 0 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
        Next myRecord
        Close #nFileNum
    End Sub

Thanks,
Fawn

 

[GlennUK]

You could also have an array of number formats to use, and use the Format function to format cell values during the processing.

Although I'm not sure what you mean here:

formatted to have no decimal points but needs to include 5 places before the decimal and 4 positions after, also it should be padded with leading and trailing zeros

 

[perry_fawn]

Yeah, it's a bit confusing. What I mean is that 18.838 should read 000188380. How would you suggest formatting this type of column?

 

[perry_fawn]

Is it possible to format the cells while putting them into a string and then use the remove function to remove any decimal places? I have added a VFormat Array to the code but I don't where to put the Format or Replace functions.

 

[GlennUK]

Maybe something like this:

                    sMyString = Format(.Offset(0, i).Value,vFormatArray(i))
                    sMyString = Replace(sMyString,".","")
                    sOut = sOut & DELIMITER & Left(sMyString  & _
                            String(vFieldArray(i), PAD), vFieldArray(i))

 

[perry_fawn]

The error that I am recieving with this code is that the Sub of Function is not defined for "Format". This might be because I am using Office 97. Do you know if I need to use a different formatting function?

 

[GlennUK]

Hi Fawn,

I've used the Format function in the past in Office 97, I'm sure. Can you try using Format in a test routine, with a simple example?

 

[perry_fawn]

You're right, the format command is not the problem. I have been able to get it to work using the vFormatArray on a single cell in the range. The only problem I seem to be having is with the

.Offset(0, i).Value

part. I have no idea how to fix this, any suggestions?