get sheets name of excel file

[Student_LV]

Hello ! 

After opening excel files in LV using Active X, I want to read data from each sheet existing in the file,That's why I put a listbox containing the sheets name that the user will select from it the sheet he wants to read.

The problem is that the sheets name are variable E.G : Sheet1 : AlternCurrent, it's written in different manners from file to other ( like: altern current or ALTERNCURRENT or Alternatif Current...), ( PS: I tried to get a list of all the name possibilities from the files)

I want to make labview recognize the sheet selected, independetly of how the sheet name is written, so to be generic to all files.

Does anyone has an idea how to solve this please 

You will find attached the VI of Read excel file 

Thank you in advance  

[PalanivelThiruvenkadam]

Get Sheet Names of Selected workbook, then user can select the required sheet and read the values of specific sheet.

[GerdW]

Hi Student,

you already read each sheet name so you "know" each name. What exactly is your problem?

I want to make labview recognize the sheet selected, independetly of how the sheet name is written, so to be generic to all files.

Then you need to implement an algorithm which is looking for "similarity" of strings!

[Student_LV]

Hello ! 

Thank you for this suggestion , as the sheet names change from file to other, i think it's better, as you said, to get all the sheet Names in the workbook and, then user can select the required sheet.

But any idea how to do this please with the VI that I attached above.

thank you 

[GerdW]

Hi Student,

But any idea how to do this please with the VI that I attached above.

Read all sheet names, put them in a listbox.

When the user selects an item of the listbox you load the corresponding sheet…

[Bob_Schor]

I'm assuming you have access to the Report Generation Toolkit (RGT), which has some Excel-specific functions that you need.  Here are the steps you need to take -- I'm deliberately doing this without giving you the VI, as it will be more instructive (and you will be more likely to understand and remember how to do this) if you "do it yourself".

1. All Reports start with "New Report".  Look at the Inputs -- you'll need to specify an Excel Report, and wire a path to the Excel Workbook whose Worksheet names you want into the "Template" input.
2. Use the Excel Workbook Properties (from the General Excel sub-Palette) to get the number of Worksheets.
3. Using a For Loop with N = number of worksheets,
   1. Wire the Loop Index ("I") to the Excel Get Worksheet function.
   2. Put a second Excel Workbook Properties next, and pass its "current worksheet name" output out of the For Loop using an Indexing Tunnel (the normal For Loop output tunnel).

At this point, when you exit the For Loop, you'll have a list of all of the WorkSheet names in an Array.  You can make any of those Worksheets be the Current Worksheet by executing another Excel Get Worksheet and either wiring the WorkSheet name in (a "bottom" input) or using the Array Index = Worksheet Index input.

Try building this simple VI and then "pointing it" (wiring the Template Name input) to a Workbook whose Worksheet names you know.  Don't forget to do a Dispose Report to close Excel when you are done.

Bob Schor

[ben64]

I posted code to do this in this thread.

Ben64

[Student_LV]

Thank you for your responses 

I look into the code that you suggested, it was helpful but still get error when I run my code, that i didn't know how to correct it  

Please see the attached file 

[ben64]

You're trying to select a sheet called "Select a sheet..." Do something like this:

Also at the end make sure to close the worksheet before calling the application quit method.

Ben64

[Student_LV]

Hello

I did like you suggested to me,but I always find an undefined error code -2146777998.

I made modifications but still doesn't work;

Does anyone have any idea how to make the attached VI work ?

Please see attached VI 

Thank you in advance !