AnsweredAssumed Answered

Excel in VEE Questions

Question asked by barriose on Feb 22, 2012
Latest reply on Mar 19, 2012 by Udaybn

Like • Show 0 Likes0
Comment • 5

Hi,

I have a couple of questions regarding the use of Excel with VEE.

1) I am sending data to cells in Excel with Visible=TRUE to monitor the values as the data is being taken. However, when I click on another Excel tab that was previously opened, cell data gets overwritten with values that should be going in to the workbook VEE is using. How can I stop this? I would like to view/make changes to a workbook while VEE sends data to separate workbook without having the data overwritten and still have the option of clicking on the Excel tab VEE is using to once look at the values VEE is sending.

2) I am using the DATATOCELL Excel user object to designate what cell VEE sends the data to. However, I am limited by CELLS input syntax (i.e. A1) for row and column. Is there a way of specifically choosing a row and column number instead of the column being a letter. I have created an algorithm that maps a number to a letter then I concatenate that to the row number to input into DATATOCELL. Is there a simpler way for this? My current method is tedious and not very useful when I need a large number of columns to map to.

Thanks for the help. Any advice is appreciated.

No one else has this question

Outcomes

gojoh Feb 22, 2012 2:19 PM

1) I think this will be hard to do using the Excel menu objects in VEE. The Excel library objects use ActiveBook, ActiveSheet technique when sending and getting data. So if you manually make another sheet the active sheet, VEE will write to this sheet.
To get around this you have to send data to an "absolute" sheet (ie the name of the sheet) but this forces you to use ActiveX programming, the Excel menu objects are not designed to handle this. This is not so difficult (but not as easy as the Excel menu objects) and there are Excel ActiveX examples included in VEE.

2) In the Navigation submenu you find the 'Cell Absolute' objects that should do the work. It returns a cell address from a row/column number.

Actions

barriose Feb 22, 2012 4:35 PM

You were very helpful. Thanks a lot

Actions

Udaybn @ barriose on Mar 12, 2012 12:58 AM

Once you initialize Excel in VEE using Excel library, create a formula object with the following in it:

xlApp.ignoreremoterequests = TRUE ;

This will create an exclusive instance of Excel for VEE. So, you can continue to use Excel for other work and that will not disturb VEE at all. So, the data from VEE will always go where the VEE program says it should go.

At the end of the program, create another formula object with:

xlApp.ignoreremoterequests = FALSE ;

Regards,
Uday

Actions

bOb.ng @ Udaybn on Mar 14, 2012 2:51 AM

I tried Uday's recommendation and it works well if the Excel sheet visible=FALSE. If visible=TRUE, the active sheet still gets written over. However, if the visible=FALSE, it will make any existing open workbook invisible together when the VEE starts the Excel library.

Perhaps ActiveX is the way to go?

Actions

Udaybn @ bOb.ng on Mar 19, 2012 2:45 AM

I don't think it matters whether VISIBLE = TRUE or not. I posted a VEE program to VRF mailing list a few days ago for demonstrating the flag. I checked again with that with VISIBLE = TRUE and I do not see the active workbook being overwritten.

You may have problems depending on when and where you declare the flag. I declare as shown in the program and that works fine.

Normally, when writing to Excel from VEE, I keep that instance of Excel hidden; I see no reason for that file being visible since it is only for VEE and not for the operator. The operator can always open the file from hard disk and look at the contents without disturbing VEE (because of the flag).

This is how I checked this program:

1. Excel VISIBLE = TRUE.
2. I open any Excel workbook for my editing; say, Book1.xls.
3. Run the attached VEE program (place the Excel file temp.xls in the path specified in the program or MODIFY that path to whichever you prefer).
4. Click through the OK buttons (ignore the button names or you may do what the buttons ask you to do).
5. When the program completes, Book1.xls is NOT disturbed, temp.xls is not disturbed and you have a new temp_new.xls created by the program.

