Need help with fixed width data macro

[lui]

Hello,

I currently have the following set of data in Excel (small excerpt):

00123456 John
00910112 Randy
00141516 Michael

Using a script that I received from another forum user, I receive the following results.

123456John
910112Randy
141516Michael

The script is as follows:

Sub ToText()
Dim f As String, fileToSave, r As Long
Dim abc(1) As String * 20, def(2 To 6) As String * 6
fileToSave = Application.GetSaveAsFilename
r = 1 'first row
f = FreeFile
Open fileToSave For Output As #f
Do
abc(1) = Cells(r, 1).Value: def(4) = Cells(r, 4).Value
def(2) = Cells(r, 2).Value: def(5) = Cells(r, 5).Value
def(3) = Cells(r, 3).Value: def(6) = Cells(r, 6).Value
Print #f, abc(1) & def(2) & def(3) & def(4) & def(5) & def(6)
r = r + 1
Loop While Not Cells(r, 1).Value = ""
Close #f
End Sub


The results are for the most part what I expected. However, the leading zeros are not saved. Is there anyway that I can save the leading zeros when running the script? I don't feel like going through thousands or records and adding a zero. Thanks very much.

-Will

 

[XL-Dennis]

Lui,

As far as I can understand the numbers are placed i the Column-A and the numbers You want to print to file should be 8 digits and not 6 digits.

Following revised procedure take this into consideration:

<PRE>
<FONT color=blue>Sub </FONT>ToText()

<FONT color=blue>Dim </FONT>f<FONT color=blue> As</FONT><FONT color=blue> String</FONT>, fileToSave, r<FONT color=blue> As</FONT><FONT color=blue> Long</FONT>

<FONT color=blue>Dim </FONT>abc(1)<FONT color=blue> As</FONT><FONT color=blue> String</FONT> * 20, def(2 <FONT color=blue>To </FONT>6)<FONT color=blue> As</FONT><FONT color=blue> String</FONT> * 6

fileToSave = Application.GetSaveAsFilename

r = 1 <FONT color=#ff0000>'first row
</FONT>
f = FreeFile

<FONT color=blue>Open </FONT>fileToSave <FONT color=blue>For </FONT><FONT color=blue>Output</FONT><FONT color=blue> As</FONT> #f

Do

abc(1) = CStr(Cells(r, 1).Value): def(4) = Cells(r, 4).Value

def(2) = Cells(r, 2).Value: def(5) = Cells(r, 5).Value

def(3) = Cells(r, 3).Value: def(6) = Cells(r, 6).Value

<FONT color=blue>Print </FONT>#f, "00" + abc(1) & def(2) & def(3) & def(4) & def(5) & def(6)

r = r + 1

<FONT color=blue>Loop</FONT> While Not Cells(r, 1).Value = ""

<FONT color=blue>Close </FONT>#f

<FONT color=blue>End Sub</FONT>




</PRE>

HTH,
Dennis