Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a text record from a number preserving the leading zero
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
leading zeros when uploading a number from text file | Excel Discussion (Misc queries) | |||
zero supress leading zeros when chg format from text to number | Excel Worksheet Functions | |||
creating a text record from a number preserving the leading zero | Excel Programming | |||
Number to text with leading zero | Excel Programming | |||
Force a Number to be Formatted as Text (keep leading 0's) | Excel Programming |