creating a text record from a number preserving the leading zero

W

ww

Hello

The answer is more involved than changing the format of the variable
going into the array. i have already successfully entered the desired
value using "0233".

For internal reasons (my boss) I need to consistently produce a text
report,
comma separated with quotes. One of the fields must be a number
"0233" beginning with zero... I successfully populate te variant using

I have tried:
vMacBlotter(i, 3) = "0233"

which enters the datum correctly to the variant vMacBlotter.

However when I convert the variant to text with commas (using a
variation of the solution in the Microsoft Knowledge Base it comes out
as "233" in the text file.

It is a particularly perplexing problem because a debug.Print of the
value during the array-to-text routine shows that indeed it remains a
4 character text item as entered:

debug.Print """" & sVal & """"
"0233" (is the print)

So that means that somehow (good ol') Microsoft VBA is "interpreting"
this field prior to closing/saving the new text file.

Below is the routine I am using to convert the array to commas with
quotes. Please help:

Sub ArrayToCommaQuotes(TheArray() As Variant, sFileName As String)
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim sVal As String

'Get destination file name.
DestFile = sFileName

' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

'Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To UBound(TheArray, 1)

' Loop for each column in selection.
For ColumnCount = 1 To UBound(TheArray, 2)
sVal = TheArray(RowCount, ColumnCount)
' Write current cell's text to file with quotation
marks.
Print #FileNum, """" & sVal & """";

' Check if cell is in last column.
If ColumnCount = UBound(TheArray, 2) Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top