Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to get rid off double quotes when Exporting String to .txt file

Status
Not open for further replies.

Valeriya

MIS
Jan 9, 2006
138
US
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...

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
 
Instead of
Code:
Write #iFileNum, sText
Try
Code:
Print #iFileNum, sText
 
Thanks a million! It's works perfect now!
Do you happen to know how(or is it possible)to attach a date stamp along with Network User ID to the file name?

Code:
Sub TextIOWrite(sText As String)
  Dim sFile As String
  Dim iFileNum As Integer

sFile = "[b]Newtwork Drive:\Network_Folder\m/d/yyyy h:mm UserID textio.txt[/b]"[COLOR=red]'*Like BUSBY1_13120074:28_textio.txt?*'[/color]
  
  iFileNum = FreeFile
  Open sFile For Output As iFileNum
  Write #iFileNum, sText
  Close #iFileNum
End Sub

Thanks a lot again,
Valeriya
 
A starting point:
sFile = sDirPath & "\" & Environ("USERNAME") & Format(Now, "_yyyymmdd_hhnn_") & "textio.txt"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top