Extra Double Quote when Export String to Text File

[sheeeng]

Hello,

Thanks for all the help last time. I really appreciate it.
Now, my second problem.

Assume that all my coding & cells are filled with correct data.
I launch this function.

Sub GenerateSQL()
Dim n As Integer
Dim stat1, stat2 As String
Dim x, y, z As Integer
Dim sPath As String
x = 1
y = 1
z = 1
Sheets(2).Activate
For n = 1 To 2
   stat1 = ""
   stat1 = "select * from " & "my_db_table" & " where hex_id = " & Trim(Sheets(1).Cells(x + z, y + 3).Value)
   stat2 = ""
   stat2 = "select * from " & Sheets(1).Cells(x + z, y + 13).Value & " where time_id in _
   ( select datetime_id from db_datetimes where date_and_time = " & Chr(34) & Now() & Chr(34) & " )"
   Cells(1, 1).Value = stat1
   Cells(2, 1).Value = stat2 'the date/time based on your PC Regional & Language Setting
   MsgBox (stat2)
   sPath = ThisWorkbook.Path & Application.PathSeparator & Sheets(1).Cells(x + z, y + 3).Value
   ActiveWorkbook.SaveAs Filename:=sPath, FileFormat:=xlText, CreateBackup:=False
   z = z + 1
Next n
Sheets(2).Select
Sheets(2).Name = "Sheet2"
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & Application.PathSeparator & "Exp02.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Sheets(2).Cells(1, 1).Value = ""
Sheets(2).Cells(2, 1).Value = ""
Sheets(1).Select
End Sub

When MsgBox appear,
select * from Statistics Table where time_id in ( select datetime_id from db_datetimes where date_and_time = "5/25/2005 17:16:15" )
this is what I want! But when I export this data out, extra quotes appears in my output file *.txt
"select * from Statistics Table where time_id in ( select datetime_id from db_datetimes where date_and_time = ""5/25/2005 17:16:15"" )"

CAN ANYBOBY KNOW WHY? HELP!

[mark007]

This is because when excel saves as a text file is saves as a comma separted values file and adds the extra quotes to specify the values. You might be able to specify in the saveas command some way round this - not sure though and no time at mo to check. The other option is to save the file your self using the standard VB file i/o commands such as:

dim fNum as long
fNum=Freefile
Open [filepath] for output as fnum
Writeline #fNum, strSQL
Close #fNum

[sheeeng]

Writeline #fNum, strSQL
Function not found.
Your reason seems logical. But can explain in further details.
Thanks.

[sheeeng]

I use basic VB I/O to solve this.

fName = Sheets(1).Cells(x + z + 1, y + 2).Value & ".txt"
Set fs = CreateObject("Scripting.FileSystemObject")
If (DirExists("Scripts") = 0) Then
   MkDir (ThisWorkbook.Path & Application.PathSeparator & "Scripts")
   Else
    End If
sPath = ThisWorkbook.Path & Application.PathSeparator & "Scripts"
Set a = fs.CreateTextFile(sPath & Application.PathSeparator & fName, True)
    a.WriteLine (stat1)
    a.WriteLine (stat2)
    a.Close

Thanks for all the help.

[mark007]

What I used was intrinsic VB I/O. You yhave used the FileSystemObvject wrapper. This is slightly slower but probably unnoticable depending on what you are doing though. I did however make one mistake as you noticed:

Writeline #fNum, strSQL

should be:

Print #fNum, strSQL