Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV file problems
Hi all,
I have MS Excel 2002 SP3 installed. I need to create a CSV file with ~ as the delimiter. I customized my current (dutch) Regional settings List delimiter to ~. So far so good. Once I open my excel file and do a save as, select CSV file and click on Save, I have exactly what I need. Upon opening the file with Notepad, all records are separeted with ~. However, I would like to automate this. I record a macro of the above action which resulted in something like this: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False But If I open that file with Notepad, all fields are separeted by comma's :-( The questions is: WHY? and how can I prevent this from happening? I want my ~ as the delimiter. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV file problems
I don't know why in Denmark they would call a file with ~ seperating
characters when CSV means Comma Seprated Values. does ~ mean Comma in Demark? Any way, below is code that you can use to manually generatte the CSV file with ~ Sub WriteCSV() Const Delimiter = "~" Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fswrite = CreateObject("Scripting.FileSystemObject") WriteFileName = "text.csv" '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 Exit Sub End Sub "Ixtreme" wrote: Hi all, I have MS Excel 2002 SP3 installed. I need to create a CSV file with ~ as the delimiter. I customized my current (dutch) Regional settings List delimiter to ~. So far so good. Once I open my excel file and do a save as, select CSV file and click on Save, I have exactly what I need. Upon opening the file with Notepad, all records are separeted with ~. However, I would like to automate this. I record a macro of the above action which resulted in something like this: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False But If I open that file with Notepad, all fields are separeted by comma's :-( The questions is: WHY? and how can I prevent this from happening? I want my ~ as the delimiter. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV file problems
Hi Ixtreme,
The problem is that when you automate the save process it is using the language of VBA and not the regional settings. Simply add the extra argument to your command and it should use the regional settings: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False _ Local:=True This should do the trick (in The Netherlands or Denmark ;-) ). Hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Ixtreme" wrote: Hi all, I have MS Excel 2002 SP3 installed. I need to create a CSV file with ~ as the delimiter. I customized my current (dutch) Regional settings List delimiter to ~. So far so good. Once I open my excel file and do a save as, select CSV file and click on Save, I have exactly what I need. Upon opening the file with Notepad, all records are separeted with ~. However, I would like to automate this. I record a macro of the above action which resulted in something like this: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False But If I open that file with Notepad, all fields are separeted by comma's :-( The questions is: WHY? and how can I prevent this from happening? I want my ~ as the delimiter. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV file problems
PS
I missed a comma in my argument list: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False, _ Local:=True Sean. -- (please remember to click yes if replies you receive are helpful to you) "SeanC UK" wrote: Hi Ixtreme, The problem is that when you automate the save process it is using the language of VBA and not the regional settings. Simply add the extra argument to your command and it should use the regional settings: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False _ Local:=True This should do the trick (in The Netherlands or Denmark ;-) ). Hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Ixtreme" wrote: Hi all, I have MS Excel 2002 SP3 installed. I need to create a CSV file with ~ as the delimiter. I customized my current (dutch) Regional settings List delimiter to ~. So far so good. Once I open my excel file and do a save as, select CSV file and click on Save, I have exactly what I need. Upon opening the file with Notepad, all records are separeted with ~. However, I would like to automate this. I record a macro of the above action which resulted in something like this: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False But If I open that file with Notepad, all fields are separeted by comma's :-( The questions is: WHY? and how can I prevent this from happening? I want my ~ as the delimiter. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV file problems
Hi Sean.
I'm using Danish Excel 2003/2007. For years I have used the hard (and slow) way to get semicolon in a csv file, because Danish VBA gives comma. Today, when I had the same probleme with a MS-DOS csv-file, I found your answer and I'm very happy, THANKS. -- Best regards, Joergen Bondesen "SeanC UK" skrev i en meddelelse ... PS I missed a comma in my argument list: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False, _ Local:=True Sean. -- (please remember to click yes if replies you receive are helpful to you) "SeanC UK" wrote: Hi Ixtreme, The problem is that when you automate the save process it is using the language of VBA and not the regional settings. Simply add the extra argument to your command and it should use the regional settings: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False _ Local:=True This should do the trick (in The Netherlands or Denmark ;-) ). Hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Ixtreme" wrote: Hi all, I have MS Excel 2002 SP3 installed. I need to create a CSV file with ~ as the delimiter. I customized my current (dutch) Regional settings List delimiter to ~. So far so good. Once I open my excel file and do a save as, select CSV file and click on Save, I have exactly what I need. Upon opening the file with Notepad, all records are separeted with ~. However, I would like to automate this. I record a macro of the above action which resulted in something like this: ActiveWorkbook.SaveAs Filename:= _ "C:\FILES\TEST.csv", _ FileFormat:=xlCSV, CreateBackup:=False But If I open that file with Notepad, all fields are separeted by comma's :-( The questions is: WHY? and how can I prevent this from happening? I want my ~ as the delimiter. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL EXPORT PROBLEMS | Excel Discussion (Misc queries) | |||
EXCEL EXPORT PROBLEMS | New Users to Excel | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
Excel file export problems | Excel Discussion (Misc queries) | |||
Problems merging an excel file due to code or file problems? | Excel Programming |