Using Excel9.CPP to convert .xls to .txt

i'm trying to use the following function to convert an excel file to a tab delimited file.

The problem is I don't understand the VARIANT params and how to populate them.
Here is the function:

void _Workbook::SaveAs(const VARIANT& Filename, const VARIANT& FileFormat, const VARIANT& Password, const VARIANT& WriteResPassword, const VARIANT& ReadOnlyRecommended, const VARIANT& CreateBackup, long AccessMode, const VARIANT& ConflictResolution,
            const VARIANT& AddToMru, const VARIANT& TextCodepage, const VARIANT& TextVisualLayout)
{
      static BYTE parms[] =
            VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_I4 VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT;
      InvokeHelper(0x11c, DISPATCH_METHOD, VT_EMPTY, NULL, parms,
             &Filename, &FileFormat, &Password, &WriteResPassword, &ReadOnlyRecommended, &CreateBackup, AccessMode, &ConflictResolution, &AddToMru, &TextCodepage, &TextVisualLayout);
}


I am calling it in an implementation file here:

      Worksheets oWorksheets;
      Workbooks oWorkbooks;
      _Worksheet oWorksheet;
      _Workbook oWorkbook;
      Range oRange;
      sLine = "";
      
      CString sItem;
      if (true == m_bHasExcel)
      {
            oWorkbooks = m_oExcelApp.GetWorkbooks();

            oWorkbook = oWorkbooks.Open(m_FileName,vOpt,COleVariant((short)true),
                        vOpt, vOpt,vOpt, vOpt, vOpt,vOpt,vOpt,vOpt,vOpt,vOpt);
            
            oWorksheets = oWorkbook.GetWorksheets();
            oWorksheet = oWorksheets.GetItem(COleVariant((short)1));      

            long vLong = 0;
            VARIANT param;
            VARIANT vText;
            param.vt      = VT_BSTR;
            param.bstrVal = SysAllocString(L"c:\\test.txt\0");
        
                              //VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT      VTS_VARIANT VTS_I4 VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT;
            
                               //HERE
                               oWorkbook.SaveAs(param, vText, vText, vText, vText, vText, vLong , vText, vText, vText, vText);



This function call fails

If someone knows another way to do this please let me know how.
alan93Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nonubikConnect With a Mentor Commented:
You can use xlTextwindows as the FileFormat parameter (20) or choose from the common file formats

xlCSV=6
xlCSVMac=22
xlCSVMSDOS=24
xlCSVWindows=23
xlCurrentPlatformText=-4158
xlExcel9795=436
xlTextMSDOS=21
xlTextPrinter=36
xlTextWindows=20
xlUnicodeText=42

oWorkbook.SaveAs(param, COleVariant((long)20), vText, vText, vText, vText, COleVariant((long)1) , vText, vText, vText, vText);
                                                                                                                                      ^
                                                                                                                                       |
                                                                                                                                 //AccessMode: xlNoChange = 1
0
 
jkrCommented:
>>This function call fails

Shouldn't that be

          long vLong = 0;
         VARIANT param;
         VARIANT vText;
         param.vt      = VT_BSTR;
         param.bstrVal = SysAllocString(L"c:\\test.txt\0");
         vText.vt = VT_EMPTY; // <<<<<<<<<<<---------------------!!!
       
                             //VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT      VTS_VARIANT VTS_I4 VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT;
         
                              //HERE
                              oWorkbook.SaveAs(param, vText, vText, vText, vText, vText, vLong , vText, vText, vText, vText);

IIRC you need to set the variant to 'empty'.
0
 
alan93Author Commented:
probably, I changed that but it still fails.

I get: "SaveAs method of Workbook class failed."
0
 
alan93Author Commented:

I am probably needing a File Format value (2nd param, see void _Workbook::SaveAs above) but don't know what this should be if I want the output to be tab delimited text .  How do you find this out? Its like a hidden mystery of the universe.
0
 
alan93Author Commented:
Thanks, that is the ticket!

However, it errored saying "not able to convert param 7 from COleVariant to Long" but I just changed it to (long)1 and it worked.

thanks a bunch.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.