C++

Forum

 
Need to select a cell or range in excel, from C++ code

rajcrec (9)
Hello,

I need something in C++, like:

selectCell( excel sheet, cell no. )
selectRange( excel sheet, range )

I need to select a cell or range in C++ automation code, and then copy-paste the same.

Do we have anything like this ?

Regards,
Raj Abhishek
Texan40 (645)
The most daunting task of opening an Office document in C++ is getting past the file formatting. This thread offers some starting points:

http://www.cplusplus.com/forum/general/106507/
freddie1 (1838)
Do it all the time. Here is my XLFunctions.cpp file ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
 
#ifndef   UNICODE
   #define   UNICODE
#endif
   #ifndef   _UNICODE
#define   _UNICODE
#endif
#include <windows.h>
#include <cstdio>
#include "Strings.h"
#include "XLFunctions.h"


String GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, String& strRange)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLRange       = NULL;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 DISPPARAMS      DispParams;
 HRESULT         hr;
 String          strCell;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(strRange.lpStr());
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;
 DispParams.cArgs               = 1;  // Try to get Range
 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
 // [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
 hr=pXLWorksheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return strCell;
 pXLRange=vResult.pdispVal;

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(SUCCEEDED(hr))
 {
    if(vResult.vt==VT_BSTR)
       strCell=vResult.bstrVal;
    else
       strCell=L"";
    VariantClear(&vResult);
 }
 pXLRange->Release();

 return strCell;
}

HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLRange       = NULL;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 DISPPARAMS      DispParams;
 HRESULT         hr;
 String          strCell;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(pszRange);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;
 DispParams.cArgs               = 1;  // Try to get Range
 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
 // [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
 hr=pXLSheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return hr;
 pXLRange=vResult.pdispVal;

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);
 VariantClear(&pVt);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&pVt,NULL,NULL);
 pXLRange->Release();

 return hr;
}

#if 0

// this is mean shit

String strXLCell(IDispatch* pXLWorksheet, LCID& lcid, String& strRange)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLRange       = NULL;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 DISPPARAMS      DispParams;
 HRESULT         hr;
 String          strCell;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(strRange.lpStr());
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;
 DispParams.cArgs               = 1;  // Try to get Range
 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
 // [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
 hr=pXLWorksheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return strCell;
 pXLRange=vResult.pdispVal;  // the above call - if successful, returns S_OK, of course, but vResult.pdispVal should then contain a Range**.

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);  // vArgArray[0] should contain the BSTR containing the input range, e.g., L"A1"; needs to be freed to prevent memory leak!
 // [id(0x00000006), propget, helpcontext(0x00010006)] VARIANT Value();

 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(SUCCEEDED(hr))
 {
    strCell=vResult.bstrVal;  // Assign retrieved BSTR to my String object strCell;
    VariantClear(&vResult);   // Clear/Release returned BSTR in vResult.
 }

 return strCell;
}


[odl, uuid(00020846-0001-0000-C000-000000000046), helpcontext(0x00020006), hidden] interface IRange : IDispatch
{
  ...
  [propget, helpcontext(0x00010006)] HRESULT _stdcall Value([in, lcid] long lcid, [out, retval] VARIANT* RHS);
  [propput, helpcontext(0x00010006)] HRESULT _stdcall Value([in, lcid] long lcid, [in] VARIANT RHS);
  ...
}

[uuid(00020846-0000-0000-C000-000000000046), helpcontext(0x00020006)] dispinterface Range
{
  properties:
  methods:
  ...
  [id(0x00000006), propget, helpcontext(0x00010006)] VARIANT Value();
  [id(0x00000006), propput, helpcontext(0x00010006)] void Value([in] VARIANT rhs);
  ...
}

#endif



IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, String& strSheet)
{
 VARIANT         vResult;
 HRESULT         hr;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 IDispatch*      pXLWorksheet   = NULL;

 // Member Get Item <170> (In Index As Variant<0>) As IDispatch  >> Gets pXLWorksheet
 // [id(0x000000aa), propget, helpcontext(0x000100aa)] IDispatch* Item([in] VARIANT Index);
 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(strSheet.lpStr());
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorksheets->Invoke(0xAA,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return NULL;
 pXLWorksheet=vResult.pdispVal;
 SysFreeString(vArgArray[0].bstrVal);

 // Worksheet::Select()
 VariantInit(&vResult);
 VARIANT varReplace;
 varReplace.vt                  = VT_BOOL;
 varReplace.boolVal             = VARIANT_TRUE;
 dispidNamed                    = 0;
 DispParams.rgvarg              = &varReplace;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 1;
 hr=pXLWorksheet->Invoke(0xEB,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);

 return pXLWorksheet;
}


IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, String& strWorkBookPath)
{
 VARIANT         vResult;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 HRESULT         hr;

 VariantInit(&vResult);         // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(strWorkBookPath.lpStr());
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorkbooks->Invoke(682,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);
 SysFreeString(vArgArray[0].bstrVal);
 if(FAILED(hr))
    return NULL;
 else
    return vResult.pdispVal;
}
freddie1 (1838)
You'll have a hard time using that though, as I have my own String Class, and don't use the one in the C++ Std. Lib. That would be the #include "Strings.h" at top. I'd be happy to post it if you wanted it though. Its just that its a lot of code and would take a number of posts. Anyway, here is the include for the above ...

1
2
3
4
5
6
7
8
9
10
 
// XLFunctions.h
#ifndef XLFunctions_h
#define XLFunctions_h

String GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, String& strRange);
HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt);
IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, String& strSheet);
IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, String& strWorkBookPath);

#endif


There's really a lot to understanding all this COM/OLE stuff. A real lot. A real, real lot.

Here's a long post I contributed to about this last year at this time. It contains some compilable examples...

http://www.cplusplus.com/forum/windows/125996/
Last edited on
freddie1 (1838)
I knew the above wouldn't help because of my private String Class, so I removed references to it and just used wchar_t* pointers. All the code is in one file. To get it to work you need to fix the path to where you have a "Book1.xls" file, and in that Workbook you need a Sheet1 and Sheet2. The program writes to the console the contents of cell "A1" on Sheet2. To get it to link you need to set references to ole32.lib, oleaut32.lib, and uuid.lib. Easy as pie.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
#ifndef   UNICODE
#define   UNICODE
#endif
#ifndef   _UNICODE
#define   _UNICODE
#endif
#include <windows.h>
#include <cstdio>
const CLSID CLSID_XLApplication = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
const IID   IID_Application     = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};


HRESULT GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, wchar_t* pszRange, wchar_t* pszCell, size_t iBufferLength)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLRange       = NULL;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 DISPPARAMS      DispParams;
 HRESULT         hr;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(pszRange);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;
 DispParams.cArgs               = 1;  // Try to get Range
 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
 // [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
 hr=pXLWorksheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return E_FAIL;
 pXLRange=vResult.pdispVal;

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(SUCCEEDED(hr))
 {
    if(vResult.vt==VT_BSTR)
    {
       if(SysStringLen(vResult.bstrVal)<iBufferLength)
       {
          wcscpy(pszCell,vResult.bstrVal);
          return S_OK;
       }
    }
    else
       pszCell[0]=0;
    VariantClear(&vResult);
 }
 pXLRange->Release();

 return E_FAIL;
}


HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLRange       = NULL;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 DISPPARAMS      DispParams;
 HRESULT         hr;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(pszRange);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;
 DispParams.cArgs               = 1;  // Try to get Range
 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
 // [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
 hr=pXLSheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return hr;
 pXLRange=vResult.pdispVal;

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);
 VariantClear(&pVt);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&pVt,NULL,NULL);
 pXLRange->Release();

 return hr;
}


IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, wchar_t* pszSheet)
{
 VARIANT         vResult;
 HRESULT         hr;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 IDispatch*      pXLWorksheet   = NULL;

 // Member Get Item <170> (In Index As Variant<0>) As IDispatch  >> Gets pXLWorksheet
 // [id(0x000000aa), propget, helpcontext(0x000100aa)] IDispatch* Item([in] VARIANT Index);
 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszSheet);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorksheets->Invoke(0xAA,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return NULL;
 pXLWorksheet=vResult.pdispVal;
 SysFreeString(vArgArray[0].bstrVal);

 // Worksheet::Select()
 VariantInit(&vResult);
 VARIANT varReplace;
 varReplace.vt                  = VT_BOOL;
 varReplace.boolVal             = VARIANT_TRUE;
 dispidNamed                    = 0;
 DispParams.rgvarg              = &varReplace;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 1;
 hr=pXLWorksheet->Invoke(0xEB,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);

 return pXLWorksheet;
}


IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, wchar_t* pszWorkBookPath)
{
 VARIANT         vResult;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 HRESULT         hr;

 VariantInit(&vResult);         // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszWorkBookPath);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorkbooks->Invoke(682,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);
 SysFreeString(vArgArray[0].bstrVal);
 if(FAILED(hr))
    return NULL;
 else
    return vResult.pdispVal;
}


