Using an Excel macro in LabVIEW

[Devour]

Thanks Even, I put it in my folder user.lib but I don't have the pallette in my block diagram. So I open the file .llb to see all the attached vi.

But I still wonder if we can write a macro in a Excel file "A", or if we can execute my macro from another Excel file "B" on this Excel file "A", because my macro make:

"You take in this worksheet, these columns, you make the graph in another sheet".

So I hope the second solution doesn't execute the macro in the file B where it is stocked, but I think it's that.

I'm still working on.

[EvenDeejay]

Have you restarted LabVIEW? Can you see it if you go to "Change Visible Palettes"?

By default your macros will not be executed in every excel file you got. See this.

In other words it would be safe to create a template and copy it, to also copy the macro within that file, fi you want to use it in many files. 

[Devour]

I have already restart LabVIEW and change the visible pallette, but it is not here. I have exactly like you but without the pallette.

Otherwise, for the macro in personal.xls file, I have already know that, but my problem is that my macro plot some data on the current file (the personal.xls) and I would like to plot some data on a file which change of name at anytime we make a Test (test which gives us the data).

Thanks Even

[EvenDeejay]

Hi!

I found this in the readme file:

how to add excel toolkit in function palette? :
 use Edit|Edit Control & Function Palettes, function palette, insert submenu and link to
 library .llb, browse to excel.llb ....
 -> remember to rename 'readonly.txt' file in active palette to enable palette editing 😉

It should work now if you do that.

I have created many reports with test results. I use the excel toolkit to write data into it, either arrays or single data. I can also paste stuff from the clipboard into the Excel file. In some reports I have created a template with dynamic graphs that will expand whenever data is written to the excel file. I don't use macros for that, but somthing called "Names". Link.

[EvenDeejay]

Actually.. In LabVIEW 2010 is a bit different than the readme says:

Here is the edit palette menu:

Then this comes up: (Right-click to get the small menu)

Then select this and browse for the excel.llb in your user.lib\_excel folder.

[Devour]

Oh yes, thank you so much Even, I was reading the readme, but I didn't find how to edit, and because I'm French, it is a bit different too. It works actually.

Then for the data made by one test, they go inside of an .xls file with the name of the test. And normally I want to make the graph at the end of the test. But the graph is a bit special, and should represent 3 series with 2 axis (+ time axis), and we can select on point easily.

All the tests are not the same, and the time also. I made the programm with LabVIEW before, but we must present it in the .xls file corresponding to the specially test file.

That's why I asked some help to create a macro which is easier in my opinion (with ActiveX, I didn't find a logic programm).

[EvenDeejay]

Hi again!

I'm glad you got the toolkit installed.

Below is an example on how to copy a template, create a new file, copy a graph to the clipboard and paste it into the new excel report.

 

This example is attached below. Unzip and run the vi. It will prompt you for the template. Push the create data and then create report. I haven't created a indicator for it so just wait a few seconds to be sure it's saved.

The report should look something like the excel file I attatched.

I'll see what I can do regarding the macro. 

[Devour]

Yes I saw your exemple in the Excel Board ! It is a nice report, but my entreprise want a graph in Excel with the data seperated. One sheet for data, another for the graph.

And if we want to see the variation on the graph, we put the mouse on the curve, and it displays the values of this point; so to make it like a picture, it is nice but not easy for us to manipulate after.

I'm glad too than a DJ can help me (I like so much DJs and I will become one 🙂

Here is the macro:

Sub Incrémental()
Dim Sh As Worksheet
Dim Ch As Chart
Dim i As Integer
 
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Graph").Delete
Application.DisplayAlerts = True
On Error GoTo 0
 
Set Sh = ThisWorkbook.Worksheets(1)
Set Ch = ThisWorkbook.Charts.Add(After:=ThisWorkbook.Worksheets(1))
With Ch
    For i = .SeriesCollection.Count To 1 Step -1
        .SeriesCollection(i).Delete
    Next i
    .Name = "Graph"
    .ChartType = xlXYScatterSmoothNoMarkers
    .SeriesCollection.NewSeries
    With .SeriesCollection(1)
        .Name = "T° Sample (°C)"
        .XValues = Sh.Range("A:A")
        .Values = Sh.Range("B:B")
    End With
    .SeriesCollection.NewSeries
    With .SeriesCollection(2)
        .Name = "T° Contact (°C)"
        .XValues = Sh.Range("A:A")
        .Values = Sh.Range("C:C")
    End With
    .SeriesCollection.NewSeries
    With .SeriesCollection(3)
        .Name = "Torque (N.m)"
        .XValues = Sh.Range("A:A")
        .Values = Sh.Range("I:I")
    End With
    With .SeriesCollection(1)
    .AxisGroup = 2
    End With
    .HasTitle = True
    .ChartTitle.Characters.Text = Sh.Name
    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Time (s)"
    End With
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Torque (N.m)"
    End With
    With .Axes(xlValue, xlSecondary)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Temperature (°C)"
    End With
End With
Set Sh = Nothing
Set Ch = Nothing
End Sub

Thanks DJ 😄

[EvenDeejay]

Check out the example called "Excel Macro Example" in the example finder. It uses a .xls file that already got a macro inside. From Labview you can call that macro and run it. Now if you create a template which contain you macro, it will be copied along with the template.

In LabVIEW you can then

Open the excel file -> write data -> run macro -> save file -> close file.

This will then only affect the current excel file. I will try to create an example for you if this is what you are looking for 

Edit:

I say your reply now. I'll try to fix something for you!

p.s good old days 

[EvenDeejay]

Hi again!

I created a new zip file for you. I'm not good at creating macros in Excel. I used the macro from the example in LabVIEW. Anyways.. My theory worked. I still got the Template with the macro and my program creates reports and the macro is copied to each file.

Run my Vi. Push create data, then push "Run macro instead of copy graph". A report is now created and the macro is ran. Just push create data again and the "Run macro instead of copy graph" to create another report. It will increment a counter to change the name of the report. 

P.S: The macro I use is not a good one for my dataset, but it shows that the macro works since it creates a graph based on the data in another sheet of the workbook, just like you mentioned.