Note that temp.xls has 10.1 to 11.0 as the values whereas temp_new.xls has 12 to 21 in the same cells.

regards,
Uday

Attachments

temp.xls17.0 KB
VEE_Excel_rmt_request.vee36.4 KB

Actions

5 Replies

gojoh Feb 22, 2012 2:19 PM
Mark Correct
Correct Answer
1) I think this will be hard to do using the Excel menu objects in VEE. The Excel library objects use ActiveBook, ActiveSheet technique when sending and getting data. So if you manually make another sheet the active sheet, VEE will write to this sheet.
To get around this you have to send data to an "absolute" sheet (ie the name of the sheet) but this forces you to use ActiveX programming, the Excel menu objects are not designed to handle this. This is not so difficult (but not as easy as the Excel menu objects) and there are Excel ActiveX examples included in VEE.

2) In the Navigation submenu you find the 'Cell Absolute' objects that should do the work. It returns a cell address from a row/column number.
Like • Show 0 Likes0
Actions
barriose Feb 22, 2012 4:35 PM
Mark Correct
Correct Answer
You were very helpful. Thanks a lot
Like • Show 0 Likes0
Actions
- Udaybn @ barriose on Mar 12, 2012 12:58 AM
  Mark Correct
  Correct Answer
  Once you initialize Excel in VEE using Excel library, create a formula object with the following in it:
  
  xlApp.ignoreremoterequests = TRUE ;
  
  This will create an exclusive instance of Excel for VEE. So, you can continue to use Excel for other work and that will not disturb VEE at all. So, the data from VEE will always go where the VEE program says it should go.
  
  At the end of the program, create another formula object with:
  
  xlApp.ignoreremoterequests = FALSE ;
  
  Regards,
  Uday
  Like • Show 0 Likes0
  Actions
  - bOb.ng @ Udaybn on Mar 14, 2012 2:51 AM
    Mark Correct
    Correct Answer
    I tried Uday's recommendation and it works well if the Excel sheet visible=FALSE. If visible=TRUE, the active sheet still gets written over. However, if the visible=FALSE, it will make any existing open workbook invisible together when the VEE starts the Excel library.
    
    Perhaps ActiveX is the way to go?
    Like • Show 0 Likes0
    Actions
    - Udaybn @ bOb.ng on Mar 19, 2012 2:45 AM
      Mark Correct
      Correct Answer
      I don't think it matters whether VISIBLE = TRUE or not. I posted a VEE program to VRF mailing list a few days ago for demonstrating the flag. I checked again with that with VISIBLE = TRUE and I do not see the active workbook being overwritten.
      
      You may have problems depending on when and where you declare the flag. I declare as shown in the program and that works fine.
      
      Normally, when writing to Excel from VEE, I keep that instance of Excel hidden; I see no reason for that file being visible since it is only for VEE and not for the operator. The operator can always open the file from hard disk and look at the contents without disturbing VEE (because of the flag).
      
      This is how I checked this program:
      
      1. Excel VISIBLE = TRUE.
      2. I open any Excel workbook for my editing; say, Book1.xls.
      3. Run the attached VEE program (place the Excel file temp.xls in the path specified in the program or MODIFY that path to whichever you prefer).
      4. Click through the OK buttons (ignore the button names or you may do what the buttons ask you to do).
      5. When the program completes, Book1.xls is NOT disturbed, temp.xls is not disturbed and you have a new temp_new.xls created by the program.
      
      Note that temp.xls has 10.1 to 11.0 as the values whereas temp_new.xls has 12 to 21 in the same cells.
      
      regards,
      Uday
      Attachments
      temp.xls17.0 KB
      VEE_Excel_rmt_request.vee36.4 KB
      Like • Show 0 Likes0
      Actions

Excel in VEE Questions

Outcomes

Attachments

Related Content

Recommended Content

Incoming Links