Excel embedding objects

[MIV]

I am trying to insert an object, .zip file in Excel sheet, but everything I try fails. Excelreport.fp doesn't have functions for such task and Excel9.0 does not have help library although I loaded one from MS web. Can someone help me write simple example that inserts .zip file into Excel sheet.

Regards,

MIV

[Kurt-P]

Hey MIV,

I have a few questions for you. Why are you trying to put this zip file in an Excel spread sheet? What version of CVI are you using? Is this Excel 2000? What OS are you using? Could you clarify what you mean when you say that excel 9 does not have a help library but you still loaded one form MS web? Could you perhaps post a link to that? Would it be possible to use a Macro instead of CVI to insert this file into the spreadsheet?

http://office.microsoft.com/en-us/excel-help/create-a-macro-HP005204711.aspx

So far I have not found any functions that explictly do this. There might be some function exposed in the Excel API that you could call into to embed an object.

Regards,

-KP

[MIV]

CVI 2012

Windows7 64

Zip file is customer requirement

Excel is 2010

I used excelreportdemo example that uses Excel 9.0 library but was missing help file. Downloaded file from http://www.microsoft.com/en-us/download/details.aspx?id=7273.

I would have to dynamically create macros to include proper zip file and assign it proper name.

Shouldn't I be able to use Excel_NewOLEObject (NULL, 1, LOCALE_NEUTRAL, 0, ); and than assign it to a cell and change it's properties. When I try to create new object is reports "Class not registered" error.

[Kurt-P]

Hey MIV, it does not look like NI provides a function that allows you to import a .zip file into an Excel Spreadsheet. I tried finding a strange work around where I tried to import it as a picture but that did not work. I'd imagine it's because of the way Excel tried to render the "image". The only documentation I could find for the Excel_NewOLEObject function was in C# or VB.NET. What languages are you using? It might be worth a post in the Microsoft forums as well since this is a Windows function that calls a Windows program. Those users would be more familiar with it than I would be.

Regards,

-KP 

[MIV]

Thank you for your help. At least I am not insane doing everything wrong. I assumed it is easy to do in CVI like everything else. I will use macro and insert file manually. Is there a way I can execute macro from CVI?

[Kurt-P]

No problem MIV. I found an example that goes over how to run Excel macros with CVI calls:

http://zone.ni.com/devzone/cda/epd/p/id/2158

I know it says it's for CVI 6.0 and Excel 2000, but I was able to run it in Excel 2007 and CVI 2012. This should be a good introduction to calling macros in CVI. 

Regards,

-KP

[MIV]

I got it running in Excel 2010 but when I execute macro from my Personal.xlsb file it doesn't execute in my target file but in xlsb file. I can keep collection of my macros in personal.xlsb but it is not practical to send customer excel file containing macros. How can I remove macros?

MIV

[Kurt-P]

MIV,

Which .xlsb file are you refering to? Is the the one that came with the project? And where do you want to delete the macro from? I found that the Personal.xlsb file and says there are hidden. The steps to unhide it are: 

1. On the View tab, in the Window group, click Unhide.
2. Under Unhide workbooks, click PERSONAL, and then click OK.

Here is the link where I found the information. It be useful since it has more information about macros in general. 

http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010014111.aspx#BMdeletemacro

[MIV · Accepted solution]

Personal.xlbs is the file I was referring to. I had it unhidden but if I call a macro stored in personal.xlsb I get "exception occurred" error message. In TestMacro.xls I see icon for the macro I want to execute and I can do it manually but not programmatically. I found solution for the problem:

I have to open personal.xlsb and than TestMacro.xls. The last accessed file will be updated. This portion of code is modified function from TestMacro.c

//----------------------------------------------------------------------------
// Open Excel and the selected worksheet
//----------------------------------------------------------------------------

int CVICALLBACK OpenCallback (int panel, int control, int event,
  void *callbackData, int eventData1, int eventData2)
{
 switch (event)
  {
  case EVENT_COMMIT:
   if (FileSelectPopup ("", "*.xls", "*.xls, *.xlsx", "", VAL_LOAD_BUTTON, 0,
         0, 1, 0, excelFileName))
    {
    SetCtrlAttribute (panelHandle, PANEL_RUNBUTTON, ATTR_DIMMED, 0);
    SetCtrlAttribute (panelHandle, PANEL_CLOSEBUTTON, ATTR_DIMMED, 0);
    SetCtrlAttribute (panelHandle, PANEL_OPENBUTTON, ATTR_DIMMED, 1);
    SetCtrlAttribute (panelHandle, PANEL_QUITBUTTON, ATTR_DIMMED, 1);
   
    //Create Excel.Application instance
    errStatus = Excel_NewApp (NULL, 1, LOCALE_NEUTRAL, 0, &excelAppHandle);
    //Make Excel visible
    errStatus = Excel_Set_ApplicationProperty (excelAppHandle, NULL,Excel_AppVisible,CAVT_BOOL, VTRUE);
    //Do not display alert when document is closed without saving
    errStatus = Excel_Set_ApplicationProperty (excelAppHandle, NULL,Excel_AppDisplayAlerts,CAVT_BOOL, VFALSE);
   
    //Acess Work books handle and open the selected file.
       errStatus =Excel_Get_ApplicationProperty (excelAppHandle, NULL, Excel_AppWorkbooks, CAVT_OBJHANDLE,&excelWorkbooksHandle);
    
    errStatus = Excel_WorkbooksOpen (excelWorkbooksHandle, NULL,
            "C:\\Users\\17346\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\personal.xlsb", CA_DEFAULT_VAL, /*replace 17346 with your user name*/
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL,
            &excelWorkSheetHandle2);
           
    errStatus = Excel_WorkbooksOpen (excelWorkbooksHandle, NULL,
            excelFileName, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL, CA_DEFAULT_VAL,
            CA_DEFAULT_VAL,
            &excelWorkSheetHandle);
            
   
   
    
    //errStatus = Excel_NewGlobal (NULL, 1, LOCALE_NEUTRAL, 0, &ohNew);
    
      
    //Discard unwanted handle
    CA_DiscardObjHandle (excelWorkSheetHandle2);
    CA_DiscardObjHandle (excelWorkSheetHandle);
    
    }
          
   break;
  }
 return 0;
}

[Kurt-P]

Hey MIV, Thank you for posting the solution so other users can use it as a reference. Best of luck with your future projects!

-KP