?
Solved

Automate Excel by Visual C++ and MFC.

Posted on 2003-03-01
17
Medium Priority
?
1,457 Views
Last Modified: 2013-12-14
I am using Visula C++ to write a program to automate excel and I have read the webpage "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xltocObjectModelApplication.asp"
, my problem is I don't know how to pass the parameter "Array(Array(3, 9), Array(1, 2))" to "FieldInfo" in C++ language. The following webpage is also useful: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffdev/html/vsofficedev.asp
0
Comment
Question by:LamLapTak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
17 Comments
 

Author Comment

by:LamLapTak
ID: 8048210
very very urgent
0
 
LVL 1

Expert Comment

by:Ess
ID: 8049298
How are you automating excel?  You can add a class library (excel9.olb)in the class wizard to have VC++ create the class wrappers for you.  The parameter is probably passed as a COleSafeArray in MFC or a SafeArray if not using MFC but I don't know which excel function you are trying to use.
0
 

Author Comment

by:LamLapTak
ID: 8050804
    // TODO: Add your control notification handler code here
     // Convenient variables.
     COleVariant
          covTrue((short)TRUE),
          covFalse((short)FALSE),
          covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

     _Application oApp;
     _Workbook oBook;
     Workbooks oBooks;
     Worksheets oSheets;
     _Worksheet oSheet;
     Range range;
     Range cols;
     Range autoformat;
     CellFormat cellformat;
     CellFormat numberformat;
     Font font;
     PageSetup pagesetup;
     PageSetup margin;

     //Launch Excel and make it invisible.
     if(!oApp.CreateDispatch("Excel.Application"))
     {
          AfxMessageBox("Excel 2002 must be installed before you can use this program!!");
          return;
     }
     oApp.SetVisible(TRUE);

     //Get the Workbooks collection.
     oBooks = oApp.GetWorkbooks();

     //Open the label.txt
     oBooks.OpenText("C:\\WINDOWS\\Desktop\\Packing Label Checking System\\label.txt",  //text file name
          covOptional,       //origin
          covOptional,       //StartRow
          COleVariant("1"),  //DataType
          1,                 //TextQualifier
          covTrue,           //ConsecutiveDelimiter
          covTrue,           //Tab
          covFalse,          //Semicolon
          covFalse,          //Comma
          covFalse,          //Space
          covFalse,          //Other
          covOptional,          //OtherChar
          COleVariant("Array(Array(2, 5))"),  //FieldInfo    //my Problem is here
          covOptional,       //TextVisualLayout
          covOptional,       //DecimalSeparator
          covOptional,       //ThousandsSeparator
          covOptional,       //TrailingMinusNumbers
          covOptional        //Local
          );
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:LamLapTak
ID: 8050823
Ess,
   I have incude <excel.h>, and I can use most of the function, so could you see my problem? I just post my code, hope you can help me. I just want to open a txt file in excel by using C++. but when I open the txt file, I need to specific to format of each column, which needs me to pass the parameter to "FieldInfo.
0
 

Author Comment

by:LamLapTak
ID: 8050970
Ess,
   I have incude <excel.h>, and I can use most of the function, so could you see my problem? I just post my code, hope you can help me. I just want to open a txt file in excel by using C++. but when I open the txt file, I need to specific to format of each column, which needs me to pass the parameter to "FieldInfo.
0
 

Author Comment

by:LamLapTak
ID: 8051545
Anyone can tell me how to pass a COleSafeArray in MFC?
0
 
LVL 1

Expert Comment

by:Ess
ID: 8054337
Your problem seems to be in the array statement.  Try COleSafeArray as follows and then pass that parameter to the function.

