Results 1 to 8 of 8

VBA Code to convert xls to txt file

This is a discussion on VBA Code to convert xls to txt file within the Excel Questions forums, part of the Question Forums category; Hi, I need to write a code to convert an excel spreadsheet to a txt file. I have found macro's ...

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default VBA Code to convert xls to txt file

    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:
    Code:
     
    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

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: VBA Code to convert xls to txt file

    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
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VBA Code to convert xls to txt file

    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?

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VBA Code to convert xls to txt file

    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.

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: VBA Code to convert xls to txt file

    Maybe something like this:
    Code:
                        sMyString = Format(.Offset(0, i).Value,vFormatArray(i))
                        sMyString = Replace(sMyString,".","")
                        sOut = sOut & DELIMITER & Left(sMyString  & _
                                String(vFieldArray(i), PAD), vFieldArray(i))
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VBA Code to convert xls to txt file

    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?

  7. #7
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: VBA Code to convert xls to txt file

    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?
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  8. #8
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VBA Code to convert xls to txt file

    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
    Code:
    .Offset(0, i).Value
    part. I have no idea how to fix this, any suggestions?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com