greymalkin
06-18-2008, 12:46 PM
Hello,
I am creating a sql query builder to assist in routine data moves I have to do at work. The application will build the necessary queries and paste them in a certain region on an excel spreadsheet. If I copy this region and paste it into notepad it looks fine.
I have created a macro that will copy that region to another sheet (paste special values), then copy that sheet to a new workbook, then save it as text. I've tried xlText, xlUnicodeText, and xlTextMSDOS and all of them are doing the same thing: when I open the text file there are double quotes around some of the queries. These quotes are not part of the original values in excel.
Does anyone know how I can fix this or if there is a particlar text format that will save the excel sheet to text EXACTLY as it appears in excel?
here's a sample of what it looks like after being saved as text, I've modified the table/data names to protect the innocent :) Notice the 2nd and 4th line are wrapped in double quotes. This is not present in the excel file and it does not happen when I copy straight out of excel and paste straight into notepad.
SELECT * FROM tblName WHERE (Project = N'ProjectName')
"SELECT * FROM tblRemarks WHERE (sKey LIKE N'%,Remark,%')"
UPDATE tblName SET Name = N'BobRoss' WHERE (Project = N'ProjectName')
"UPDATE tblRemarks SET sKey = REPLACE(sKey,',Remark1,', ',Remark2,') WHERE (sKey LIKE N'%,ProjectName,%')"
EDIT: UPDATE!
I did some more troubleshooting and discovered that it's the commas that are causing the problems. I've tried just about every xlText type allowed and It's still wrapping the lines with commas in double quotes...
I am creating a sql query builder to assist in routine data moves I have to do at work. The application will build the necessary queries and paste them in a certain region on an excel spreadsheet. If I copy this region and paste it into notepad it looks fine.
I have created a macro that will copy that region to another sheet (paste special values), then copy that sheet to a new workbook, then save it as text. I've tried xlText, xlUnicodeText, and xlTextMSDOS and all of them are doing the same thing: when I open the text file there are double quotes around some of the queries. These quotes are not part of the original values in excel.
Does anyone know how I can fix this or if there is a particlar text format that will save the excel sheet to text EXACTLY as it appears in excel?
here's a sample of what it looks like after being saved as text, I've modified the table/data names to protect the innocent :) Notice the 2nd and 4th line are wrapped in double quotes. This is not present in the excel file and it does not happen when I copy straight out of excel and paste straight into notepad.
SELECT * FROM tblName WHERE (Project = N'ProjectName')
"SELECT * FROM tblRemarks WHERE (sKey LIKE N'%,Remark,%')"
UPDATE tblName SET Name = N'BobRoss' WHERE (Project = N'ProjectName')
"UPDATE tblRemarks SET sKey = REPLACE(sKey,',Remark1,', ',Remark2,') WHERE (sKey LIKE N'%,ProjectName,%')"
EDIT: UPDATE!
I did some more troubleshooting and discovered that it's the commas that are causing the problems. I've tried just about every xlText type allowed and It's still wrapping the lines with commas in double quotes...