Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
Hi again. I'm trying to open a csv file with a simple macro (which is just gonna help someone, like a step by step), but my problem is that when I use - Dim x As Variant x = Application.GetOpenFilename("Fichiers CSV (*.csv), *.csv") Workbooks.Open Filename:=x- (which is msdn code), I will open the CSV as a text file, showing in A1 all my cells separated by semicolumns, instead of opening it as a really comma-separated values in different cells! Any idea how to solve that? Thanks -- lonfnico ------------------------------------------------------------------------ lonfnico's Profile: http://www.excelforum.com/member.php...o&userid=33396 View this thread: http://www.excelforum.com/showthread...hreadid=532572 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
Is the input file truly comma separated? From what you are getting it
appears that perhaps the semicolons are used instead of commas. For the Workbooks.Open method you can specify what the separator character is using the Format argument; if my suspicion is correct then this might resolve the problem: Workbooks.Open FileName:=x, Format:=4 -- - K Dales "lonfnico" wrote: Hi again. I'm trying to open a csv file with a simple macro (which is just gonna help someone, like a step by step), but my problem is that when I use - Dim x As Variant x = Application.GetOpenFilename("Fichiers CSV (*.csv), *.csv") Workbooks.Open Filename:=x- (which is msdn code), I will open the CSV as a text file, showing in A1 all my cells separated by semicolumns, instead of opening it as a really comma-separated values in different cells! Any idea how to solve that? Thanks -- lonfnico ------------------------------------------------------------------------ lonfnico's Profile: http://www.excelforum.com/member.php...o&userid=33396 View this thread: http://www.excelforum.com/showthread...hreadid=532572 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
Actually, you're totally right. For some reason, in europe, people ofte use ";" instead of "," in "comma"-SV, no idea why. But since excel automatically transforms it into a "normal" csv if doubleclick on it, I thought that, maybe, it would work well wit OpenFileDialog ^^ Obviously it doesnt! As for Format:=4, it doesnt work, it might be another format though, will try. On the other hand, as I said, I'm brand new to Excel programming. I there a _very_ good website with all the objects, methods, constants etc? that are used in VB for excel? Thanks in advanc -- lonfnic ----------------------------------------------------------------------- lonfnico's Profile: http://www.excelforum.com/member.php...fo&userid=3339 View this thread: http://www.excelforum.com/showthread.php?threadid=53257 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
Format:=4 didnt work, and Format:=6, Delimiter:=";" doesnt work either. I have no idea how this damn csv is made, and close to no way to find out, as its made by some external source. If I open it with notepad, it doesnt even show the "return" chars, its just some ascii code (as it seems) that is not taken in charge by notedpad... But the ; are still there! I thought this would take 5 minutes, but finally, im going to use the day on it -- lonfnico ------------------------------------------------------------------------ lonfnico's Profile: http://www.excelforum.com/member.php...o&userid=33396 View this thread: http://www.excelforum.com/showthread...hreadid=532572 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
It is not a CSV file. That is your first mistake. A CSV file is comma
separated and you have a semicolon separated. (Hint Tab Separated is better, imo.) The comma separated files will load seven fields instead of six fields because a field contains a comma. To fix this surround each text field with quotes. That doesn't fix everything but it will help. Full Name,Doe, John,First Name, John, Last name, Doe To figure out the proper syntax for loading your specific file follow these instructions. Record Macro Open the file to be imported. (File | Open) Change the file type to "Text Files (*.prn; *.txt; *.csv)" Select the file to be loaded. Click Open Enable Delimited Click Next Select Semicolon as the delimiter and set up any other options as required Continue through the dialog Once the file is imported Stop the Macro Now look at the code that was generated and modify it as necessary. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "lonfnico" wrote in message ... Format:=4 didnt work, and Format:=6, Delimiter:=";" doesnt work either. I have no idea how this damn csv is made, and close to no way to find out, as its made by some external source. If I open it with notepad, it doesnt even show the "return" chars, its just some ascii code (as it seems) that is not taken in charge by notedpad... But the ; are still there! I thought this would take 5 minutes, but finally, im going to use the day on it -- lonfnico ------------------------------------------------------------------------ lonfnico's Profile: http://www.excelforum.com/member.php...o&userid=33396 View this thread: http://www.excelforum.com/showthread...hreadid=532572 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
AnExpertNovice Wrote: It is not a CSV file. That is your first mistake. A CSV file is comma separated and you have a semicolon separated. (Hint Tab Separated is better, imo.) The comma separated files will load seven fields instead of six fields because a field contains a comma. To fix this surround each text field with quotes. That doesn't fix everything but it will help. Full Name,Doe, John,First Name, John, Last name, Doe As I previously said, I know its not a real CSV. And I can't modify the source file, as it is not mine - its generated every day by an external source, I just want to analyse it. But Excel will treat it as a real CSV, and if I put a comma, without quotes, in the text (like Name;Adress,postal code;country), it will make 3 fields and not 4 (I just tested to be sure not to say something wrong). If I just doubleclick on this *.csv file (which is NOT comma separated, but which still has the csv extension), Excel will open it properly. AnExpertNovice Wrote: To figure out the proper syntax for loading your specific file follow these instructions. Record Macro Open the file to be imported. (File | Open) Change the file type to "Text Files (*.prn; *.txt; *.csv)" Select the file to be loaded. Click Open Enable Delimited Click Next Select Semicolon as the delimiter and set up any other options as required Continue through the dialog Once the file is imported Stop the Macro Now look at the code that was generated and modify it as necessary. I just tried that, sounded like a good idea! But my version of Excel (2002 sp3) doesn't gives me the opportunity to "Enable Delimited" - it just opens properly the file, and thats it... and the code generated looks like this : Sub MyMacro() ChDir "D:\Docs\" Workbooks.Open Filename:= _ "D:\Docs\RAPPORT.csv" End Sub As I said, looks like Excel internally treats it, no idea how to change that... -- lonfnico ------------------------------------------------------------------------ lonfnico's Profile: http://www.excelforum.com/member.php...o&userid=33396 View this thread: http://www.excelforum.com/showthread...hreadid=532572 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
Hi, You could try this: Place this in a module in the *.xls that must open the *.csv I used a button to trigger the macro and separated name and family name. If you need more than 2 columns, change array(2,1) to array(?,1) for the number of columns you need in macro "Text_to_columns". Sub openCSV() Workbooks.Open "*:\*\*.csv" Run "Text_to_columns" End Sub Sub Text_to_columns() Range("A1").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Range("A1").Select End Sub -- Mikeyhend ------------------------------------------------------------------------ Mikeyhend's Profile: http://www.excelforum.com/member.php...o&userid=33400 View this thread: http://www.excelforum.com/showthread...hreadid=532572 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening CSV in excel from VB
How about importing the data into a new worksheet? Then you'll be in
control of how EXCEL interprets the file, even if the file does have the CSV extension. For example, something like: Workbooks.Add With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Documents and Settings\Administrator\My Documents\My Spreadsheets\Temp\Test.csv" _ , Destination:=Range("A1")) .AdjustColumnWidth = True .TextFileParseType = xlDelimited .TextFileSemicolonDelimiter = True .Refresh BackgroundQuery:=False End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop blank workbook from opening when opening an existing | Excel Discussion (Misc queries) | |||
Opening files in Excel 2003 saved in Excel 2000 without data loss... | Excel Discussion (Misc queries) | |||
Trouble opening large size Excel file 155Mb in Excel 2007 | Excel Discussion (Misc queries) | |||
REF errors when opening excel in xp. works fine when opening wor. | Excel Discussion (Misc queries) | |||
excel VBA problem - setting workbook as variable & opening/re-opening | Excel Programming |