| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook.Open: Problems with field separation character
I am using these lines to open a semicolon separated file:
Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkBook.Open: Problems with field separation character
Ebbe
csv files are seen as a native format for Excel and so, I suspect, it is seeing the extension and presuming commas. You can try two routes to fix this... 1) Try using the OpenText method rather than the Open method 2) Try using a file with no extension or a *.txt extension and option 1 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Ebbe" wrote in message ... I am using these lines to open a semicolon separated file: Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkBook.Open: Problems with field separation character
In at least xl97 and xl2000, only option 2 will work. The settings in
OpenText are ignored if the file is named with a .csv extension (in my experience - different regional versions may have different behavior). -- Regards, Tom Ogilvy "Nick Hodge" wrote in message ... Ebbe csv files are seen as a native format for Excel and so, I suspect, it is seeing the extension and presuming commas. You can try two routes to fix this... 1) Try using the OpenText method rather than the Open method 2) Try using a file with no extension or a *.txt extension and option 1 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Ebbe" wrote in message ... I am using these lines to open a semicolon separated file: Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkBook.Open: Problems with field separation character
Thank You Nick.
Renaming the .csv file to .txt did the work. The fields game in as expected. I tried also Toms proposal, but Excel think it know better when the file extension is .csv :-( It is clearly very difficult to persuade Excel to treat .csv files in alternative ways. Ebbe "Nick Hodge" skrev i en meddelelse ... Ebbe csv files are seen as a native format for Excel and so, I suspect, it is seeing the extension and presuming commas. You can try two routes to fix this... 1) Try using the OpenText method rather than the Open method 2) Try using a file with no extension or a *.txt extension and option 1 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Ebbe" wrote in message ... I am using these lines to open a semicolon separated file: Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkBook.Open: Problems with field separation character
You must have misread my post (or perhaps I was not clear). I echoed Nick
that you must change to .txt - that .csv does not work. -- Regards, Tom Ogilvy "Ebbe" wrote in message ... Thank You Nick. Renaming the .csv file to .txt did the work. The fields game in as expected. I tried also Toms proposal, but Excel think it know better when the file extension is .csv :-( It is clearly very difficult to persuade Excel to treat .csv files in alternative ways. Ebbe "Nick Hodge" skrev i en meddelelse ... Ebbe csv files are seen as a native format for Excel and so, I suspect, it is seeing the extension and presuming commas. You can try two routes to fix this... 1) Try using the OpenText method rather than the Open method 2) Try using a file with no extension or a *.txt extension and option 1 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Ebbe" wrote in message ... I am using these lines to open a semicolon separated file: Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkBook.Open: Problems with field separation character
No, I knew what you were saying, it was with reference to option 1 which I
wasn't sure about ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Tom Ogilvy" wrote in message ... You must have misread my post (or perhaps I was not clear). I echoed Nick that you must change to .txt - that .csv does not work. -- Regards, Tom Ogilvy "Ebbe" wrote in message ... Thank You Nick. Renaming the .csv file to .txt did the work. The fields game in as expected. I tried also Toms proposal, but Excel think it know better when the file extension is .csv :-( It is clearly very difficult to persuade Excel to treat .csv files in alternative ways. Ebbe "Nick Hodge" skrev i en meddelelse ... Ebbe csv files are seen as a native format for Excel and so, I suspect, it is seeing the extension and presuming commas. You can try two routes to fix this... 1) Try using the OpenText method rather than the Open method 2) Try using a file with no extension or a *.txt extension and option 1 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Ebbe" wrote in message ... I am using these lines to open a semicolon separated file: Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkBook.Open: Problems with field separation character
Option 1 said use OpenText (with the file still named with a .csv extension)
Option 2 said use OpenText, but rename the file with a .Txt extension (or no extension) I said Using OpentText with a file with a .CSV extention does NOT work as the settings for OpenText are ignored. the OP responded that he had renamed the file with a .txt extension and that worked - which is what I echoed - cautioning that not doing that would not work. The OP then said he tried my method and it did not work. What method the OP tried that did not work is a mystery, but it was nothing I suggested. I was responding to the OP's comment. -- regards, Tom Ogilvy "Nick Hodge" wrote in message ... No, I knew what you were saying, it was with reference to option 1 which I wasn't sure about ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Tom Ogilvy" wrote in message ... You must have misread my post (or perhaps I was not clear). I echoed Nick that you must change to .txt - that .csv does not work. -- Regards, Tom Ogilvy "Ebbe" wrote in message ... Thank You Nick. Renaming the .csv file to .txt did the work. The fields game in as expected. I tried also Toms proposal, but Excel think it know better when the file extension is .csv :-( It is clearly very difficult to persuade Excel to treat .csv files in alternative ways. Ebbe "Nick Hodge" skrev i en meddelelse ... Ebbe csv files are seen as a native format for Excel and so, I suspect, it is seeing the extension and presuming commas. You can try two routes to fix this... 1) Try using the OpenText method rather than the Open method 2) Try using a file with no extension or a *.txt extension and option 1 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Ebbe" wrote in message ... I am using these lines to open a semicolon separated file: Dim ImportFileName As String ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv") Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";" The file is opened, but the Excel uses "," (comma) as separator. WHY! I am running Office 2000 Pro Ebbe |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Field Character Changes | Setting up and Configuration of Excel | |||
| Need help : Open a csv file with semicolon separation | Excel Programming | |||
| Workbook Open macro/& Pivot Table Calculated field query | Excel Programming | |||
| Workbook Open macro/& Pivot Table Calculated field query | Excel Programming | |||
| Workbook Open macro/& Pivot Table Calculated field query | Excel Programming | |||
Linear Mode
