Combine multiple excel files into one excel file

[fedejh]

Hello there, my first post in this forum. I was looking for a solution to this problem but I found quite few posts without any solution for me.

I have 6 different excel files (1 sheet each) based on 6 different templates. What i would like to do is to get only one file with 6 sheets. I'm using RGT to generate every single file and I nevers used ActiveX.

Do you know how to do this? Do you have any tip for a different approach to the problem?

Thanks for your help

[Cm7]

Hi

If you use Microsoft Excel, you can do by VBA.

Before Trying to run, you make source Excel files, and save to C:\temp.

On the sheet "Ctrl", you write path , file name, & sheet Name Cell of file to read.

And also write destination to paste.

After this, click the shape named "Read!".

You will find some data on worksheet.

[fedejh]

---

@Cm7 wrote:

Hi

If you use Microsoft Excel, you can do by VBA.

Before Trying to run, you make source Excel files, and save to C:\temp.

On the sheet "Ctrl", you write path , file name, & sheet Name Cell of file to read.

And also write destination to paste.

After this, click the shape named "Read!".

You will find some data on worksheet.

---

Thanks for your script Cm7 but I'm looking for something that has to be done programmatically via Labview, without using Excel macros.

The point is that I have to sequentially run 6 tests and i want to write the data inside a single file (but i need 6 different templates, one for every test). I thought that the easiest way for doing it was to create 6 different files and then merge them all in one at the end. I'm wondering if it's possible to create only one workbook and then add everytime a sheet based on a different template, but I don't know honestly how to solve this

[Munna232]

Yes you can do it with one workbook. No need to create 6 different sheets & merge.

By default workbook have 3sheets.So, add 3 more sheets. After that read 1^st sheet & write your data (as you want) after that next file.

Check this below snippet to get some idea.

[Bob_Schor]

I agree with Munna's post.  With a little more work, you can even name the Worksheets (I'll have to look up how I did that ...).

Bob Schor

[Munna232]

With "Excel Rename worksheet.vi", we can edit/change the name.

http://zone.ni.com/reference/en-XX/help/372120A-01/lvoffice/excel_rename_worksheet/

[fedejh]

Thanks everyone for your help, but the problem is not easy as it looks ( I poorly described it, so sorry about that). I've to create a file with UP TO 6 different templates based sheets, sometimes I have only 1/2/3/4/5 sheets. 

[Munna232]

Hello fedejh,

Everything is not so easy. Sometimes you need to work more.

Anyway,

---

fedejh wrote:

sometimes I have only 1/2/3/4/5 sheets. 

---

Who will decide this number? If you know how many sheets you want write, pass that number to “N” value (For loop count).

If user wants to select required sheets at runtime, Use list options & display the sheets. Ask user to select.

After selection, pass that array to For loop & do what you want.

[Bob_Schor]

Unfortunately, NI's talking about a "Template" in describing opening an Excel Workbook is a little misleading.  An Excel Workbook is not like a Text document, where you start with an empty file, and then write all the stuff you want and close the file when it is done.  With Excel, you essentially open the Final Document, "fill in some blanks" (you can think of it as filling in the empty rows and columns), then close the file when it is done.  [This analogy isn't exact, but more of an illustration].

If you have a pre-defined Workbook, with all the Spreadsheets that you want to have when the Workbook is closed already defined (i.e. if you know in advance that you need 5, or 6, or 20 Worksheete), you can "pre-make" the Workbook with the Sheets already in place -- this is called a "Template" because you really do "fill in the blanks", going to the already-existing Spreadsheets, which may (or may now) have pre-determined Headers and formatting already in place, and just place the data where it needs to go.

On  the other hand, you can open an Excel Report without a Template, which gives you a blank, 3-sheet Workbook that you fill in and "name when closing".  If you go this way, without a Template, you can construct the Workbook precisely as you wish, with as many Worksheet as you want, with headings whereever you put them (rather than being "already in place" in the Template.

The key to knowing how to construct your Excel Workbook is to know exactly what you want to do.  There's a well-known "Rule for Developing Good Software" called "Write the Documentation First".  I must confess I never did this until about a year ago, but boy, does it make a difference!  In your case, spelling out (by writing it out1) precisely what you want to do, what Worksheets you need, what information each should have, etc. will help you decide if a Template (best for a fixed format Report) or creating the Workbook "on the fly" depending on the choices presented at run time is the better model.  Once you know this, you can fold this logic into creating the decisions as to the Worksheets you need and what you want to put on each of them.  [I'd strongly suggest making a VI that is responsible for creating each individual sheet, so your top level code doesn't need to be cluttered with all of the details).

Bob Schor 

[Bob_Schor]

Unfortunately, NI's talking about a "Template" in describing opening an Excel Workbook is a little misleading.  An Excel Workbook is not like a Text document, where you start with an empty file, and then write all the stuff you want and close the file when it is done.  With Excel, you essentially open the Final Document, "fill in some blanks" (you can think of it as filling in the empty rows and columns), then close the file when it is done.  [This analogy isn't exact, but more of an illustration].

If you have a pre-defined Workbook, with all the Spreadsheets that you want to have when the Workbook is closed already defined (i.e. if you know in advance that you need 5, or 6, or 20 Worksheete), you can "pre-make" the Workbook with the Sheets already in place -- this is called a "Template" because you really do "fill in the blanks", going to the already-existing Spreadsheets, which may (or may now) have pre-determined Headers and formatting already in place, and just place the data where it needs to go.

On  the other hand, you can open an Excel Report without a Template, which gives you a blank, 3-sheet Workbook that you fill in and "name when closing".  If you go this way, without a Template, you can construct the Workbook precisely as you wish, with as many Worksheet as you want, with headings whereever you put them (rather than being "already in place" in the Template.

The key to knowing how to construct your Excel Workbook is to know exactly what you want to do.  There's a well-known "Rule for Developing Good Software" called "Write the Documentation First".  I must confess I never did this until about a year ago, but boy, does it make a difference!  In your case, spelling out (by writing it out1) precisely what you want to do, what Worksheets you need, what information each should have, etc. will help you decide if a Template (best for a fixed format Report) or creating the Workbook "on the fly" depending on the choices presented at run time is the better model.  Once you know this, you can fold this logic into creating the decisions as to the Worksheets you need and what you want to put on each of them.  [I'd strongly suggest making a VI that is responsible for creating each individual sheet, so your top level code doesn't need to be cluttered with all of the details).

Bob Schor