CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Excel Automation Headaches

  1. #1
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    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

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

    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++20 Compiler: Microsoft VS2019 (16.8.6)

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

    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++20 Compiler: Microsoft VS2019 (16.8.6)

  4. #4
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    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

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

    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++20 Compiler: Microsoft VS2019 (16.8.6)

  6. #6
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    Re: Excel Automation Headaches

    Quote Originally Posted by 2kaud View Post
    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

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

    Re: Excel Automation Headaches

    Quote Originally Posted by jim enright View Post
    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

  8. #8
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    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

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

    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++20 Compiler: Microsoft VS2019 (16.8.6)

  10. #10
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    Re: Excel Automation Headaches

    Quote Originally Posted by 2kaud View Post
    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

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

    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++20 Compiler: Microsoft VS2019 (16.8.6)

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

    Re: Excel Automation Headaches

    Quote Originally Posted by jim enright View Post
    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

  13. #13
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    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

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

    Re: Excel Automation Headaches

    Quote Originally Posted by jim enright View Post
    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.

  15. #15
    Join Date
    Oct 1999
    Location
    ks
    Posts
    517

    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

Page 1 of 2 12 LastLast

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)