-
September 7th, 2002, 02:27 PM
#1
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.
-
September 7th, 2002, 03:25 PM
#2
_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
-
September 7th, 2002, 04:20 PM
#3
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
-
September 9th, 2002, 08:22 AM
#4
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
-
September 9th, 2002, 09:08 AM
#5
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
-
October 5th, 2020, 10:05 AM
#6
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
-
October 5th, 2020, 01:10 PM
#7
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
-
October 6th, 2020, 12:03 AM
#8
Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.
Originally Posted by VictorN
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
-
October 6th, 2020, 01:53 AM
#9
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++23 Compiler: Microsoft VS2022 (17.6.5)
-
October 6th, 2020, 11:36 PM
#10
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.
-
October 7th, 2020, 01:22 AM
#11
Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.
Originally Posted by vcdebugger
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.
-
October 7th, 2020, 01:36 AM
#12
Re: Opening An Excel File in Visual C++ using Workbook.Open( ) gives an error.
Originally Posted by Arjay
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
-
October 7th, 2020, 02:13 AM
#13
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++23 Compiler: Microsoft VS2022 (17.6.5)
-
October 8th, 2020, 10:46 PM
#14
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|