PDA

View Full Version : [SOLVED] Extra Double Quote when Export String to Text File



sheeeng
05-25-2005, 02:08 AM
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" ) :thumb (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=0#)
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"" )":banghead: (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=0#)

CAN ANYBOBY KNOW WHY? HELP! :friends: (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=0#)

mark007
05-28-2005, 03:00 AM
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
06-05-2005, 09:25 PM
Writeline #fNum, strSQL
Function not found.
Your reason seems logical. But can explain in further details.
Thanks.

sheeeng
06-05-2005, 10:48 PM
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
06-06-2005, 02:00 AM
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

:)