Preserve column widths: sheet to text file.

I have a sheet of data that I need to write to a text file, but I need to preserve the exact column widths.

I am using the VBA code below, but it takes 40 minutes to run when there is 68,000 rows of data.

Is there a better way?


Public Sub MF_Export_File()
        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 = Array(9, 4, 3, 5, 2, 8, 8, 30, 6, 10, 8, 2, 3, 9, 25)
        nFileNum = FreeFile
        Open Range("MF_XFER_Path").Value & "\" & Range("To_MF_File").Value 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

Open in new window

Here's the text file (after 40 minutes) . . .

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

replace this line
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                    String(vFieldArray(i), PAD), vFieldArray(i))

by this line
sOut = sOut & DELIMITER & Left(.Offset(0, i).Value2 & _
                    String(vFieldArray(i), PAD), vFieldArray(i))

Rory mentioned it in the previous question.
84000 rows took 15 sec.
mike2401Author Commented:
OMG:  40 minutes -> 15 seconds.  You both are amazing!

Your expertise is much appreciated.

@Rora  Roy Archibald, please reply to this thread so I can split the points.


For your info it was news to me as well that Value2 is a speedy Gonzales type of property. We never cease to learn. Hv sent a msg to Royra to attend to this one.

Rory ArchibaldCommented:
There's no need to worry about points for me, but it's not so much that .Value2 is speedy (though it is generally a little faster than Value if you don't specifically need Date or Currency types) as that .Text is really slow!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mike2401Author Commented:
Thanks again, you both rock!

(100 finder-fee points go to @gowflow for spelling it out for me: I didn't exactly understand how easy Rory's solution was to implement!)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.