Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting CSV numbers become text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting CSV numbers become text
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... -- HTH... Jim Thomlinson "GeorgeAtkins" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting CSV numbers become text
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting CSV numbers become text
Thanks, Jim. I should have thought of that!
George "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "GeorgeAtkins" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting CSV numbers become text
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 "excelthoughts" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting numbers from Sql Server to Excel becomes text | Charts and Charting in Excel | |||
Exporting Time Formatted Numbers inside Macros | Excel Worksheet Functions | |||
Mix of numbers/letters in same column - Error exporting | Excel Discussion (Misc queries) | |||
Exporting From Quickbooks-not formatting numbers | Excel Discussion (Misc queries) | |||
Exporting numbers into Excel | Excel Discussion (Misc queries) |