Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook SaveAs question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook SaveAs question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook SaveAs question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook SaveAs question
I modified the code to open a remote workbook and save as CSV. the code
willopen a pop up to select the XLS file 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 filetoopen = Application _ .GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=filetoopen Set newbk = ActiveWorkbook Set fswrite = CreateObject("Scripting.FileSystemObject") 'open files WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) With newbk.ActiveSheet 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 With newbk.Close End Sub "Dave Farquharson" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook SaveAs question
Hi there Joel,
Your macro works as advertised. The thing is, my code isn't running in a macro, my application is a c++ program using a COM interface to talk to Excel (using the _WorkbookPtr interface). Perhaps there's a way to give a macro to a workbook and run it, or run one some other way; or maybe I'm in the wrong news group, or, quite likely, I'm just being dense ;-) Anyway, thanks for the macros. They do what I want in principal at least. Dave "Joel" wrote in message ... I modified the code to open a remote workbook and save as CSV. the code willopen a pop up to select the XLS file 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 filetoopen = Application _ .GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=filetoopen Set newbk = ActiveWorkbook Set fswrite = CreateObject("Scripting.FileSystemObject") 'open files WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) With newbk.ActiveSheet 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 With newbk.Close End Sub "Dave Farquharson" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook SaveAs question
I'm not an expert on COM interfaces. But if you make the macro a worksheet
open macro then open the workbook from C++ it may work. "Dave Farquharson" wrote: Hi there Joel, Your macro works as advertised. The thing is, my code isn't running in a macro, my application is a c++ program using a COM interface to talk to Excel (using the _WorkbookPtr interface). Perhaps there's a way to give a macro to a workbook and run it, or run one some other way; or maybe I'm in the wrong news group, or, quite likely, I'm just being dense ;-) Anyway, thanks for the macros. They do what I want in principal at least. Dave "Joel" wrote in message ... I modified the code to open a remote workbook and save as CSV. the code willopen a pop up to select the XLS file 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 filetoopen = Application _ .GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=filetoopen Set newbk = ActiveWorkbook Set fswrite = CreateObject("Scripting.FileSystemObject") 'open files WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) With newbk.ActiveSheet 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 With newbk.Close End Sub "Dave Farquharson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 question involving SaveAs to .XLS | Excel Discussion (Misc queries) | |||
Calling workbook.saveas depending on excel version | Excel Programming | |||
Excel workbook.saveas freezes program | Excel Programming | |||
Using custom template with Excel.Workbook.SaveAs | Excel Programming | |||
Error Excel: 1004 SaveAs method of Workbook class failed | Excel Programming |