Hi everyone! I wanted to see if there is a way to get rid off double quotes when programmatically writing string to a .txt file. In order for me to further use data from the text file it needs to be without double quotes. I tried to use Left() and Right() on the string, however, that yields no result. Obviously, the double quotes are added to the beginning and the end of the string when I use "Write" function when right to text file. That explains why Left() and Right()would not work. Unfortunately, I can't think of a way to solve this problem. I guess there is a way to read string from text file into a character array and get rid off the first and last elements but I'm not sure how it is done (or if it going that route would solve my problem)...Below is the code that is behind my worksheet...
Currently, the output in the text file looks like this:
"1125014000,00613994181862,2007-38,12
1125014000,00613994181862,2007-39,12
1125014000,00613994181862,2007-40,12
1125014000,00613994181862,2007-41,12
1125014000,00613994181862,2007-42,12
1125014000,00613994181862,2007-43,12
1125014000,00613994181862,2007-44,12
1125014000,00613994181862,2007-45,12
"
I would appreciate any input...Thanks much!!!
Valeriya
Code:
Option Explicit
Sub CrossTab()
Dim cel As Range
Dim rwIndex As Integer
Dim colIndex As Integer
Dim lngLen As Long
Dim allData, charData, kfData As String
allData = ""
charData = ""
kfData = ""
For rwIndex = 2 To FindLastRowColB
charData = ""
For colIndex = 1 To 2
charData = charData & Cells(rwIndex, colIndex).Value & ","
Next
For colIndex = 3 To FindLastColumnA2()
allData = allData & charData & Cells(1, colIndex).Value & "," & Cells(rwIndex, colIndex).Value & vbCrLf
Next
Next rwIndex
'MsgBox (allData)
TextIOWrite (allData)
End Sub
Public Function FindLastRowColB()
FindLastRowColB = Range("B65536").End(xlUp).Row
End Function
Public Function FindLastColumnA2()
FindLastColumnA2 = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
End Function
Sub TextIOWrite(sText As String)
Dim sFile As String
Dim iFileNum As Integer
sFile = "C:\Documents and Settings\Kirill\Desktop\textio.txt"
iFileNum = FreeFile
Open sFile For Output As iFileNum
Write #iFileNum, sText
Close #iFileNum
End Sub
Currently, the output in the text file looks like this:
"1125014000,00613994181862,2007-38,12
1125014000,00613994181862,2007-39,12
1125014000,00613994181862,2007-40,12
1125014000,00613994181862,2007-41,12
1125014000,00613994181862,2007-42,12
1125014000,00613994181862,2007-43,12
1125014000,00613994181862,2007-44,12
1125014000,00613994181862,2007-45,12
"
I would appreciate any input...Thanks much!!!
Valeriya