asked on

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.

jkr

>>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'.

alan93

ASKER

probably, I changed that but it still fails.

I get: "SaveAs method of Workbook class failed."

alan93

ASKER

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.

ASKER CERTIFIED SOLUTION

nonubik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alan93

ASKER

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.