Macro - loading multiple CSV into calc

[APU]

Is it possible to load (using macro) multiple csv files into calc document, each file into different sheet ?

Thanks in advance.

[i_jens]

Check here: http://www.oooforum.org/forum/viewtopic.phtml?t=15678&highlight=csv - there is a multitude of threads in this forum with this topic.

Anyhow - I modified it to my needs: It will insert the csv given by fileName into a new table. I have "|" as delimiter - but simply replace the ascii code accordingly ("|" = 124)

[auser99]

Thanks for the reply.

au

http://www.xprad.org/

[jody]

@i_jens:
I would like to use your code - could you explain the content of the string sOpc?
From your comment i assume that the first number is the ascii code of the separator
character. But what about the others - the empty one, the " 0", the "1" and the "1/2/2/1/3/2"?

Thank You
Jody

[jan.novak]

Here is description of Filter Options for the CSV Filter.

http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options

[jody]

Thank you for the link!

I have another question though:
From the documentation and the example given in this link, it looks like
i have to know in advance how many fields are to be expected, and
what types they have. Is this correct?

But when i look at OpenOfficeCalc loading a csv file, it only asks about the
character set, the start row, the separator character, and the text delimiter
(and even makes good guesses) and does not need to be told about the
number of colons and their formats. So i should think there should be
the possibility to load a csv file the same way by a "VB" function,
i.e. without knowledge of number and formats of columns.
Or am i completely wrong here?

Thank You
jody

[i_jens]

If you do not specify it explictly the macro will do a 'good guess' - so there are no problems if you omit that part (at least that's how it worked for me - no expert here).

sOpc = "ASCII CODE Field Separator, ASCII CODE Text Delimiter, Character Set, Starting Line" is all you need to specify

[APU]

I tried a code similar to that of i_jens (thanks again), but loading CSV into existing sheets rather than new ones. It worked fine, except for one problem - loading data destroyed the original cell formatting of the sheets (background color etc), which I wanted to keep.

So I went other way: opening CSV in a hidden temporary document, and then copying the contents to the relevant sheet of the original document ( the code can be seen here: http://www.oooforum.org/forum/viewtopic.phtml?p=368767&highlight=#368767 ). It keeps cell formatting of the original sheet. However, I have a problem with multiline cells, which are displayed as single lines ( also described in more detail in that thread ).

I wonder if there is some other way to keep the original cell formatting from being destroyed ?