Active X and Excel

[MogaRaghu]

Using LV2012 and Office 2010.

I am trying to create a XLS file to collate data from numerous test certifcates to do some analysis on the test data. Got a good working example in the Help section and have modified it to suit my need. It works but I need to be able to do the following :

1. It always writes the headers on the Worksheet 4. How to make it to write to the Worksheet 1 ?

2. How to auto size the column width to suit the header information ?

3. How to auto save the file to a name I choose in advance. ( All the input fields on the Invoke Method are Variant data types but what I need is a string type...)

Its good that the node exposes so many properties and methods but many of  them are cryptic and the function is not too obvious.

[ben64 · Accepted solution]

---

Raghunathan a écrit :

Using LV2012 and Office 2010.

I am trying to create a XLS file to collate data from numerous test certifcates to do some analysis on the test data. Got a good working example in the Help section and have modified it to suit my need. It works but I need to be able to do the following :

1. It always writes the headers on the Worksheet 4. How to make it to write to the Worksheet 1 ?

2. How to auto size the column width to suit the header information ?

3. How to auto save the file to a name I choose in advance. ( All the input fields on the Invoke Method are Variant data types but what I need is a string type...)

Its good that the node exposes so many properties and methods but many of  them are cryptic and the function is not too obvious.

---

1- By default a new workbook is created with 3 worksheets. After creating the new workbook you have a VI named Open New Worksheet.vi that add a fourth worksheet to the sheets collection. This new worksheet is now the active sheet so when you add the headers they are written to sheet 4.

 You don't need to add a new worksheet, by default a new workbook is created with sheet 1 active. To select a worksheet create a vi similar to Open New Worksheet but replace the Add method with the Item method and set index to the value of the sheet you want to select.

2- You need to use the Range Autofit method for that

3- When you use the Close method use the Save Changes and Filename arguments (you can pass a string to a variant input or use the 'To Variant' function)

Ben64

[MogaRaghu]

Thanks Ben. Just after posting I figured out the worksheet method "Item"

But the next two suggestions you gave helped a lot. The one problem with the ActiveX properties is that most of them are not very descriptive and one needs to experiment a lot to figure which to use to get what we want !!

I dont think there is a Help listing which gives this information ??

[ben64]

---

Raghunathan a écrit :

Thanks Ben. Just after posting I figured out the worksheet method "Item"

But the next two suggestions you gave helped a lot. The one problem with the ActiveX properties is that most of them are not very descriptive and one needs to experiment a lot to figure which to use to get what we want !!

I dont think there is a Help listing which gives this information ??

---

Here is the best place to get this kind of information. 

Ben64

[MogaRaghu]

Perfect. Thats exactly waht I wanted. Thanks.