W
ww
Hello
For internal reasons i need to consistently produce a text report,
comma separated with quotes. One of the fields must be a number
"0234" beginning with zero... but no matter whether i use quotes or
use the format function it always results in "233".
I have tried:
vMacBlotter(i, 3) = "0233"
and
Format("0233", "")
which both appear to enter the datum correctly to the variant
vMacBlotter.
However when I convert to text with commas it comes out as "233".
Please help:
Sub QuoteCommaExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' Prompt user for destination file name.
'DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma
Exporter")
DestFile =
"C:\ACME\Trading\MacBlotters\ACME200408260000.csv"
' 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 Selection.Rows.count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.count
' Write current cell's text to file with quotation
marks.
Print #FileNum, """" & Selection.Cells(RowCount,
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.count 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
For internal reasons i need to consistently produce a text report,
comma separated with quotes. One of the fields must be a number
"0234" beginning with zero... but no matter whether i use quotes or
use the format function it always results in "233".
I have tried:
vMacBlotter(i, 3) = "0233"
and
Format("0233", "")
which both appear to enter the datum correctly to the variant
vMacBlotter.
However when I convert to text with commas it comes out as "233".
Please help:
Sub QuoteCommaExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' Prompt user for destination file name.
'DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma
Exporter")
DestFile =
"C:\ACME\Trading\MacBlotters\ACME200408260000.csv"
' 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 Selection.Rows.count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.count
' Write current cell's text to file with quotation
marks.
Print #FileNum, """" & Selection.Cells(RowCount,
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.count 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