none
Write on excel cells

    Question

  • Hello,

    I am trying to write value on excel cells.

    Here is the code i am using right now:

    #include "stdafx.h"
    
    
    #import "C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\MSO.DLL" no_implementation rename("RGB", "ExclRGB") rename("DocumentProperties", "ExclDocumentProperties") rename("SearchPath", "ExclSearchPath")
    
    
    #import "C:\Program Files (x86)\Common Files\microsoft shared\VBA\VBA6\VBE6EXT.OLB" no_implementation
    
    #import "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" rename("DialogBox", "ExclDialogBox") rename("RGB", "ExclRGB") rename("CopyFile", "ExclCopyFile") rename("ReplaceText", "ExclReplaceText")
    
    
    #include <stdexcept>
    
    #include <iostream>
    
    using namespace std;
    
    using namespace Excel;
    
    using namespace Office;
    
    using namespace VBIDE;
    
    
    
    int main()  
    {  
        HRESULT hr = CoInitializeEx(0, COINIT_MULTITHREADED);   
        if (FAILED(hr))   
        {  
            cout << "Failed to initialize COM library. Error code = 0x"  
               << hex << hr << endl;   
            return hr;  
        }  
      
        // Create Excel Application Object pointer  
        Excel::_ApplicationPtr pXL;  
      
        if ( FAILED( pXL.CreateInstance( "Excel.Application" ) ) )  
        {  
            cout << "Failed to initialize Excel::_Application!" << endl;  
            return 0;  
        }  
          
        // Open the Excel Workbook, but don't make it visible  
        pXL->Workbooks->Open( L"C:\\Users\\test\\Documents\\Visual Studio 2010\\Projects\test\\test.xls" );  
        pXL->Visible = false;  
      
        // Access Excel Worksheet and return pointer to Worksheet cells  
        Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;  
        Excel::RangePtr pRange = pWksheet->Cells;  
      
        // Read an Excel data cell. (Note Excel cells start from index = 1)  
        double value = pRange->Item[1][1];  
      
        // Write/modify Excel data cells + save. (reopen xls file to verify)  
        pRange->Item[1][1] = 5.4321;  
        pRange->Item[1][2] = 1.1211;  
        pWksheet->SaveAs("C:\\Users\\test\\Documents\\Visual Studio 2010\\Projects\test\\test.xls");  
          
        // Exit the Excel Com object  
        pXL->Quit();   
      
        return 0;  
    }
    

    I am having a _com_error at memory location 0x003efa08, i think the problem is when i try to open the excell workbook.

    Anyone know how to solve this?

    I am on visual c++ 2010, Windwows 7.

    Thanks

    Tuesday, January 14, 2014 9:15 PM

Answers

  • Hi Limehaze,

    Welcome to the MSDN forum!

    I have tested your code using visual studio 2010 ultimate. I deleted "pXL->Visible = false" and it works fine without displaying the excel.   

    I suggest you check if C:\\Users\\test\\Documents\\Visual Studio 2010\\Projects\test\\test.xls exists. Please make sure that it is test.xls rather than test.xlsx.

    Moreover, please post your issue in Microsoft Office for developers forum if you have any questions about office.

    Best regards,

    Sunny

    • Marked as answer by Limehaze Thursday, January 16, 2014 1:58 PM
    Wednesday, January 15, 2014 6:28 AM

All replies

  • Hi Limehaze,

    Welcome to the MSDN forum!

    I have tested your code using visual studio 2010 ultimate. I deleted "pXL->Visible = false" and it works fine without displaying the excel.   

    I suggest you check if C:\\Users\\test\\Documents\\Visual Studio 2010\\Projects\test\\test.xls exists. Please make sure that it is test.xls rather than test.xlsx.

    Moreover, please post your issue in Microsoft Office for developers forum if you have any questions about office.

    Best regards,

    Sunny

    • Marked as answer by Limehaze Thursday, January 16, 2014 1:58 PM
    Wednesday, January 15, 2014 6:28 AM
  • Hi Limehaze,

    I add a addition here.

    I notice that you use "OFFICE14".

    If you use Excel 2010, please use like "pXL->Visible[0] = false;".

    Best regards,

    Sunny

    Wednesday, January 15, 2014 6:42 AM
  • Thanks sunny, its starting to work.

    I have a new problem related to write on excel cells.

    Here is my code:

    	bool To_CharStar(String^ source, char*& target)
     {
       int len = (source->Length+1) * 2;
       target = new char[len];
       pin_ptr<const wchar_t> wch = PtrToStringChars(source);
       return wcstombs(target, wch, len) != -1;
     }
    
    
       char *TBtext1;
       To_CharStar(textBox1->Text, TBtext1);
    
       char *TBtext2;
       To_CharStar(textBox2->Text, TBtext2);
    
       pRange->Item[textBox1][textBox2]  = "Testing 123";  
    
      	

    I got this error:

    error C2664: 'Excel::Range::PutItem' : cannot convert parameter 1 from 'std::string' to 'const _variant_t &
    
    Reason: cannot convert from 'std::string' to 'const _variant_t'
    

    I want to be able to control witch cells i am going to modidy in a textbox.

    Any idea?

    Wednesday, January 15, 2014 9:21 PM
  • Hi Limehaze,

    I'm glad to hear that you got that working.

    Please open a new thread of a new issue for better response.

    Best regards,

    Sunny


    Thursday, January 16, 2014 2:18 AM