COleSafeArray oSafeArray;
//create the array note the first parameter can be anything
// use the specific type of array this creates an array of
//longs,second parameter is number of dimensions,third is
//array of dimensions with elements of each for this
//2 dimensional array with 2 elements and then 5 elements
DWORD rgElem[2] = {2,5};
oSafeArray.Create(VT_I4,2,rgElem);
//set elements
long index[2];
index[0]=0;
index[1]=0;
oSafeArray.PutElement(index,(void*)(&data));
index[0]=0;
index[1]=1;
oSafeArray.PutElement(index,(void*)(&data);

continue this for each element of the array using index to point to each element.  Better to use a for loop!!  Also remember that PutElement requires a pointer to void* so you need to typecast your data for each element.  The array index[] is where you tell PutElement which element you are filling.  In the above example you need index[0] to vary from 0-1 for each element of the second dimension.  You also need to vary the second element from 0-4 in the above example for each element.

Hope this helps.  After filling the array just pass oSafeArray as the parameter and it should work.
0
 
LVL 1

Expert Comment

by:Ess
ID: 8054464
Another note an array of arrays is just a two dimensional array.  For that function you need the number of columns and 2 for the number of elements for each dimension.  For example, for 5 columns it is oSafeArray[5][2].  It may also be [2][5] i'm not sure how excel reads the data so try it both ways.
0
 

Author Comment

by:LamLapTak
ID: 8065816
Ess,
   Really thanks for your help!!
   I have tried the above coding, but when I compile it,
it said I need to declare 'data'. But what type I should declare it? and also, I just want to pass (2, 2) into to that parameter.
   Thanks for your help!!
0
 
LVL 1

Expert Comment

by:Ess
ID: 8067848
data is just shorthand for whatever you are putting into the array.  If you are using data as your variable you need to declare it before using it.  For example if the data you are putting in the array is of type long declare it as follows:

long data;
data = 2;
//then insert that into the array
//then change data for the next value in your array
data = 4;

each member of the array has to have a value.  the value is your constant from excel for each of the two positions for each column.

I believe the constants for OpenText for FieldInfo are of type long but you may need to check them they may also be integers.  Use OleView for excel automation object and view the typelib to see their types.
0
 
LVL 1

Expert Comment

by:Ess
ID: 8067989
According to your code your text data is delimited.  Your first element of your two element array will be your column number.  Your second element is your column data type. type both of these as integers according to OleView.  According to your code your format is xlTextFormat.  So for each column if the numbers you show are correct you need to set the first element to the column number and the second to 2.

for(int i=0;i<ColumnNumber;++i)
{
   data = i+1;
  //Put data into the array
  data = 2;
  //Put data into the array
}

declare data as type int
int data;
also remember column numbers are 1 based not zero based.
0
 

Author Comment

by:LamLapTak
ID: 8098630
ESS,
   Your coding really work!! But there is still one problem, I tried the following:

DWORD rgElem[2] = {1,2};
oSafeArray.Create(VT_I4,2,rgElem);
long data;
index[0]=0;
index[1]=0;
data = 2;
oSafeArray.PutElement(index,(void*)(&data));
index[0]=0;
index[1]=1;
data = 2;
oSafeArray.PutElement(index,(void*)(&data);

    The result is the type of column 1 changes to xlTextFormat, but not column 2, and I never input "1" into the array, so why it changed column 1 for me? And I had tried to change rgElem[2] to {2,2}, {3,2}...and put data = 2 to the array, but I still can't change the data type of column 2 to xlTextFormat. Do you know where I should put "2" which stand for the number of column and "2" which stand for "xlTextFormat"?

   And thanks again for your help!!

0
 

Author Comment

by:LamLapTak
ID: 8098815
ESS,
    I finally made column 1 and column 2 to xlTextFormat successfully with the following code:

DWORD rgElem[2] = {2,2};
oSafeArray.Create(VT_I4,2,rgElem);

long index[2];
index[0]=0;
index[1]=0;
long data;
data = 2;
oSafeArray.PutElement(index,(void*)(&data));
index[0]=0;
index[1]=1;
data = 2;
oSafeArray.PutElement(index,(void*)(&data));

index[0]=1;
index[1]=0;
data = 9;  // I really don't know why I should set 9 here
oSafeArray.PutElement(index,(void*)(&data));
index[0]=1;
index[1]=1;
data = 2;
oSafeArray.PutElement(index,(void*)(&data));

    But I really don't know why the above code work, anyway, really thanks for your help, and I will accept your comment as answer as soon as possible, as I afraid I will have problem later on.

    Anyway, thank you very much. I will finish my program as soon as possible.
0
 
LVL 1

Accepted Solution

by:
Ess earned 300 total points
ID: 8099835
Your code is still incorrect.  The first element of the array is the column number the second is the formatter.
So for the first column set [0][0] element to 1(column Number) and [0][1] element to 2(xlTextFormat).  Set [1][0] to 2(column number) and [1][1] to 2(format specifier).  You are setting to column 9 in your code.  Also, if no format specifier is used then general formatting is used.  That may be why it is working for you.  I may be wrong and it may be that the 2-dim arrays are the first element and the number of formatters are the second element.  But for your code it doesn't matter.  For example if reversed then [0][0] = 1(column number), [1][0] = 2(format), [0][1] = 2(column number), and [1][1] = 2(format).  Same numbers as above.  If you have other formatting to add then try it both ways.
0
 
LVL 1

Expert Comment

by:Ess
ID: 8099896
Forget what I said about reversing the order the first half is right.  Your problem is that you are confusing the array element and the column numbers.  The first index of the array is the NUMBER of FORMAT specifiers, not the column number.  You still have to specify the column numbers in the array values.  To format column 2 you have to set the elements to 2 for the first element and 2 for the xlFormat in the second element.  Note you could also set this as [0][0] = 2 [0][1] = 2;  You don't have to set it as any particular 2-element array.  You just have to set one of the 2-element arrays as this.  If you still have problems then it has to do with your data and you are choosing the wrong format to read the data.
0
 

Author Comment

by:LamLapTak
ID: 8112941
Ess,
   Thank you very much !! I finally get it!!
0
 

Author Comment

by:LamLapTak
ID: 8112956
Thank you very much for your help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

C++ Properties One feature missing from standard C++ that you will find in many other Object Oriented Programming languages is something called a Property (http://www.experts-exchange.com/Programming/Languages/CPP/A_3912-Object-Properties-in-C.ht…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question