exporting CSV numbers become text

[GeorgeAtkins]

I have an xls 2003 file with dates, text and currency values. I need to run a
macro to export to a CSV format. When run, the currency values convert to
text. I've tried various "pastespecial" options. But when I get the currency
to show up as numbers, the dates show up unformatted as integers, too.

Here is a sample of the Excel data:
1/3/2005 5.03.h.1 District 287 $65,658 Carl Perkins Grant
1/3/2005 5.03.h.2 District 287 $7,525 Tech Prep Grant

Here is what it looks like after a typical "pastespecial":
1/3/2005,5.03.h.1, 287,"$65,658",Carl Perkins Grant
1/3/2005,5.03.h.2, 287,"7,525",Tech Prep Grant

Here is my code:

Sub ExportToCSV()
' Save selected range as a CSV text file
Dim ThisBook As Workbook

ChDrive "S"
ChDir "S:\TECH\GEORGE\"

Set ThisBook = ActiveWorkbook
Selection.Copy

Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues ' one of several options used
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="TestForRichard.txt", FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
ThisBook.Activate

End Sub

By the way, the exact range can change, meaning that the columns are NOT
always in the positions shown!

Thanks for any ideas or a thwack on the head for overlooking something
obvious.

George

 

[Jim Thomlinson]

Your problem is with the Coma in the currency. In order to represent the coma
it has to put the value in quotes. You will need to have your sub do some
best guess to determine what needs to be converted to an unformatted number
and what can be left alone. You can use the isdate function to determine what
columns are dates and convert the rest of the sheet to unformatted numbers. I
am afraid I have to go now so I can't be more help than that...

 

[excelthoughts]

Hi

Try this amended routine:

Regards,
Andrew
excelthoughts.com

Sub ExportToCSV()
' Save selected range as a CSV text file
Dim ThisBook As Workbook
Dim thisSheet As Worksheet
Dim thisSelection As Range
Dim newBook As Workbook
Dim NewSheet As Worksheet
Dim cell As Range

ChDrive "S"
ChDir "S:\TECH\GEORGE\"

Set ThisBook = Selection.Parent.Parent
Set thisSheet = ThisBook.ActiveSheet
Set thisSelection = Selection

thisSelection.Copy

Set newBook = Workbooks.Add
Set NewSheet = newBook.ActiveSheet

ActiveCell.PasteSpecial Paste:=xlPasteValues ' one of several options
used

For Each cell In Range(thisSheet.Cells(1),
thisSheet.Cells(thisSelection.Columns.Count))

If IsDate(cell.Value) Then
cell.EntireColumn.Copy
NewSheet.Cells(1, cell.Column).PasteSpecial
Paste:=xlPasteFormats
End If
Next

Application.DisplayAlerts = False
newBook.SaveAs Filename:="TestForRichard.txt", FileFormat:=xlCSV
newBook.Close
Application.DisplayAlerts = True
ThisBook.Activate


End Sub

 

[GeorgeAtkins]

Thanks, Jim. I should have thought of that!
George

 

[GeorgeAtkins]

Thanks, Andrew. Good work. I must have got distracted by preoccupation of the
PasteSpecial command and didn't think through logically. You and Jim have put
me back on track.
George