int main()
{
 IDispatch* pXLApp              = NULL;
 IDispatch* pXLWorkbooks        = NULL;
 IDispatch* pXLWorkbook         = NULL;
 IDispatch* pXLWorksheets       = NULL;
 IDispatch* pXLWorksheet        = NULL;
 wchar_t    szWorkBookPath[]    = L"C:\\Code\\CodeBlks\\XL_Dispatch\\XL16\\Book1.xls";    // << Fix This For Your Path!!!
 wchar_t    szSheet[]           = L"Sheet2";
 wchar_t    szRange[]           = L"A1";
 wchar_t    szCell[64];
 DISPPARAMS NoArgs              = {NULL,NULL,0,0};
 VARIANT    vResult;
 HRESULT    hr;
 LCID       lcid;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(SUCCEEDED(hr))
 {
    printf("pXLApp        = %p\n",pXLApp);
    lcid=GetUserDefaultLCID();
    VariantInit(&vResult);  //572 Get _Application::Workbooks  >> Gets pXLWorkbooks
    hr=pXLApp->Invoke(572,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
    if(SUCCEEDED(hr))
    {
       pXLWorkbooks=vResult.pdispVal;
       printf("pXLWorkbooks  = %p\n",pXLWorkbooks);
       pXLWorkbook=OpenXLWorkBook(pXLWorkbooks,lcid,szWorkBookPath);
       if(pXLWorkbook)
       {
          printf("pXLWorkbook   = %p\n",pXLWorkbook);

          VariantInit(&vResult);
          hr=pXLWorkbook->Invoke(494,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
          if(SUCCEEDED(hr))
          {
             pXLWorksheets=vResult.pdispVal;
             printf("pXLWorksheets = %p\n",pXLWorksheets);
             pXLWorksheet=SelectWorkSheet(pXLWorksheets,lcid,szSheet);
             if(pXLWorksheet)
             {
                printf("pXLWorksheet  = %p\n",pXLWorksheet);
                GetXLCell(pXLWorksheet,lcid,szRange,szCell,64);
                wprintf(L"szCell = %s\n",szCell);
                pXLWorksheet->Release();
             }
             pXLWorksheets->Release();
          }
          pXLWorkbook->Release();
       }
       pXLWorkbooks->Release();
    }
    pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL); // pXLApp->Quit() 0x12E
    pXLApp->Release();
 }
 CoUninitialize();
 getchar();

 return 0;
}


Here's The Console Output:

1
2
3
4
5
6
 
pXLApp        = 0027ADEC
pXLWorkbooks  = 00278354
pXLWorkbook   = 0027A4CC
pXLWorksheets = 00279294
pXLWorksheet  = 00277F7C
szCell = This Is Sheet #2
Last edited on
freddie1 (1838)
Oh! Almost forgot. For the newer GCC g++ C++ compiler you need to use %S in the printf format specification instead of %s for wide character strings. Not sure why. POSIX nonconformance glitch or something. If you are using one of those compilers (4.7, 4.8, 4.9 series) you'll just get the 1st letter of each string (p's and s's above).
Last edited on
andywestken (4094)
@rajcrec

If...

(...you are wanting to automate an existing Excel installation...)

... and are using Visual Studio, then you could use its Direct-to-COM support to simplify your code (to avoid the explicit Automation (IDispatch, etc) code like freddie1 is using.)

The Direct-to-COM code does have a few quirks, and it does use exceptions for error reporting, but it does cut down on code. You could, of course, write your own equivalent wrappers, but you'll find the Direct-to-COM approach being discussed in assorted forums quite a bit.

To use it you have to #import typelibs -- rather than #include the corresponding header files -- so the compiler will generate the required wrapper classes.

The following code shows Direct-to-COM in actions. The code is a bit loose and long winded, but I wanted to spell out what you need to do.

You do need to have a basic grasp of COM, inc. the way Automation works, so if it's not something you already have I'd read up on the subject.

And if you check out the Microsoft documention, a fair amount of the examples seem to be written in VBA which knows how to take short cuts which aren't available to C++ programmers. :-(

The following example shows how to:
- dump the contents of a rectangular block of cell
- copy a range
- replace a single value

Andy

PART 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
// Excel automation test

#define WINVER  0x0501

// import required typelibs (all the renaming and excluding is to stop name collisions)

#import <C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL> no_namespace \
rename("DocumentProperties", "DocumentPropertiesXL") rename("RGB", "MSO_RBG")

#import <C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb> no_namespace

#import <C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE> no_dual_interfaces \
rename("DialogBox", "DialogBoxXL") rename("RGB", "Excel_RBG") \
rename("DocumentProperties", "Excel_DocumentProperties") \
rename("CopyFile", "Excel_CopyFile") rename("ReplaceText", "Excel_ReplaceText") \
exclude("IFont") exclude("IPicture")

#include <iostream>
#include <iomanip>
using namespace std;

// incomplete insertion operator for _variant_t (enough for this test)
std::wostream& operator<<(std::wostream& wos, const _variant_t& var)
{
    switch(var.vt & ~VT_TYPEMASK)
    {
        case 0:
        {
            switch(var.vt)
            {
                case VT_BSTR:
                {
                    _bstr_t str = (_bstr_t)var;
                    const wchar_t* psz = (const wchar_t*)str;
                    if(NULL == psz)
                        wos << L"<null>";
                    else
                        wos << L"\"" << psz << L"\"";
                }
                break;
                case VT_R8:
                {
                    double dbl = (double)var;
                    wos << dbl;
                }
                break;
                case VT_DATE:
                {
                    SYSTEMTIME sysTime = {0};
                    VariantTimeToSystemTime(var.date, &sysTime);
                    wchar_t buffer[256] = {0}; // blatantly assume buffer size...
                    GetDateFormat(LOCALE_USER_DEFAULT, DATE_SHORTDATE, &sysTime, NULL, buffer, _countof(buffer));
                    wos << buffer;
                }
                break;
                default:
                {
                    wos << L"<???>";
                }
            }
        }
        break;
        case VT_ARRAY:
        {
            wos << L"<array>";
        }
        break;
        default:
        {
            wos << L"<???>";
        }
    }
    return wos;
}

void TestExcel();

void DumpValueRectTest(Excel::_WorksheetPtr ptrSheet);
void CopyRangeTest(Excel::_WorksheetPtr ptrSheet);
void ChangeValueTest(Excel::_WorksheetPtr ptrSheet);

void ReportError(const _com_error& err);

int wmain(int argc, wchar_t* argv[])
{
    HRESULT hr = CoInitialize(NULL);

    if(SUCCEEDED(hr))
    {
        TestExcel();

        CoUninitialize();
    }

    return 0;
}

void TestExcel()
{
    using namespace Excel;

    _ApplicationPtr ptrExcel;

    try
    {
        wcout << L"Launch Excel...\n";
        HRESULT hr = ptrExcel.CreateInstance(L"Excel.Application.11");
        if(FAILED(hr))
            _com_raise_error(hr);
        // _com_ptr_t<>::CreateInstance() does not raise exception itself

        wcout << L"Set visible\n";
        ptrExcel->Visible = VARIANT_TRUE;

        const wchar_t filePath[] = L"C:\\Test\\Excel\\Test.xls";

        wcout << L"Open " << filePath << L"\n";
        WorkbooksPtr ptrBooks = ptrExcel->Workbooks;
        _WorkbookPtr ptrBook = ptrBooks->Open(filePath);

        Sleep(1000); // slow the test down so can see what happens

        const wchar_t sheetName[] = L"TheBasics";
        wcout << L"Get \"" << sheetName << L"\" sheet\n";
        SheetsPtr ptrSheets = ptrBook->Sheets;
        _WorksheetPtr ptrSheet  = ptrSheets->Item[sheetName];

        wcout << L"\n";

        Sleep(1000); // slow the test down so can see what happens

        DumpValueRectTest(ptrSheet);

        wcout << L"\n";

        Sleep(1000); // slow the test down so can see what happens

        CopyRangeTest(ptrSheet);

        wcout << L"\n";

        Sleep(1000); // slow the test down so can see what happens

        ChangeValueTest(ptrSheet);

        wcout << L"\n";

        Sleep(1000); // slow the test down so can see what happens

        wcout << L"Save\n";
        ptrBook->Save();

        wcout << L"Quit\n";
        ptrExcel->Quit();
    }
    catch(const _com_error& err)
    {
        ReportError(err);
    }
}
Last edited on
andywestken (4094)
PART 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
void DumpValueRectTest(Excel::_WorksheetPtr ptrSheet)
{
    using namespace Excel;

    const wchar_t rangeSpec[] = L"A2:C5";

    wcout << L"Get range [" << rangeSpec << L"]\n";
    RangePtr ptrRange = ptrSheet->Range[rangeSpec];

    long cols = ptrRange->Columns->Count;
    long rows = ptrRange->Rows->Count;

    for(long c = 1; c <= cols; ++c)
    {
        for(long r = 1; r <= rows; ++r)
        {
            _variant_t varElem = ptrRange->Item[r][c];
            RangePtr ptrRangeElem = varElem.pdispVal;
            _variant_t varTemp = ptrRangeElem->Value;
            // this output line is not safe for real code!
            // indices into a range are relative and 1-based, so
            // need to calc values starting from A2
            wcout << L"[" << (wchar_t)(L'A' + c - 1) << (2 + r - 1) << L"] = " << varTemp << L"\n";
        }
    }
}

void CopyRangeTest(Excel::_WorksheetPtr ptrSheet)
{
    using namespace Excel;

    const wchar_t rangeSpecFrom[] = L"B2:C2";
    const wchar_t rangeSpecTo  [] = L"B7:C7";

    wcout << L"Get range from [" << rangeSpecFrom << L"]\n";
    RangePtr ptrRangeFrom = ptrSheet->Range[rangeSpecFrom];

    long cols = ptrRangeFrom->Columns->Count;
    long rows = ptrRangeFrom->Rows->Count;

    for(long c = 1; c <= cols; ++c)
    {
        for(long r = 1; r <= rows; ++r)
        {
            _variant_t varElem = ptrRangeFrom->Item[r][c];
            RangePtr ptrRangeElem = varElem.pdispVal;
            _variant_t varTemp = ptrRangeElem->Value;
            // this output line is not safe for real code!
            // indices into a range are relative and 1-based, so
            // need to calc values starting from B2
            wcout << L"[" << (wchar_t)(L'B' + c - 1) << (2 + r - 1) << L"] = " << varTemp << L"\n";
        }
    }

    wcout << L"Get range to [" << rangeSpecTo << L"]\n";
    RangePtr ptrRangeTo = ptrSheet->Range[rangeSpecTo];

    _variant_t varRangeTo;
    varRangeTo.pdispVal = ptrRangeTo.Detach(); // varRangeTo now owns interface
    varRangeTo.vt       = VT_DISPATCH;

    wcout << L"Copy range\n";
    ptrRangeFrom->Copy(varRangeTo);
}

void ChangeValueTest(Excel::_WorksheetPtr ptrSheet)
{
    using namespace Excel;

    const wchar_t rangeSpec[] = L"C2:C2";

    wcout << L"Get range [" << rangeSpec << L"]\n";
    RangePtr ptrRange = ptrSheet->Range[rangeSpec];

    wcout << L"Get old value\n";
    _variant_t varOldValue = ptrRange->Value;
    wcout << L"value = " << varOldValue << L"\n";

    const wchar_t englishValue[] = L"Hello world!";
    const wchar_t frenchValue [] = L"Bonjour le monde !";
    const wchar_t* newValue = englishValue;
    if(VT_BSTR == varOldValue.vt)
    {
        _bstr_t bstr = (_bstr_t)varOldValue;
        const wchar_t* psz = (const wchar_t*)bstr;
        if((NULL != psz) && (0 != wcscmp(psz, frenchValue)))
            newValue = frenchValue;
    }

    wcout << L"Set value to \"" << newValue << L"\"\n";
    _bstr_t bstrNewValue = newValue;
    _variant_t varNewValue = bstrNewValue;
    ptrRange->Item[1][1] = varNewValue;
}

void ReportError(const _com_error& err)
{
    _bstr_t bstrSource(err.Source());
    _bstr_t bstrDescription(err.Description());

    wcerr << L"_com_error:\n";
    wcerr << L"  Error code    : 0x" << std::hex << err.Error() << std::dec << L"\n";
    wcerr << L"  Error message : "   << err.ErrorMessage() << L"\n";
    if(NULL != (LPCWSTR)bstrSource)
    {
        wcerr << L"  Source        : "   << (LPCWSTR)bstrSource << L"\n";
    }
}


NOTES

- I've built the code against the Microsoft Office 11 interfaces (aka Microsoft Office 2003) as that's the version I've still got on my home computer.
- there are hardcoded paths you'll have to replace
- I've used using std to make it easier to read here. Not something I normally do...
- Sleep() is used to slow the program down so you can see things happening
- and Excel is made visible for the same reason
- the code is all Unicode (well, wchar_t) as this is what COM uses itself.
- etc
Last edited on
andywestken (4094)
Test.xls has a worksheet called "TheBasics" with these contents:

Index  Type        Value               Comments
1      string      Hello world!
2      number      123
3      date        24/10/1964
4      percentage  80.00%
5      currency    $1000000
6      string      Bonjour le monde !  Copy


And the resultant output is:

Launch Excel...
Set visible
Open C:\Test\Excel\Test.xls
Get "TheBasics" sheet

Get range [A2:C5]
[A2] = 1
[A3] = 2
[A4] = 3
[A5] = 4
[B2] = "string"
[B3] = "number"
[B4] = "date"
[B5] = "percentage"
[C2] = "Bonjour le monde !"
[C3] = 123
[C4] = 24/10/1964
[C5] = 0.8

Get range from [B2:C2]
[B2] = "string"
[C2] = "Bonjour le monde !"
Get range to [B7:C7]
Copy range

Get range [C2:C2]
Get old value
value = "Bonjour le monde !"
Set value to "Hello world!"

Save
Quit

freddie1 (1838)
Hi Andy!

I got your code to compile! I ran into one small glitch. I have an even older version of Office installed on my home computer than yours I guess – Office 2000, and I got an error on the line where you #import EXCEL9.EXE. When I looked in my applicable directory and made sure everything was spelled correctly and my paths were all right, I noticed that in this version of Office there was a separate Excel.olb file, which I’m asumming is the compiled type library resource, that is, what I usually see as a *.tlb file, and when I changed that line I got it to work. I have Visual Studio 2008 Professional. Here are the changes I made to your code at top…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
#define WINVER  0x0501

// import required typelibs (all the renaming and excluding is to stop name collisions)

#import <C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL> no_namespace \
rename("DocumentProperties", "DocumentPropertiesXL") rename("RGB", "MSO_RBG")

#import <C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb> no_namespace

#import <C:\Program Files\Microsoft Office\Office\EXCEL9.OLB> no_dual_interfaces \
rename("DialogBox", "DialogBoxXL") rename("RGB", "Excel_RBG") \
rename("DocumentProperties", "Excel_DocumentProperties") \
rename("CopyFile", "Excel_CopyFile") rename("ReplaceText", "Excel_ReplaceText") \
exclude("IFont") exclude("IPicture")


One other line I changed was this…

 
 
HRESULT hr = ptrExcel.CreateInstance(L"Excel.Application");


Note I removed the version specific Program ID identifier and used the Version Independent Program ID instead.

Our original poster [OP] is gone. Its just you and me left now. I’m sure I’m responsible for that. I scarred him away, and he won’t be coming back any time soon. I’m sure he looked at my code, decided he didn’t want to devote the next X number of years of his life studying OLE/COM, and decided to do something else. Too bad. Maybe your code would have been more palatable to him.

But for myself, I prefer doing it my way. Of course, I’ve invested something like 18 years of my life into COM, but for others who haven’t done that, your way might be better, especially if they have, like you said, Visual Studio.

What I did do however, was make an attempt to comment and explain my code, so that anyone out there who may be interested in it, unlikely as that may be, might have a chance of understanding it. Also, in my haste to post that code the other day, I created a bug. The original with my String Class was OK, but when I converted it over to wchar_t I created a little memory leak. I fixed that. Here's the beginning of the updated version with comments and explanations. I'll need several posts to get it all in...

Here is my updated code with some comments and explanations. To run this code just make a blank Excel Book1.xls file, and type “Hello, World!” into cell “A1” of “Sheet2” …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
// g++ Main.cpp -oXL2.exe -m32 -lole32 -loleaut32 -luuid -Os -s
// cl Main.cpp kernel32.lib ole32.lib oleaut32.lib uuid.lib /O1 /Os /MT /FeXL2.exe
#ifndef   UNICODE
    #define   UNICODE
#endif
#ifndef   _UNICODE
    #define   _UNICODE
#endif
#include <windows.h>
#include <cstdio>

HRESULT SetVisible(IDispatch* pObject, LCID lcid) // Visible is one of the members of the _Application object ...
{                                                 //
 VARIANT         vArgArray[1];                    // [odl, uuid(000208D5-0000-0000-C000-000000000046), helpcontext(0x00020001), dual, oleautomation] interface _Application : IDispatch
 DISPPARAMS      DispParams;                      // {
 DISPID          dispidNamed;                     //   ...
 VARIANT         vResult;                         //   [id(0x0000022e), propget, helpcontext(0x0001022e)] HRESULT Visible([in, lcid] long lcid, [out, retval] VARIANT_BOOL* RHS);
 HRESULT         hr;                              //   [id(0x0000022e), propput, helpcontext(0x0001022e)] HRESULT Visible([in, lcid] long lcid, [in] VARIANT_BOOL RHS);
                                                  // };
 VariantInit(&vArgArray[0]);                      //
 vArgArray[0].vt               = VT_BOOL;         // This information can be obtained by dumping the object's typelib with OleView.exe.  You'll note the hexidicimal number 0x22e in
 vArgArray[0].boolVal          = TRUE;                 // both the propget and propput Visible _Application::Visible() members.  That number is a dispatch id or dispid for short.
 dispidNamed                   = DISPID_PROPERTYPUT;   // Since here we want to set the property to TRUE we use the DISPATCH_PROPERTYPUT as the wFlags parameter of the
 DispParams.rgvarg             = vArgArray;            // IDispatch::Invoke() call. Note above that the Excel Application Object is listed as being a dual interface.  The unfortunate
 DispParams.rgdispidNamedArgs  = &dispidNamed;    // fact of the matter is that the direct VTable part of the dual interface is largely nonfunctional in all the MS Office applications
 DispParams.cArgs              = 1;               // of which I'm familiar, specifically Word and Excel.  So this horrendous IDispatch access is forced upon us.  Its only redeeming
 DispParams.cNamedArgs         = 1;               // feature is that it does work in spite of the fact that it is horribly awkward.
 VariantInit(&vResult);
 hr=pObject->Invoke(0x0000022e,IID_NULL,lcid,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);

 return hr;
}


freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
HRESULT GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, wchar_t* pszRange, wchar_t* pszCell, size_t iBufferLength)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};     // With IDispatch all of the parameters found in the dumped *.idl code (OleView.exe can dump the original IDL (Interface
 IDispatch*      pXLRange       = NULL;                // Definition Language) which forms the basis of the COM Object) must be passed into the IDispatch::Invoke() call through
 VARIANT         vArgArray[1];                         // an array of VARIANT objects.  That's what DISPARAMS is - an array of VARIANT Objects.  Believe it or not - they get
 VARIANT         vResult;                              // loaded in reverse order.  It really just keeps getting worse without mercy or limit.  Before getting any further, why
 DISPPARAMS      DispParams;                           // don't you just get yourself a copy of VBA, .NET, or PowerBASIC and save yourself this torture???  This procedure is only
 HRESULT         hr;                                   // useful if you know that the contents of a cell you are trying to retrieve is a String.  And by a string, I mean a BSTR.
                                                       // The BSTR type is the OLE/COM String Type.  Its close to a wchar_t but a little bit more.  The length of the string is
 VariantInit(&vResult);                                // prefaced to a size_t sized memory block right before the start of the string.  And the string can contain NULLs.  Like
 vArgArray[0].vt                = VT_BSTR,             // I said, it'll only get worse - without limit.
 vArgArray[0].bstrVal           = SysAllocString(pszRange);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;          // Here is what the _Worksheet object looks like  with regard to its Range() member (from an OleView.exe IDL dump)...
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;          // [odl, uuid(000208D8-0000-0000-C000-000000000046), helpcontext(0x00020010), dual, nonextensible, oleautomation] interface _Worksheet : IDispatch
 hr=pXLWorksheet->Invoke                      // {
 (                                            //  ....
  0xC5,                                       //  [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
  IID_NULL,                                   //  ....
  lcid,                                       // };
  DISPATCH_PROPERTYGET,                       //
  &DispParams,                                // Why are we doing this?  Maybe I should back up.  To get data out of a cell you 1st have to open an Excel object.  That gives you an App
  &vResult,                                   // object in IDispatch* form.  Using that app object you get an IDispatch* to the App Object's WorkBooks Collection.  You then use that Work-
  NULL,                                       // books collection pointer to get a pointer to a WorkBook object, also in IDispatch* form.  With that pointer you can finally call the 'Open()'
  NULL                                        // Method of the WorkBook Object to open a specific *.xls file.  Once you get an IDispatch* to the opened Workbook you can query that interface
 );                                           // for a pointer (an IDispatch one, naturally) to the Workbook's WorkSheets Collection.  And with that pointer you open a specific Worksheet.
 if(FAILED(hr))                               // That brings us up to my code blurb above.  One of the members of the _Worksheet object is Range, and that would be an Excel Cell such as "A1",
    return E_FAIL;                            // "A2", etc.  You need to first set the range before you can extract its contents.  And the dispid (Dispatch ID) of the Range property
 pXLRange=vResult.pdispVal;                   // DISPATCH_PROPERTYGET call is 0xC5.  That pXLWorksheet->Invoke(0xC5) call will return an HRESULT which can be checked for SUCCESS, and in
                                              // that positive case an IDispatch* to an Excel Range object will be returned....
 //Member Get Value <6> () As Variant         //
 VariantClear(&vArgArray[0]);                 // [uuid(00020846-0000-0000-C000-000000000046),helpcontext(0x00020006)] dispinterface Range
 hr=pXLRange->Invoke                          // {
 (                                            //  ...
  6,                                          //  [id(0x00000006), propget, helpcontext(0x00010006)] VARIANT Value();
  IID_NULL,                                   //  [id(0x00000006), propput, helpcontext(0x00010006)] void Value([in] VARIANT rhs);
  lcid,                                       // };
  DISPATCH_PROPERTYGET,                       //
  &NoArgs,                                    // As you can see above, the Range dispinterface contains, among many others, getters and setters (mutators in OOP speak) for the 'Value'
  &vResult,                                   // contained within the cell.  You can see we're making this next IDispatch::Invoke() call just left using the IDispatch* to a Range object
  NULL,                                       // we've just received.  Now to continue with the brutality of all this, the reason I said this function ( GetXLCell() ) is only good for
  NULL                                        // retrieving BSTRs, is that just below left you see I'm testing the VARIANT::vt member of type VARTYPE to see what type of variable the VARIANT
 );                                           // contains.  A VARIANT essentially is comprised of a VARTYPE such as VT_BSTR for a BSTR, VT_I4 for a 32 bit integer, etc., and an unnamed union
 if(SUCCEEDED(hr))                            // which contains the actual value.  The pain really is without limit, like I said.  So in the case left I test the [out] VARIANT parameter of
 {                                            // the Invoke() call which would be the 6th parameter - vResult, to see if it contains a BSTR.  If it does, and if the character count returned
    if(vResult.vt==VT_BSTR)                   // by SysStringLen() is less than iBufferLength passed into this function, I use wcscpy() from the C Runtime to copy the characters to the buffer
    {                                                    // passed into this function in pszCell. Then I return S_OK.  Note all the other possibilities I return E_FAIL.  Note that memory leaks
       if(SysStringLen(vResult.bstrVal)<iBufferLength)   // are an ever present problem with all of this. Realize that when a BSTR is returned to you from the operating system, a memory
       {                                                 // allocation was performed to give you that BSTR, and you are responsible for freeing that memory.  There is a BSTR function for that
          wcscpy(pszCell,vResult.bstrVal);               // named SysFreeString(), but in this context here calls to VariantClear() will see that the contents of the VARIANT are released if
          VariantClear(&vResult);             // necessary.  Also note I did an explicit IDispatch::Release() call on the pXLRange object.
          return S_OK;
       }
       else
       {
          VariantClear(&vResult);
          return E_FAIL;
       }
    }
    else
    {
       pszCell[0]=0;
       VariantClear(&vResult);
    }
 }
 pXLRange->Release();

 return E_FAIL;
}

freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};           // Now this function here - 'GetCell()', is more general purpose than my 'GetXLCell()' above.  Use this
 IDispatch*      pXLRange       = NULL;                      // one when you aren't sure what type of value is contained within a cell, or, stated even more precisely,
 VARIANT         vArgArray[1];                               // how Excel itself is internally handling the value contained within a cell.  For example, if you type
 VARIANT         vResult;                                    // 123456 in a cell, Excel the miserable but intelligent beaste will recognize that as a number, and when
 DISPPARAMS      DispParams;                                 // the cell contents is returned in a VARIANT, the VARIANT::vt member will likely be VT_I4 or VT_R8.
 HRESULT         hr;                                         // However, if the user or creator of the Excel SpreadSheet's data explicitely typed the cell as a 'Text'
                                                             // cell, (Right Click On A Cell And Choose 'Format Cells') a VT_BSTR will be returned in the VARIANT.  So
 VariantInit(&vResult);                                      // in other words, you can't just look at the data in an Excel spreadsheet and determine what the data
 vArgArray[0].vt                = VT_BSTR,                   // type of the data is.  You had better reread that last sentence and think long and hard about it.  But
 vArgArray[0].bstrVal           = SysAllocString(pszRange);  // this function begins exactly like the one above, but when it gets the IDispatch* to the Excel Range,
 DispParams.rgvarg              = vArgArray;                 // it simply makes the IDispatch::Invoke() call to obtain the contents of the cell, and returns that
 DispParams.rgdispidNamedArgs   = 0;                         // contents to the caller in the [out] parameter VARIANT& pVt.  The caller then has the responsibility
 DispParams.cArgs               = 1;                         // for determining what the VARIANT contains, e.g, a number, a string, a date, an interface pointer, etc.,
 DispParams.cNamedArgs          = 0;                         // and for freeing that memory if necessary.
 hr=pXLSheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))VariantClear(&vResult);
    return hr;
 pXLRange=vResult.pdispVal;

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);
 VariantClear(&pVt);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&pVt,NULL,NULL);
 pXLRange->Release();

 return hr;
}


IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, wchar_t* pszSheet)
{
 VARIANT         vResult;
 HRESULT         hr;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 IDispatch*      pXLWorksheet   = NULL;

 // Member Get Item <170> (In Index As Variant<0>) As IDispatch  >> Gets pXLWorksheet
 // [id(0x000000aa), propget, helpcontext(0x000100aa)] IDispatch* Item([in] VARIANT Index);
 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszSheet);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorksheets->Invoke(0xAA,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return NULL;
 pXLWorksheet=vResult.pdispVal;
 SysFreeString(vArgArray[0].bstrVal);

 // Worksheet::Select()
 VariantInit(&vResult);
 VARIANT varReplace;
 varReplace.vt                  = VT_BOOL;
 varReplace.boolVal             = VARIANT_TRUE;
 dispidNamed                    = 0;
 DispParams.rgvarg              = &varReplace;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 1;
 hr=pXLWorksheet->Invoke(0xEB,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);

 return pXLWorksheet;
}


IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, wchar_t* pszWorkBookPath)
{
 VARIANT         vResult;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 HRESULT         hr;

 VariantInit(&vResult);         // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszWorkBookPath);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorkbooks->Invoke(682,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);
 SysFreeString(vArgArray[0].bstrVal);
 if(FAILED(hr))
    return NULL;
 else
    return vResult.pdispVal;
}

IDispatch* GetDispatchObject(IDispatch* pCallerObject, DISPID dispid, WORD wFlags, LCID lcid)
{
 DISPPARAMS   NoArgs     = {NULL,NULL,0,0};
 VARIANT      vResult;
 HRESULT      hr;

 VariantInit(&vResult);
 hr=pCallerObject->Invoke(dispid,IID_NULL,lcid,wFlags,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    return NULL;
 else
    return vResult.pdispVal;
}


int main()
{
 const CLSID  CLSID_XLApplication = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 const IID    IID_Application     = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 IDispatch*   pXLApp              = NULL;
 IDispatch*   pXLWorkbooks        = NULL;
 IDispatch*   pXLWorkbook         = NULL;
 IDispatch*   pXLWorksheets       = NULL;
 IDispatch*   pXLWorksheet        = NULL;
 DISPPARAMS   NoArgs              = {NULL,NULL,0,0};
 wchar_t      szWorkBookPath[]    = L"C:\\Code\\VStudio\\VC++9\\XL_Dispatch\\XL2\\Book1.xls"; // << Fix This For Your Path!!!
 wchar_t      szSheet[]           = L"Sheet2";
 wchar_t      szRange[]           = L"A1";
 wchar_t      szCell[64];
 HRESULT      hr;
 LCID         lcid;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(SUCCEEDED(hr))
 {
    lcid=GetUserDefaultLCID();
    SetVisible(pXLApp,lcid);
    pXLWorkbooks=GetDispatchObject(pXLApp,572,DISPATCH_PROPERTYGET,lcid);
    if(pXLWorkbooks)
    {
       pXLWorkbook=OpenXLWorkBook(pXLWorkbooks,lcid,szWorkBookPath);
       if(pXLWorkbook)
       {
          pXLWorksheets=GetDispatchObject(pXLWorkbook,494,DISPATCH_PROPERTYGET,lcid);
          if(pXLWorksheets)
          {
             pXLWorksheet=SelectWorkSheet(pXLWorksheets,lcid,szSheet);
             if(pXLWorksheet)
             {
                GetXLCell(pXLWorksheet,lcid,szRange,szCell,64);
                wprintf(L"szCell = %s\n",szCell);
                pXLWorksheet->Release();
             }
             pXLWorksheets->Release();
          }
          pXLWorkbook->Release();
       }
       pXLWorkbooks->Release();
    }
    getchar();
    pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL); // pXLApp->Quit() 0x12E
    pXLApp->Release();
 }
 CoUninitialize();

 return 0;
}


