Copy / match data between Excel distribution sheets, Vb.net

Question

I need to copy data using Vb.net, if possible, from one excel spreadworbook to another and put the data in the appropriate columns in an existing Excel spreadsheet. The column headings of the tables are the same, I have several templates in which to place the data, and the order of the columns in each template is different, so I need a way to find the column heading in the template, and then copy the data in this column.

Would there be a better way to achieve this with ADO?

For example, move data from this workbook1 with columns "Test1", "Test2" and data

Test1 Test2 1 2 12 23 123 234 

In book 2, which will have the same column names, but may be in a different order:

 Test0 Test1 Test1.1 Test2 

I need to do this automatically, since I have a lot of data for copying and 30-40 book templates for copying data, the columns of the templates are in different orders and cannot be moved.

Answer 1

There are various ways to interact with Excel using .NET. If you just want to do this with one version of Excel, then VSTO might be your easiest solution, otherwise use something else. I like to use EXCEL-DNA .

You can also use ADO to output data, but to put it in another, I would think that for this you will need one of the methods listed above (since you will need to reference the excel object). If you use Excel 2007 and above, you can also directly access XML files and manipulate them this way (minus xlb, of course).

You can also create a library from an executable file and copy it locally. See here.

As for the headers, just use the dictionary (Of String, Integer) or List (Of String) to find out what the index of the file (s) is.