CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 14 of 14

Thread: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

  1. #1
    Join Date
    Aug 2002
    Location
    USA
    Posts
    8

    Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Hi everyone,
    I need help in opening an excel file in visual c++ using WorkBook.Open ().
    I am passing a parameter to Open() method. This Parameter has the file name assigned to it. The file was obtained by using CFileDialog. WHen I hadcode the file in Workbook.open(), it works fine. But, if I pass the parameter which has the exactly the same file name, it fails. Could you please advice me on this.
    Look for //FAILS HERE in the following code for the error line in the code.

    Thanks in advance.
    Venkat


    // code for reading excel data

    void CExcelArraysDlg::OnRun()
    {
    // Read a file name for opening in Excel using WorkBook.Open

    CString myFileName;
    CFileDialog dlg( TRUE, NULL, NULL, OFN_ALLOWMULTISELECT, NULL, NULL );
    DWORD MAXFILE = 2562; //2562 is the max
    dlg.m_ofn.nMaxFile = MAXFILE;
    char* pc = new char[MAXFILE];
    dlg.m_ofn.lpstrFile = pc;
    dlg.m_ofn.lpstrFile[0] = NULL;
    dlg.m_ofn.lpstrFilter = "Excel files (*.xls)\0*.xls\0All files (*.*)\0*.*\0\0";
    dlg.m_ofn.Flags |= OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST| OFN_READONLY | OFN_HIDEREADONLY;

    if (dlg.DoModal() == IDOK)
    {
    CStdioFile InputFile;
    CString strRecord;
    BOOL bGotData;

    //Store the File Name to used in Excel
    myFileName = dlg.GetPathName();

    if (InputFile.Open(dlg.GetPathName(), CFile::modeRead | CFile::shareDenyWrite))
    {
    bGotData = InputFile.ReadString(strRecord);
    while (bGotData)
    {
    bGotData = InputFile.ReadString(strRecord);
    }
    InputFile.Close();
    }
    else
    {
    AfxMessageBox("Failed to open file.");
    }
    }

    // OLE EXCEL AUTOMATION USING VISUAL C++

    // OLE Variant for Optional.
    COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

    _Application objApp;
    _Workbook objBook;
    Workbooks objBooks;
    Worksheets objSheets;
    _Worksheet objSheet;
    Range objRange;
    VARIANT ret;


    // Instantiate Excel and open an existing workbook.
    objApp.CreateDispatch("Excel.Application");
    if (!objApp)
    {
    AfxMessageBox("Cannot start Excel.");
    return;
    }

    objBooks = objApp.GetWorkbooks();

    myFileName.Replace("\\","\\\\");
    myFileName.Insert(0,"\"");
    myFileName.Insert(myFileName.GetLength()+1,"\"");
    LPCTSTR newFile1;
    newFile1 = myFileName;

    objBook = objBooks.Open(newFile1, // FAILS HERE
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional);

    objSheets = objBook.GetWorksheets();

    //Close the workbook without saving changes

    objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);

    //and quit Microsoft Excel.
    objApp.Quit();

    }

    //end of code
    Last edited by cnext; September 7th, 2002 at 04:26 PM.

  2. #2
    Join Date
    Dec 2001
    Location
    New York
    Posts
    529
    _Application app;
    _Workbook book; // workbook object
    _Worksheet sheet; // worksheet object
    Workbooks books;
    Worksheets sheets;
    LPDISPATCH lpDisp; //often reused variable.


    CString fileName = "c:\\filesfolder\\excelfiles\\jul00.xls";

    // Initialize COM for this thread...
    //CoInitialize(NULL);

    // open excel application
    //app.AttachDispatch((LPDISPATCH) &pXlApp);
    if(!app.CreateDispatch("Excel.Application"))
    {
    AfxMessageBox("Could not create Excel Application Object.");
    Exit();
    }

    //get the workbook cp;;ectopm/
    lpDisp = app.GetWorkbooks(); // get a n IDispatch pointer.
    ASSERT (lpDisp);
    books.AttachDispatch(lpDisp); // attach the IDispatch poiner to the books object.

    //to open an existing workbook, you need to provide all
    //arguments for the open member function,In the case of
    //excel 2002 you need to provide 16 arguments.
    //the code below opens a workbook and adds it to the workbook's collection object. It shows 13 arguments, required for excel 2000.
    //you need to modify the path and file name for your own workbook

    lpDisp = books.Open(fileName, covOptional,covOptional, covOptional, covOptional, covOptional, covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional); // return workbooks's IDispatch
    ASSERT(lpDisp);
    book.AttachDispatch(lpDisp);
    app.SetVisible(TRUE); // set visible....
    app.SetUserControl(TRUE); // let the user control
    BP

  3. #3
    Join Date
    Aug 2002
    Location
    USA
    Posts
    8
    Thanks BP. But, I am trying use the filename which I got from using CFileDialog open file dialog.

    Here is the code
    // Read a file name for opening in Excel using WorkBook.Open

    CString myFileName;
    CFileDialog dlg( TRUE, NULL, NULL, OFN_ALLOWMULTISELECT, NULL, NULL );
    DWORD MAXFILE = 2562; //2562 is the max
    dlg.m_ofn.nMaxFile = MAXFILE;
    char* pc = new char[MAXFILE];
    dlg.m_ofn.lpstrFile = pc;
    dlg.m_ofn.lpstrFile[0] = NULL;
    dlg.m_ofn.lpstrFilter = "Excel files (*.xls)\0*.xls\0All files (*.*)\0*.*\0\0";
    dlg.m_ofn.Flags |= OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST| OFN_READONLY | OFN_HIDEREADONLY;

    if (dlg.DoModal() == IDOK)
    {
    CStdioFile InputFile;
    CString strRecord;
    BOOL bGotData;

    //Store the File Name to used in Excel
    myFileName = dlg.GetPathName();


    AT this point, myFiname has the value
    C:\Test.xls

    I inserted the \ and inserted " to the file name to make it as
    "C:\\Test.xls"

    Now myFileName has the value
    "C:\\Test.xls"

    I am using this myFileName in the following code to open the workbook

    objBook = objBooks.Open(myFileName,
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional);

    It fails in the above statement. It says it can not open the file
    ""C:\\Test.xls""


    But, if I assign the file name to myFileName as
    myFileName = "C:\\Test.xls"


    and use this name in
    objBook = objBooks.Open(myFileName,
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional,
    VOptional, VOptional, VOptional, VOptional);


    it works fine now.

    AM I missing some thing first scenario.

    Thanks in advance
    Venkat

  4. #4
    Join Date
    Dec 2001
    Location
    New York
    Posts
    529
    did you try not to reassign the file name.

    Code:
    you don't wanna do it....
            I inserted the \ and inserted " to the file name to make it as
    "C:\\Test.xls"
    
    Now myFileName has the value
    "C:\\Test.xls"
    leave it as it is.... and open it..
    AT this point, myFiname has the value
    C:\Test.xls
    BP

  5. #5
    Join Date
    Aug 2002
    Location
    USA
    Posts
    8
    BP, Thanks for the help.I got it to work in a different way.
    I used to Application.GetOpenFileName to get the filename and then used this name to open the workbook.

    Thanks
    Venkat

  6. #6
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    460

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    In related to this, how to open a password protected XL file in MFC

    Code:
    int stgm_mode;
    
    	if ((mode & (ios_base::in|ios_base::out)) == (ios_base::in|ios_base::out))
    		stgm_mode = STGM_READWRITE | STGM_SHARE_EXCLUSIVE;
    	else if (mode & ios_base::out)
    		stgm_mode = STGM_WRITE | STGM_SHARE_EXCLUSIVE;
    	else
    		stgm_mode = STGM_READ | STGM_SHARE_EXCLUSIVE;
    
    	HRESULT hr = StgOpenStorage(filename, NULL, stgm_mode, NULL, 0, &_pStg);
    
    	return SUCCEEDED(hr);
    here I am not getting in which parameter should we pass the password

  7. #7
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,850

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    How (as excel workbook/worksheet, as a database, as binary, ...) are you going to open it and what for?
    Victor Nijegorodov

  8. #8
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    460

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Quote Originally Posted by VictorN View Post
    How (as excel workbook/worksheet, as a database, as binary, ...) are you going to open it and what for?
    I am opening it as a .xls file it self using the function StgOpenStorage(...). Any idea to open a .xls file using this function when it is password protected.

    Without password protection this function opens the XLS file successfully but when the XLS file is password protected then it throw a access violation error when you try to open this file. Want to know if there a method to resolve this issue

  9. #9
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,324

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Do you know the correct password?
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2019 (16.8.2)

  10. #10
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    460

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Yes . We are setting the password explicitly for the security purpose for the xls file.

    from the code we want to open this file using the function StgOpenStorage(...) because using workbook method to open the file we have issue to find out the EXCEL application path installed on that PC.

  11. #11
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,401

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Quote Originally Posted by vcdebugger View Post
    Yes . We are setting the password explicitly for the security purpose for the xls file.

    from the code we want to open this file using the function StgOpenStorage(...) because using workbook method to open the file we have issue to find out the EXCEL application path installed on that PC.
    You can easily find the path by looking at the progid COM registration in the registry.

  12. #12
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    460

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Quote Originally Posted by Arjay View Post
    You can easily find the path by looking at the progid COM registration in the registry.
    Since I am new to this it would be helpful if you point to any sample code doing this.

    Also we need to do import DLLS of some DLLs in order to import the name spaces.. like mso..DLL etc

    How to import the DLLs dynamically in the code using #import statement

  13. #13
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,324

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Also we need to do import DLLS of some DLLs in order to import the name spaces.. like mso..DLL etc

    How to import the DLLs dynamically in the code using #import statement
    Have a look at post #14 at https://forums.codeguru.com/showthre...ata&highlight=

    You might need to change the paths depending upon your version of installed office.
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2019 (16.8.2)

  14. #14
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,401

    Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.

    Here is the approach for finding an app's installation path using its COM registry registration. This works for any application that is COM enabled.

    COM applications follow a strict pattern for how they are registered, and the COM subsystem uses this registration to locate the app when it goes to launch the COM component or COM server. All the COM registrations are under the HKEY_CLASSES_ROOT and retrieving the path to an application is a two-step process.

    First you locate the Program Id (like Excel.Application or Word.Application) then extract its Clsid (Class Id).

    So for Excel we get the clsid value HKEY_CLASSES_ROOT\Excel.Application\CLSID. The value there is {00024500-0000-0000-C000-000000000046}.

    Second we look up this value as a key under HKEY_CLASSES_ROOT\CLSID and retrieve its LocalServer32 value.
    HKEY_CLASSES_ROOT\CLSID\{00024500-0000-0000-C000-000000000046}\LocalServer32 contains 'C:\Program Files\Microsoft Office\Root\Office16\EXCEL.EXE'

    Finding Word is similar:
    HKEY_CLASSES_ROOT\Word.Application\CLSID returns {000209FF-0000-0000-C000-000000000046}
    HKEY_CLASSES_ROOT\CLSID\{000209FF-0000-0000-C000-000000000046}\LocalServer32 contains 'C:\Program Files\Microsoft Office\Root\Office16\WINWORD.EXE'

    If you are trying to locate an application that you aren't sure whether it's COM enabled, search for the app.exe in the registry under HKEY_CLASSES_ROOT\CLSID.

    You're looking for the full path of the app to appear under a LocalServer32 key. If you find it, retrieve it's parent class Id, and use it to locate the Program Id under HKEY_CLASSES_ROOT. If you don't find anything, the app is not COM enabled.

    Microsoft has a CRegKey class for C++ that makes reading the registry simple.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)