I have the command line compilation strings for GCC g++ and MS cl listed at top. Compiles to 52736 bytes with VC9 compiler (version 15 I think – same as in Windows 7 SDK download), and to 40960 bytes with TDM-GCC 4.8 series x64. All the newer compilers bloat binaries aweful, but with the 4.4 series Mingw it compiles to just 8k! All these numbers are for static linking (/MT) with the C Runtime. Interestingly, you can compile this under x64 and get it to work just fine with a 32 bit installation of Office. I suppose since the data has to be marshalled across process boundaries with out of process exe servers the OS can manage it somehow. As I expect you know, x64 code won’t work too well with 32 bit Dlls.
Last edited on
andywestken (4094)
I have to admit your code does look very "old school" to me -- clear but rather verbose. Are you adverse to using classes (you own if not than the Direct-to-COM wrappers.)

But it doesn't seem that you've totally scared rajcrec as he's still asking related questions!

Andy
freddie1 (1838)
Not sure what you mean by my code being verbose. I think of the word verbose as meaning overly wordy. Yet when I examine my Visual Studio Project with your #import directive I come up with 214,954 lines of code in your main source I’ve named Main.cpp, plus these Visual Studio generated application specific includes and source…

1
2
3
4
5
6
 
excel9.tlh   3,151 KB  TypeLib Generated C/C++ Header        70,513 lines of code
excel9.tli   3,237 KB  TypeLib Generated C/C++ Inline File   89,940 lines of code
mso.tlh      1,089 KB  TypeLib Generated C/C++ Header        26,627 lines of code
mso.tli        805 KB  TypeLib Generated C/C++ Inline File   23,906 lines of code
vbe6ext.tlh     65 KB  TypeLib Generated C/C++ Header         2,042 lines of code
vbe6ext.tli     55 KB  TypeLib Generated C/C++ Inline File    1,655 lines of code


…which compiles to a 148 KB Release executable using /O1 /Os /MT linking statically.

On the other hand, my single Main.cpp file contains only 269 lines of code and compiles to anywhere from 8 KB to 52 KB compiled with the above options depending on which Microsoft or GCC compiler suite I use. So I’m at about 1/3 the executable size of yours and 0.125% of the Lines of Code, hense my confusion over your use of the word verbose.


Are you adverse to using classes...?


Yes, but I use them anyway. Classes are an encapsulation mechanism. The benefits are hiding the nasty details of achieving some functionality. The costs are the code bloat of the wrapping/encapsulation mechanism which creates an additional layer of abstraction. Further, the encapsulation/wrapping mechanism adds nothing to the underlying functionality of the code being wrapped. Its simply a convenience mechnism. Having said that, I use classes heavily in my C++ coding; its just that my Excel example above just happens not to contain any examples of that. I do fully realize that classes and OOP are holy grail to C++ purists.

For the work I do I require a good String Class and the one in the C++ Standard Library is too bloated to meet my needs so I wrote my own. I also require flexible dynamic multi-dimensional array capability able to handle up to four dimensions so I constructed a template based class implementation after the BASIC model for that where I’ve overloaded the ‘(‘ and ‘)’ operators. And for my database access I use my own ODBC class. So I wouldn’t consider myself adverse to using classes. I much prefer the C++ language to the C language, but I do still use a lot of C ‘isms’ in my code, as I believe you may have noted. And I‘m also committed to the OOP paradigm, although my adversion to code bloat often requires me to use C ‘isms’ to achieve it rather than C++ classes. Its for that reason I’m a big user or the raw Windows Api in my coding which is C based OOP code.

But the difference between my techniques and yours do raise some interesting philosophical issues. To get at those let’s first define the concepts of ‘Black Box Code’ and ‘Grey Box Code’. I’d define ‘Black Box Code’ as binary components, e.g., a COM object or just plain lib or .obj file. You can build applications out of these that way. You generally do need a textural header though.

‘Grey Box Code’ can be stuff like that generated by the #import directive, wizard generated code, visual designer generated code, *.h and *.cpp files one gets from somewhere or somebody, Class Framework code, etc; its becomes part of one’s application’s source. You can read it. And if something breaks there you are going to have to.

I always shy away from grey box code. I’ve used it already but always wish I didn’t have to. If it contains functionality that’s ‘over my head’ to understand but I absolutely need to have it I’ll usually spend whatever time is necessary to understand it when I have time available. And in that way ‘make it my own’, so to speak.

This issue with OLE Automation and Office Applications is a good example of what I’m talking about. COM being an advanced topic and hard to learn for beginners and even intermediate programmers is something Microsoft always understood. First they had to get it to work themselves, then take advantage of its potentialities in their Office Suite of applications – particularly Excel and Word, but others too. For C and C++ coders there was always the ‘hard core’ low level way of doing it like I did, and for those C or C++ coders who were looking for something easier but who wished to not use VB/VBA or whatever they provided things like #import, ATL, MFC, etc.

But personally, I have qualms about all that. C and C++ are low level systems languages. They tend to be used by CS and IT professionals. Why bother if you aren’t going to write low level code? I know this is something of an overstatement, and I can’t really come up with a better way of stating it due to my linguistic limitations, but it almost seems intellectually dishonest to me to fill up one’s source with this ‘grey box’ type code as I’ve here defined it. And what would I define as an ‘intellectually honest’ alternative? Other than doing what I did? Glad you asked! Here’s some of your output Andy from another program I wrote whose executable is 29K statically linked (no runtimes needed) and this program comprises only 63 lines of code… 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Launch Excel
Make Visible
Open C:\Code\PwrBasic\PBCC6\ExcelAuto\Test.xls
Get TheBasics Sheet

[A2] =  1
[A3] =  2
[A4] =  3
[A5] =  4
[B2] = string
[B3] = number
[B4] = date
[B5] = percentage
[C2] = Bonjour le monde !
[C3] = 123.0
[C4] = 10/24/1964
[C5] = 0.8


And here is the entirety of the source code that created that 29 K executable …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
#Compile Exe
#Dim All
#Include "Windows.inc"

