Hi Joel,

Thanks for the response. I'm going to try this macro inside a test Excel
spreadsheet just to see, but I suspect this sort of solution may not help me
with my particular problem. I don't have control over the XLS files this
macro would run on and running a new macro on an arbitrary XLS file is a
tech problem I have not yet investigated how to do. Thanks again though! I
laughed out loud reading "... because of ALLLLL the unusual things excel
does..."

Incidentally, I tried xlCSVMSDOS as a file type just to give it a shot, with
the same comma-stained results.

Dave

"Joel" wrote in message
...
I have given people the macro before because of ALLLLLL the unusual thing
excel does when writing CVS. It is just better to do it yourself.

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")


'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

End Sub



"Dave Farquharson" wrote:

I am using _Workbook::SaveAs to make a csv copy of an XLS file (using the
file format Excel::xlCSVWindows). The problem I've run into is, numeric
cells in the Excel file that are formatted to use thousands separators
copy
that formatting to the CSV file (one of the few bits of formatting that
DO
get copied).

So a number without thousands separators is 1000 in both the XLS and CSV
files.

A number with thousands separators is 1,000 in the XLS file, and "1,000"
in
the CSV file.

This isn't so tragic, except for a few things: if you have a text field
with
1,000 in it, it looks exactly the same in the CSV file as a numeric field
with thousands separators ("1,000"), making it impossible to distinguish
between them. Because of this, modifying our CSV parser to handle
something
like "1,000" is somewhat less than ideal.

So my question is, is there a way to tell Excel NOT to format over the
thousands separator on SaveAs? I suspect there is not, but thought I'd
ask
anyway.

Thanks,

Dave