looking for excel function description list

[TiTou]

Hello all !

I saw many threads about reading/writing excel document (I mean xls files), I also had a look to the example, but the point is that there seem to be no documentation of the functions in invoke nodes and property nodes...

Does anyone know it such documentation exists and where ?

It is quite long and boring to test dozens of function before finding what I am looking for just because there is no documentation ! Just as an example I found out that when you run the code below, you automatically get 65536 rows and 256 columns which is the max for excel... What I'd like to get is the real numbers of rows and columns of a document...





Thanks for any help 😉

Message Edité par TiTou le 06-09-2006 01:08 PM

[DavidJCrawford]

Titou

How about opening up the visual basic editor within excel? From there you can browse using the object brower or us the VBA reference to hunt down what you need. Labview 8.0 also has an object browser. I suppose I am more confortable using the excel object browser to find out stuff.

I haven't really played with ActiveX in Labview but this is how I would get the number of rows and number cols in worksheet in VBA. This will only work if there are no gaps in the data such as empty cells. The .End method stops when it sees an empty cell.

Public Function intNoOfRows() As Integer

    Dim intLastRow As Integer
    Range("A1").Select
    Selection.End(xlDown).Select
    intLastRow = Selection.Row
    intNoOfRows = intLastRow
    Range("A1").Select
End Function

Public Function intNoOfCols() As Integer

    Dim intLastCol As Integer
    Range("A1").Select
    Selection.End(xlRight).Select
    intLastCol = Selection.Col
    intNoOfCols = intLastCol
    Range("A1").Select
End Function

From there I would go about translating the VBA code into labview.

Does this make sense?

David

[TiTou]

Thanks for that David !

I am still working under LV 7.1.1... and looking forward to move to 8 😉

Also, I am not a real excel expert and I have never used VBA... that's why I didn't know about the "object browser", I'll try to find the functions I need 😉

Thanks also for the quick example to get the size, I did the same with LabVIEW, but you found the limit... when there is a empty cell in the column, it stops 😞
That makes a lot of sense, but I need something slitely different 😉 any idea on how not to be affected by an empty cell ??

[DavidJCrawford]

Some basics... When you start Excel you will probably need to active the VBA side of things. In Excel got Tools->Add-In and check Analysis Toolpak and Analysis Toolpak-VBA options. From here you can right click on the excel toolbar and select the vba editor toolbar.

So I am thinking are there any unique markers in your xls file that indicates the end of the dataset. I recorded a macro to search for a marker and I arrived at this

    Range("A1").Select
    Cells.Find(What:="#### END OF DATA ####", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    myRow = ActiveCell.Row
    myCol = ActiveCell.Column

When you record a macro in excel it translates your actions into VBA which is a great way of getting your baseline. In this case, this is me clicking cell A1, going to the edit menu, selecting Find then executing the search. I then stopped the macro and hopped over to the VBA editor to look at the objects, methods and properties used for the task.

The bits in red are my additions to the macro I just recorded to get the row and column of the unique marker.

Maybe there isn't any unique marker for the data bounds? Maybe you can add one?

David

[AnalogKid2DigitalMan]

There's always that mass of hard to navigate documentation available at MSDN. Probably harder than using VBA help

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlproRows1_HV05204726....

[Ben]

Hey Mug-bearer!

No one else mentioned it yet so I will.

If you do a custom install for the MS Office products you can select "Help for VBA".

That will let you use the "Help>>>...." from the LV diagram to figure out what the functions do.

Ben

[tst]

Not to mention the smaller scale of right clicking any invoke or property node and selecting Help for property name. If you have the Office VBA help installed LV should open it to the specific target or method.

[tst]

Sorry Ben, for some reason I didn't see your reply even though I saw the page several minutes after you posted.

[Ben]

tst wrote "Sorry Ben..."

No problem!

You have only prooven (once again) that you are the encyclopaedia and I am just an index!

Ben

[unclebump]

Hi Titou,

you want to use the usedrange property item.