Opening CSV in excel from VB

[lonfnico]

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

[K Dales]

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
>
>

[lonfnico]

Actually, you're totally right. For some reason, in europe, people often use ";" instead of "," in "comma"-SV, no idea why.

But since excel automatically transforms it into a "normal" csv if i doubleclick on it, I thought that, maybe, it would work well with OpenFileDialog ^^
Obviously it doesnt!

As for Format:=4, it doesnt work, it might be another format though, I will try.

On the other hand, as I said, I'm brand new to Excel programming. Is there a _very_ good website with all the objects, methods, constants, etc? that are used in VB for excel?

Thanks in advance

[lonfnico]

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

[Mikeyhend]

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

[AnExpertNovice]

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" <lonfnico.267fvz_1144935005.8818@excelforum-nospam.com> wrote in
message news:lonfnico.267fvz_1144935005.8818@excelforum-nospam.com...
>
> 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
>

[lonfnico]

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.

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...

[Randy Harmelink]

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