c# - How to import external data (.csv file) as a new worksheet without opening another workBook/excel application

Question

Hi

My target is to open many .csv files, each of them in a new workSheet in the same excel workBook, without opening a workBook for each.

I noticed that the Open() or OpenText() methods belong to the workBook class and using them means opening a new workBook.

To be more clear, a regular user would simply do this in Excel 2003 by selecting: Data > Import External Data > Import Data... > (then selecting a .csv file and go along with the wizard). This scenario will grant the user an ability to open the .csv file to a new workSheet. Also, it is possible to choose the Delimiters, Text Qualifier and Data Format, in a similar manner as using the workBook.OpenText() method.

I managed to do it by opening each .csv file in a seperate workBook and then copying it to a new workSheet in the target excel workBook. It seems kind of a clumsy way to do and i think it requires more time (im dealing with many .csv files here).

I tried searching everywhere. could it be that this thing is not possible via "Microsoft Excel 11.0 Object Library" ??

Thanks

Answer 1

You have to use workbook to open them one by one, but you can improve it:

I managed to do it by opening each .csv file in a seperate workBook and then copying it to a new workSheet in the target excel workBook.

Just use one workbook object to open each CSV file and copy the opened worksheet over to another workbook object (as your basebook) to store them in separate workssheets.