-
January 3rd, 2021, 06:40 PM
#1
Excel Automation Headaches
1, using vs2019
1. have ms office pro plus 2019
have googled many excel automation articles but they all fail by either:
1. not compiling
2. compiling but not working
3. not allowing one to get to compile process because vs2019 apparently does not allow selection of type libs
4. other - eg article written in 2020 but uses vc6.0
the closest i have come is using the article
https://www.technical-recipes.com/20...th-excel-in-c/
it suggests 3 import statements:
#import "C:\\Program Files\\Common Files\\microsoft shared\\OFFICE14\\MSO.DLL" \
rename( "RGB", "MSORGB" )
using namespace Office;
#import "C:\\Program Files\\Microsoft Office\\root\\vfs\\ProgramFilesCommonX86\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB"
using namespace VBIDE;
#import "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE" \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" ) \
exclude( "IFont", "IPicture" ) no_dual_interface
the first two imports compile no problem but adding the last import spawns all sorts of error:
1>C:\Users\j\ExcelAutomation\Debug\excel.tlh(29084,18): error C2027: use of undefined type 'Excel::Office'
1>C:\Users\j\ExcelAutomation\Debug\excel.tlh(29076): message : see declaration of 'Excel::Office'
1>C:\Users\j\ExcelAutomation\Debug\excel.tlh(29084,35): error C3646: 'Type': unknown override specifier
1>C:\Users\j\ExcelAutomation\Debug\excel.tlh(29098,22): error C2027: use of undefined type 'Excel::Office'
cannot figure out what is wrong - appreciate any insights.
if it matters i am trying to do this thru an MFC dlg based app.
i also checked if office 14 files are on my pc - they are not.
thx - j
Last edited by jim enright; January 4th, 2021 at 08:10 AM.
Reason: add more info
-
January 4th, 2021, 04:25 AM
#2
Re: Excel Automation Headaches
This is what I use with VS2019 for Excel 2010:
Code:
#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE14\\mso.dll" \
rename( "RGB", "MSORGB" ) \
rename( "DocumentProperties", "MSDocument") \
rename( "SearchPath", "MSSearch")
#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"
#import "c:\\Program Files (x86)\Microsoft Office\\Office14\\excel.exe" \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" )
using namespace Excel;
....
int main()
{
const auto hr {CoInitializeEx(0, COINIT_MULTITHREADED)};
if (FAILED(hr))
return (cout << "Failed to initialize COM library. Error code = 0x" << hex << hr << endl), 1;
_ApplicationPtr pXL;
if (FAILED(pXL.CreateInstance("Excel.Application")))
return (cout << "Failed to initialize Excel::_Application!" << endl), 2;
_WorkbookPtr pBook;
try {
pBook = pXL->Workbooks->Open("File Name");
}
catch (...) {
pXL->Quit();
return (cout << "Cannot open spreadsheet file " << "File Name" << endl), 3;
}
pXL->PutVisible(0, FALSE);
const _WorksheetPtr pWksheet {pXL->ActiveSheet};
const RangePtr pRange {pWksheet->Cells};
....
}
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)
-
January 4th, 2021, 08:30 AM
#3
Re: Excel Automation Headaches
This is a complete program that compiles OK with MS VS2019 for Excel 2010 on Windows 7. This is the basis for all of my Excel automation code. Sorry, I can't try it with Windows 10 as I haven't got Office installed on that system.
Code:
#pragma warning (disable : 4471)
#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE14\\mso.dll" \
rename( "RGB", "MSORGB" ) \
rename( "DocumentProperties", "MSDocument") \
rename( "SearchPath", "MSSearch")
#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"
#import "c:\\Program Files (x86)\Microsoft Office\\Office14\\excel.exe" \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" )
#include <iostream>
#include <string>
using namespace Excel;
using namespace std;
const string sname = "c:\\MyProgs\\Level_4_60.xlsm"; // Complete path name to Excel filter file
int main()
{
const auto hr {CoInitializeEx(0, COINIT_MULTITHREADED)};
if (FAILED(hr))
return (cout << "Failed to initialize COM library. Error code = 0x" << hex << hr << endl), 1;
_ApplicationPtr pXL;
if (FAILED(pXL.CreateInstance("Excel.Application")))
return (cout << "Failed to initialize Excel::_Application!" << endl), 2;
_WorkbookPtr pBook;
try {
pBook = pXL->Workbooks->Open(sname.c_str());
}
catch (...) {
pXL->Quit();
return (cout << "Cannot open spreadsheet file " << sname << endl), 3;
}
pXL->PutVisible(0, FALSE);
const _WorksheetPtr pWksheet {pXL->ActiveSheet};
const RangePtr pRange {pWksheet->Cells};
const int nRange {12}; // Number of ranges
const int rRow {15}; // Number of rows per range
const int rCol {4}; // Number of cols per range
const int rcStart {8}; // H for starting col of first range
const int rrStart {10}; // Starting row for the ranges
int rData[nRange][rRow][rCol] {0}; // Contains the data for the various required ranges from the spreadsheet
bool bad {false}; // Has data read failed
int vPar6 {};
int vMin6 {};
int vMax6 {};
try {
vPar6 = (int)pRange->Item[8][25]; // Y8
vMin6 = (int)pRange->Item[8][48]; // AV8
vMax6 = (int)pRange->Item[8][50]; // AX8
// Obtain the range data
for (int r {0}; r < nRange; ++r)
for (int cl {0}; cl < rCol; ++cl)
for (int rw {0}; rw < rRow; ++rw)
rData[r][rw][cl] = (int)pRange->Item[rrStart + rw][rcStart + (r * rCol) + cl];
}
catch (...) {
bad = true;
}
pWksheet->Release();
pBook->Release();
pXL->Quit();
if (bad)
return (cout << "Cannot read the data!" << endl), 4;
}
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)
-
January 4th, 2021, 10:54 AM
#4
Re: Excel Automation Headaches
thanks again for the response. seems to me that this will not work because the import of the EXCEL.EXE file
will still fail to compile.
however, did notice one thing:
in your code the mso.dll import and the excel.exe import might both be version 14 whereas in the referenced link
one of the imports might be a version 14 and the other one a version 16. so i will see about downloading the
complementary versions of these two imports (if two versions exist) and and check out if either setting both
to versions 16 or both to version 14 will compile and run.
nonethelss if anyone out there sees something else as the problem please pipe up.
thx - j
here are the file versions i see on my machine (the excel.exe time stamp might be bogus)
MSO.DLL 6/27/2013 12:29 PM
EXCEL.EXE 12/27/2020 10:27 AM
could you provide yours?
Last edited by jim enright; January 4th, 2021 at 11:11 AM.
Reason: add more info
-
January 4th, 2021, 12:46 PM
#5
Re: Excel Automation Headaches
mso.dll 17/11/2020 00:10 version 14.0.7263.5000
excel.exe 17/11/2020 0:04 version 14.0.7263.5000
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)
-
January 4th, 2021, 01:19 PM
#6
Re: Excel Automation Headaches
Originally Posted by 2kaud
mso.dll 17/11/2020 00:10 version 14.0.7263.5000
excel.exe 17/11/2020 0:04 version 14.0.7263.5000
does not look like that helps much. 2 questions:
1. can you somehow make those available for download or
2. can you identify the download source for these files?
thx again - j
-
January 4th, 2021, 02:16 PM
#7
Re: Excel Automation Headaches
Originally Posted by jim enright
1, using vs2019
1. have ms office pro plus 2019
have googled many excel automation articles but they all fail by either:
1. not compiling
2. compiling but not working
3. not allowing one to get to compile process because vs2019 apparently does not allow selection of type libs
4. other - eg article written in 2020 but uses vc6.0
...
The best and simplest way would be using https://docs.microsoft.com/en-us/pre...-mfc-fill-data
The only problem here is the impossibility to create (MFC) classes from type library. This feature was removed from class wizard of VS 2019.
There is, however, a workaround: install VS 2017 (one of the first versions) just to create these classes from the typelib.
Another way would be use something like this one.
These both worked for me (the latter also works for MS Outlook)
Victor Nijegorodov
-
January 5th, 2021, 09:25 AM
#8
Re: Excel Automation Headaches
v - thanks for the input.
will look into the other solutions but the compiler issues are caused by the inconsistencies between the mso.dill file and the
excel.exe file. are you saying that finding a compare of compatible files will not solve the problem?
id are you saying that finding a compatible pair of these files will allow the comile to continue but after that the compile or
the execution will fail due to the inability of vs2019 to construct the necessary mfc classes from the info in these import files?
thx -j
-
January 5th, 2021, 10:42 AM
#9
Re: Excel Automation Headaches
Have you got more than one mso.dll/excel.exe files located on your system? perhaps you're using the wrong files?
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)
-
January 5th, 2021, 10:55 AM
#10
Re: Excel Automation Headaches
Originally Posted by 2kaud
Have you got more than one mso.dll/excel.exe files located on your system? perhaps you're using the wrong files?
no - just one mso.dll and one excel.exe - but as you can see from prior messages class members are referenced but not defined - so i am suspicious that one is not the correct complement to the
the files are being found in the compile process but throwing errors - i did notice that one in a ...14 directory and one in a ,,,16 directory
-
January 5th, 2021, 01:09 PM
#11
Re: Excel Automation Headaches
The only last thing I can suggest is that you completely un-install ALL office products, make sure that are no folders called OFFICEnn or VBA, and then re-install complete Office.
PS You might try an office repair/re-install first. Sometimes that fixes obscure problems. Also, are you up-to-date with office updates?
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)
-
January 5th, 2021, 01:17 PM
#12
Re: Excel Automation Headaches
Originally Posted by jim enright
v - thanks for the input.
will look into the other solutions but the compiler issues are caused by the inconsistencies between the mso.dill file and the
excel.exe file. are you saying that finding a compare of compatible files will not solve the problem?
If you will use VS2017 to create the Excel-MFC classes from the type library then you won't need any #import at all, neither mso.dil, nor VBE6EXT.OLB, nor excel.exe.
Only the classes created from the type library!
Victor Nijegorodov
-
January 5th, 2021, 02:15 PM
#13
Re: Excel Automation Headaches
seems to me that vs201y will be a last resort. frankly, this leads to one down the path of
having to own (and pay for) every version of vs. case in point, the article written in 2020
employing vc6.0
trying to be humorous here - maybe ms should concentrate spending less time creating
new string classes and more time on insuring backward compatability by not removing inclusions of typelibs.
was these something developers were screaming for?
Last edited by jim enright; January 5th, 2021 at 02:18 PM.
Reason: add more info
-
January 5th, 2021, 08:32 PM
#14
Re: Excel Automation Headaches
Originally Posted by jim enright
seems to me that vs201y will be a last resort. frankly, this leads to one down the path of
having to own (and pay for) every version of vs. case in point, the article written in 2020
employing vc6.0
trying to be humorous here - maybe ms should concentrate spending less time creating
new string classes and more time on insuring backward compatability by not removing inclusions of typelibs.
was these something developers were screaming for?
Are you aware that since about 2015 or so Microsft has offered free Visual Studio Community Editions?
As far as backward compatibility, it might be too much to ask for Microsoft to keep current with the latest C++ standards and maintain backward compatibility with a 23 year old VC6 compiler.
-
January 6th, 2021, 04:29 PM
#15
Re: Excel Automation Headaches
have download vs2017 and performed the modifications that presumably would allow
mfc dlg based app development. this seems to work. however when i try to follow the article
https://docs.microsoft.com/en-us/pre...m-visual-c-net
i get to the point of adding the type lib i cannot find any typelib start with "Microsoft Excel" as per the article:
select "Microsoft Excel 10.0 Object Library" if you are automating Excel 2002 from Office XP. The default location for Excel 2002 is C:\Program Files\Microsoft Office\Office10\Excel.exe. Or, select "Microsoft Excel 11.0 Object Library" if you are automating Microsoft Office Excel 2003.
tough to do when no seemingly suitable options exist. appreciate any insights.
thx - j
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
|