LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Excel Add Worksheet and Rename Worksheet not Working

Solved!
Go to solution

The Excel Add Worksheet VI isn't working for me.  Specifically, it fails at the "close existing Shapes reference" part.  The Shapes reference produced by the VI is always null.  Similarly, Rename Worksheet doesn't work either.  It passes a null reference for the _Worksheet Name property node.

 

This is using Office 2016 Professional Plus and LabVIEW 2016 16.0f2.

 

 

This is the second inexplicable error I've had with Excel and LabVIEW now, and I'm starting to doubt NI does any quality testing on this stuff when a new version of Office comes out.  I suppose I could just write macros to get around these problems, but does anybody else have this problem?  Is there a workaround?

 

Edited to include Rename Worksheet to the pile.

0 Kudos
Message 1 of 9
(5,230 Views)

Hello,

as the VI fails, you get an error, could you please name your error number?

In addition, could you please attach your code?

Thanks.

 

Greetings and Regards,
James
0 Kudos
Message 2 of 9
(5,142 Views)

I'm having similar problems, also using office 2016. I'm able to write to one worksheet, but as soon as I want to add a second worksheet i'm not able to. I'm using some code that I had working in Labview/Office 2007, so it must be NI's issues with Office 2016.

0 Kudos
Message 3 of 9
(5,115 Views)
Solution
Accepted by SmithGo

I've actually come up with a workaround for this and my own problem.  The problem I had after adding a worksheet was that, if you're in a loop, you need to use a shift register to forward the report reference.  Adding a worksheet modifies the report reference, so using the old one doesn't work.  Another problem was switching to a worksheet that doesn't exist messes up the report as well.  So I wrote this VI to handle switching and worksheet creation.  The basic idea is, check to see if a worksheet name exists (shorten the name to 31 characters if longer first because that's Excel's limit.  Yes really, in the Year of Our Lord, 2017), and if it does, switch to it.  If not, create it and rename it.  Below is a shot of that VI and the VI it runs in.  If it's unclear I can attach a non-company copy of the VI.

Download All
Message 4 of 9
(5,112 Views)

This worked really well! Thank you for this. I noticed that you had a custom VI to save the excel file - I have a similar implementation to write and save my Excel files, but when trying to save the file I get "Error 5 occurred at Delete in NI_Excel.lvclass Save Report to File.vi.... " I was just wondering if you had some workaround in your custom Save VI.

0 Kudos
Message 5 of 9
(5,071 Views)

The Save Excel VI was written to force the user to save the file without overwriting an existing one.  It calls the Save File dialog in a while loop until the user picks an unused name/path for the file, then calls the Save Report to File VI.  I don't quite recognize the VI you're calling there, but it looks like the old Open or Create New File VI?  You wouldn't use that here because you're report file is already opened when it was created.  Instead, you just need to build a file name, and maybe a path to it.  Use Build Path or String to Path instead.  And use the file extension ".xlsx", that's for the current Excel Workbook format, unless you really need compatibility with Excel 97-2003.

 

Edit: There is also no need to use a case structure with the error code as you have done here.  You can just feed the error directly into Save Report to File, unless you're doing something special when there is an error.

0 Kudos
Message 6 of 9
(5,066 Views)

Hello SmithGo,

 

Would you mind to share the save excel vi for me as reference? But the way do you have any ideas on Error code 41000: "NI-Reports:  You attempted to use a function that has no effect with the current report type."?

0 Kudos
Message 7 of 9
(4,754 Views)

Sure, I attached an image.  So the way it works is, If the file coming in from the Save File Path and Title doesn't exist, it just goes through and nothing else happens.  If it does exist, then the File Dialog is opened with "-new" appended to the input file name as a default.  It will keep in the loop until the user enters a legitimately new name.  I put some cutesy thing in there about which error code gets sent out of the loop, but I don't think it's really necessary.

 

I can't help with your error unless you show me what you're trying to do.  I would guess you've used the wrong report VI for the type of report you are working with.

0 Kudos
Message 8 of 9
(4,739 Views)

Thanks, SmithGo.

 I've solved the error. Thank you for sharing.

0 Kudos
Message 9 of 9
(4,583 Views)