In spreadsheet I have Monitor 15" in A1.
I wrtie this spreadsheet as txt file where values
are seperated with tabular sign and after that when I edit
this file I have "Monitor 15""". Why Excell adds this 3 """" ?
Is there anything I can do to force excel not to add unexpected " ?
If your data is not too "complex" (the following is not a fully robust CSV
file creator, but it should correctly handle the vast majority of data types
one would normally have), you can use the code following my signature to
write out your data the way you indicated you want. Copy/Paste all of the
code below my signature into an Module (Insert/Module from VB editor's menu
bar) for maximum flexibility (the code can be called from any sheet in your
workbook) or into a specific sheet's code window if you know it will only be
run form that one worksheet. Your code simply needs to call PrintCVSfile
with the sheet you want to be outputted as the active sheet.
Rick
Sub PrintCVSfile()
Dim FF As Long
Dim X As Long, Y As Long
Dim StartRow As Long, EndRow As Long
Dim StartColumn As Long, EndColumn As Long
Dim LineOfText As String
Dim FileName As Variant
FileName = Application.GetSaveAsFilename(fileFilter:= _
"CSV (Comma Separated) (*.csv), *.csv")
If FileName = False Then Exit Sub
ActualUsedRange StartRow, EndRow, StartColumn, EndColumn
FF = FreeFile
Open FileName For Output As #FF
For X = StartRow To EndRow
LineOfText = ""
For Y = StartColumn To EndColumn
If InStr(ActiveSheet.Cells(X, Y).Value, ",") Then
LineOfText = LineOfText & """" & ActiveSheet.Cells(X, Y) & """"
Else
LineOfText = LineOfText & ActiveSheet.Cells(X, Y)
End If
If Y < EndColumn Then LineOfText = LineOfText & ","
Next
Print #FF, LineOfText
Next
Close #FF
End Sub
Sub ActualUsedRange(StartRow, EndRow, StartColumn, EndColumn)
Dim X As Long
If VarType(StartRow) > 6 Or VarType(EndRow) > 6 Or _
VarType(StartColumn) > 6 Or VarType(EndColumn) > 6 Then
MsgBox "Numeric data types only!", vbCritical, "Data Type Error"
Exit Sub
End If
With ActiveSheet.UsedRange
StartRow = .Row + .Rows.Count - 1
StartColumn = .Column + .Columns.Count - 1
For X = .Row To .Row + .Rows.Count - 1
With ActiveSheet.Cells(X, Columns.Count)
If EndColumn < .End(xlToLeft).Column Then
EndColumn = .End(xlToLeft).Column
End If
End With
With ActiveSheet.Cells(X, 1)
If ActiveSheet.Cells(X, 1).Value <> "" Then
StartColumn = 1
ElseIf StartColumn > .End(xlToRight).Column Then
StartColumn = .End(xlToRight).Column
End If
End With
Next
For X = .Column To .Column + .Columns.Count - 1
With ActiveSheet.Cells(Rows.Count, X)
If EndRow < .End(xlUp).Row Then
EndRow = .End(xlUp).Row
End If
End With
With ActiveSheet.Cells(1, X)
If ActiveSheet.Cells(1, X).Value <> "" Then
StartRow = 1
ElseIf StartRow > .End(xlDown).Row Then
StartRow = .End(xlDown).Row
End If
End With
Next
End With
End Sub