Function PBMain() As Long
  Local pDisp, pWorkBook, pWorkSheet As IDispatch
  Local vVnt1,vVnt2 As Variant
  Local strRange As WString
  Local st As SystemTime
  Register i As Long

  Console.Print "Launch Excel"
  pDisp = NewCom "Excel.Application"
  Console.Print "Make Visible"
  vVnt1=1
  Object Let pDisp.Visible=vVnt1
  Con.Print "Open " & "C:\Code\PwrBasic\PBCC6\ExcelAuto\Test.xls"
  vVnt1="C:\Code\PwrBasic\PBCC6\ExcelAuto\Test.xls"
  Object Call pDisp.WorkBooks.Open(vVnt1) To pWorkBook
  Print "Get TheBasics Sheet"
  Print
  vVnt1="TheBasics"
  Object Call pWorkbook.Sheets(vVnt1).Select
  Object Get pWorkbook.ActiveSheet To vVnt2
  Set pWorkSheet = vVnt2

  ' get 1st column
  For i=2 To 5
    strRange = "A" & Trim$(Str$(i))
    vVnt1=strRange
    Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
    Con.Print "[A" + Trim$(Str$(i)) & "] = " & Str$(Variant#(vVnt2))
  Next i

  'get 2nd column
  For i=2 To 5
    strRange = "B" & Trim$(Str$(i))
    vVnt1=strRange
    Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
    Con.Print "[B" + Trim$(Str$(i)) & "] = " & Variant$(vVnt2)
  Next i

  'get 3rd column
  For i=2 To 5
    strRange = "C" & Trim$(Str$(i))
    vVnt1=strRange
    Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
    Select Case As Long VariantVt(vVnt2)
      Case %VT_R8
        Print "[C" + Trim$(Str$(i)) & "] = " & Format$(Variant#(vVnt2),"##0.0")
      Case %VT_DATE
        VariantTimeToSystemTime(Variant#(vVnt2),st)
        Print "[C" + Trim$(Str$(i)) & "] = " & Trim$(Str$(st.wMonth)) + "/" & Trim$(Str$(st.wDay)) + "/" & Trim$(Str$(st.wYear))
      Case %VT_BSTR
        Print "[C" + Trim$(Str$(i)) & "] = " & Variant$(vVnt2)
    End Select
  Next i
  Con.WaitKey$
  Object Call pWorkbook.Close()
  Object Call pDisp.Quit()

  PBMain=0
End Function

freddie1 (1838)
I’m guessing you never saw any C++ like that! Its PowerBASIC code. Note that I wouldn’t have even needed that 3rd line above - #Include “Windows.inc” except you used a date in your Excel data and I needed to have VariantTimeToSystemTime() declared for that, and you used it too. Now that code is what I’d term ‘intellectually honest’. There isn’t any ‘grey box’ auto-generated code involved. Not one line of it. When I wrote that code I created a directoy where I keep my PowerBASIC code named “ExcelAuto”. In that folder after the compile were two files – a 29 K executable and a 2K source code file. About as clean and simple as it gets – BASIC in fact, and totally ‘honest’.

But you are probably thinking that the program’s a dog since its written in a dialect of BASIC? That its not as fast as C++. You would be right about that. That particular program is pure late binding IDispatch. Yours using #import would have been faster because the #import keyword caused all those *.tlh and *.tli files to be generated at compile time, which contained the Dispatch Ids from each and every method call. So therefore, when a method call was made, the code didn’t have to query the interface for the dispid of the call, then make the call. The dispid was known at compile time. In that PowerBASIC program above the compiler had no knowledge beforehand of how to make those calls. As you can see and as I’ve described, there are no additional files. First the compiler would have generated code to call IDispatch::GetIDsOfNames() to get the dispid – and that’s a slow text search, followed by an IDispatch::Invoke() call. Two trips to the server for each call in the client; the first a slow text search in the server, and the second involving arrays of VARIANTs, and all this marshalled across process boundaries. Nope. Its not fast, relatively speaking, but honest it is.

However, the PowerBASIC language does do ‘early binding’ like your #import example does. There is a menu selection in the IDE where one can bring up the ‘OLE/COM Browser, which is an OleView.exe type thing which lists Type Libraries found in the Windows Registry. You double click on a desired Type Library and the utility reads the TypeLib and auto-generates includes something like your #import does. However, they are much more readable than those produced by #import. And the resulting executable in that case would be every bit as fast as a C++ one. I might as well show what that program looks like. Note below everything doesn’t have to be defined as IDispatch, but the names of the actual Interfaces can be used…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
#Compile Exe
#Dim All
#Include "PBExcel.inc"

Type SYSTEMTIME
  wYear         As Word
  wMonth        As Word
  wDayOfWeek    As Word
  wDay          As Word
  wHour         As Word
  wMinute       As Word
  wSecond       As Word
  wMilliseconds As Word
End Type


Declare Function VariantTimeToSystemTime Lib "OleAut32.dll" Alias "VariantTimeToSystemTime" _
( _
  Byval vtime        As Double, _
  Byref lpSystemTime As SYSTEMTIME _
) As Long


Function PBMain() As Long
  Local pXLWorkSheet As XLWorkSheet
  Local pXLWorkBook As XLWorkbook
  Local pXLApp As XLApplication
  Local vVnt1,vVnt2 As Variant
  Local strRange As WString
  Local st As SystemTime
  Register i As Long

  pXLApp = NewCom "Excel.Application"
  If IsObject(pXLApp) Then
     vVnt1="C:\Code\PwrBasic\PBCC6\ExcelAuto\Test.xls"
     Object Call pXLApp.Workbooks.Open(vVnt1) To vVnt2
     Set pXLWorkBook = vVnt2
     If IsObject(pXLWorkBook) Then
        vVnt1=1
        Object Let pXLApp.Visible=vVnt1
        vVnt1="TheBasics"
        Object Call pXLWorkbook.Sheets(vVnt1).Select
        Object Get pXLWorkbook.ActiveSheet To vVnt2
        Set pXLWorkSheet = vVnt2
        If IsObject(pXLWorkSheet) Then
           For i=2 To 5    ' get 1st column
             strRange = "A" & Trim$(Str$(i))
             vVnt1=strRange
             Object Get pXLWorkSheet.Range(vVnt1).Value To vVnt2
             Console.Print "[A" + Trim$(Str$(i)) & "] = " & Str$(Variant#(vVnt2))
           Next i
           For i=2 To 5  'get 2nd column
             strRange = "B" & Trim$(Str$(i))
             vVnt1=strRange
             Object Get pXLWorkSheet.Range(vVnt1).Value To vVnt2
             Con.Print "[B" + Trim$(Str$(i)) & "] = " & Variant$(vVnt2)
           Next i
           For i=2 To 5  'get 3rd column
             strRange = "C" & Trim$(Str$(i))
             vVnt1=strRange
             Object Get pXLWorkSheet.Range(vVnt1).Value To vVnt2
             Select Case As Long VariantVt(vVnt2)
               Case %VT_R8
                 Print "[C" + Trim$(Str$(i)) & "] = " & Format$(Variant#(vVnt2),"##0.0")
               Case %VT_DATE
                 VariantTimeToSystemTime(Variant#(vVnt2),st)
                 Print "[C" + Trim$(Str$(i)) & "] = " & Trim$(Str$(st.wMonth)) + "/" & Trim$(Str$(st.wDay)) + "/" & Trim$(Str$(st.wYear))
               Case %VT_BSTR
                 Print "[C" + Trim$(Str$(i)) & "] = " & Variant$(vVnt2)
             End Select
           Next i
           Set pXLWorkSheet=Nothing
        End If
        Con.Waitkey$
        Object Call pXLWorkbook.Close()
        Set pXLWorkBook=Nothing
     End If
     Object Call pXLApp.Quit()
     Set pXLApp=Nothing
  End If

  PBMain=0
End Function

#if 0

[A2] =  1
[A3] =  2
[A4] =  3
[A5] =  4
[B2] = string
[B3] = number
[B4] = date
[B5] = percentage
[C2] = Bonjour le monde !
[C3] = 123.0
[C4] = 10/24/1964
[C5] = 0.8

#endif


In the above program I did without the #Include “Windows.inc” and simply placed declarations of VariantTimeToSystem() time and SystemTime user defined type ahead of the source. However, the #Include that is there …

#Include “PBExcel.inc”

…is analogous in function to your *.tlh and *.tli files although its bulk, though largs, is considerable less – 15,329 lines and 825 KB. Here is briefly and annotated what the _Application Interface looks like…

freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
INTERFACE IDBIND XLApplication
   MEMBER GET  Application <148> () AS XLApplication   ' <coclass>
   MEMBER GET  Creator <149> () AS LONG   ' XLXlCreator <enum>
   MEMBER GET  Parent <150> () AS XLApplication   ' <coclass>
   MEMBER GET  ActiveCell <305> () AS XLRange   ' <dispinterface>
   MEMBER GET  ActiveChart <183> () AS XLChart   ' <coclass>
   MEMBER GET  ActiveDialog <815> () AS XLDialogSheet   ' <dispinterface>
   MEMBER GET  ActiveMenuBar <758> () AS XLMenuBar   ' <dispinterface>
   MEMBER GET  ActivePrinter <306> () AS WSTRING
   MEMBER LET  ActivePrinter <306> ()   ' Parameter Type AS WSTRING
   MEMBER GET  ActiveSheet <307> () AS IDispatch
   MEMBER GET  ActiveWindow <759> () AS XLWindow   ' <dispinterface>
   MEMBER GET  ActiveWorkbook <308> () AS XLWorkbook   ' <coclass>
   MEMBER GET  AddIns <549> () AS XLAddIns   ' <dispinterface>
   MEMBER GET  Assistant <1438> () AS IDispatch   ' Assistant <dispinterface>
   MEMBER CALL Calculate <279> ()
   MEMBER GET  Cells <238> () AS XLRange   ' <dispinterface>
   MEMBER GET  Charts <121> () AS XLSheets   ' <dispinterface>
   MEMBER GET  Columns <241> () AS XLRange   ' <dispinterface>
   MEMBER GET  CommandBars <1439> () AS IDispatch   ' CommandBars <coclass>
   MEMBER GET  DDEAppReturnCode <332> () AS LONG
   MEMBER CALL DDEExecute <333> (IN prm_Channel AS LONG<0>, _
               IN prm_String AS WSTRING<1>)
   MEMBER CALL DDEInitiate <334> (IN prm_App AS WSTRING<0>, _
               IN prm_Topic AS WSTRING<1>) AS LONG
   MEMBER CALL DDEPoke <335> (IN prm_Channel AS LONG<0>, _
               IN prm_Item AS VARIANT<1>, _
               IN prm_Data AS VARIANT<2>)
   MEMBER CALL DDERequest <336> (IN prm_Channel AS LONG<0>, _
               IN prm_Item AS WSTRING<1>) AS VARIANT
   MEMBER CALL DDETerminate <337> (IN prm_Channel AS LONG<0>)
   MEMBER GET  DialogSheets <764> () AS XLSheets   ' <dispinterface>
   MEMBER CALL Evaluate <1> (IN prm_Name AS VARIANT<0>) AS VARIANT
   MEMBER CALL Evaluate_ <-5> (IN prm_Name AS VARIANT<0>) AS VARIANT
   MEMBER CALL ExecuteExcel4Macro <350> (IN prm_String AS WSTRING<0>) AS VARIANT
   MEMBER CALL Intersect <766> (IN prm_Arg1 AS XLRange<0>, _
               IN prm_Arg2 AS XLRange<1>, _
               OPTIONAL IN prm_Arg3 AS VARIANT<2>, _
               OPTIONAL IN prm_Arg4 AS VARIANT<3>, _
               OPTIONAL IN prm_Arg5 AS VARIANT<4>, _
               OPTIONAL IN prm_Arg6 AS VARIANT<5>, _
               OPTIONAL IN prm_Arg7 AS VARIANT<6>, _
               OPTIONAL IN prm_Arg8 AS VARIANT<7>, _
               OPTIONAL IN prm_Arg9 AS VARIANT<8>, _
               OPTIONAL IN prm_Arg10 AS VARIANT<9>, _
               OPTIONAL IN prm_Arg11 AS VARIANT<10>, _
               OPTIONAL IN prm_Arg12 AS VARIANT<11>, _
               OPTIONAL IN prm_Arg13 AS VARIANT<12>, _
               OPTIONAL IN prm_Arg14 AS VARIANT<13>, _
               OPTIONAL IN prm_Arg15 AS VARIANT<14>, _
               OPTIONAL IN prm_Arg16 AS VARIANT<15>, _
               OPTIONAL IN prm_Arg17 AS VARIANT<16>, _
               OPTIONAL IN prm_Arg18 AS VARIANT<17>, _
               OPTIONAL IN prm_Arg19 AS VARIANT<18>, _
               OPTIONAL IN prm_Arg20 AS VARIANT<19>, _
               OPTIONAL IN prm_Arg21 AS VARIANT<20>, _
               OPTIONAL IN prm_Arg22 AS VARIANT<21>, _
               OPTIONAL IN prm_Arg23 AS VARIANT<22>, _
               OPTIONAL IN prm_Arg24 AS VARIANT<23>, _
               OPTIONAL IN prm_Arg25 AS VARIANT<24>, _
               OPTIONAL IN prm_Arg26 AS VARIANT<25>, _
               OPTIONAL IN prm_Arg27 AS VARIANT<26>, _
               OPTIONAL IN prm_Arg28 AS VARIANT<27>, _
               OPTIONAL IN prm_Arg29 AS VARIANT<28>, _
               OPTIONAL IN prm_Arg30 AS VARIANT<29>) AS XLRange   ' <dispinterface>
   MEMBER GET  MenuBars <589> () AS XLMenuBars   ' <dispinterface>
   MEMBER GET  Modules <582> () AS XLModules   ' <dispinterface>
   MEMBER GET  Names <442> () AS XLNames   ' <dispinterface>
   MEMBER GET  Range <197> (IN prm_Cell1 AS VARIANT<0>, _
               OPTIONAL IN prm_Cell2 AS VARIANT<1>) AS XLRange   ' <dispinterface>
   MEMBER GET  Rows <258> () AS XLRange   ' <dispinterface>
   ….
   …
   etc.
END INTERFACE


The IDBIND keyword referrs to the fact that the compiler is retrieving the dispatch ids from the various method calls of the various interfaces. Oddly enough, the above program resulted in a 28 K executable, or about a K smaller than the one using pure IDispatch. No doubt having the include file with the dispids made things a lot easier for the compiler.

But if I’m calling your #import stuff ‘grey box code’ how is this any different? I hate to admit this but you’ve got me on that one. About all I can say is the PowerBASIC stuff is less verbose and more readable. But still ‘grey box code’.

Really, the only solution to this dilemma for me – purist that I am, is to write my own TypeLib Browser utility similar to what #import is, and that way I can claim the auto-generated files are indeed my creation. And indeed I spent about 6 months on that exact thing about four years ago but got side-tracked by some stuff going on where I work, and I never went back to it to finish it. You know, I believe that might be the only software project in my career I started but never finished. Amazingly though, it was almost done where I left it. And the final part I was somewhat stuck on involved Excel believe it or not. My code worked perfectly on just about everything I tried it on, but the big Office Applications such as Excel and Word had *.idl files which themselve used the ‘importlib’ keyword to include other files, as such…
1
2
3
4
5
6
7
8
9
10
11
 
[uuid(00020813-0000-0000-C000-000000000046),version(1.3),helpstring("Microsoft Excel 9.0 Object Library"),helpfile("VBAXL9.CHM"),  helpcontext(0x0000ffff)] library Excel
{
  // TLib :     // TLib : Microsoft Visual Basic for Applications Extensibility 5.3 : {0002E157-0000-0000-C000-000000000046}
  importlib("VBE6EXT.OLB");
  // TLib : Microsoft Office 9.0 Object Library : {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
  importlib("MSO9.DLL");
  // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
  importlib("stdole2.tlb");
  …..
  …..
};


So I needed to modify my code to first process any ‘importlib’ included files before the main file itself. I just never got to it when I got sidetracked. But my code allowed me to easily utilize all the ActiveX Controls in old VB6 just fine, as well as most other COM components I tried. In fact, I recall posting here a 12 K Sdk example hosting the ‘Shell.Explorer” ActiveX Control which is the heart of MS Internet Explorer. Maybe I ought to return to that project sometime.
Topic archived. No new replies allowed.