//Save current workbook as an Excel file:
//ActiveWorkbook.SaveAs
//FileName:=szFileName, FileFormat:=xlNormal,
//Password:=szPassword,
//WriteResPassword:=szWritePassword,
//ReadOnlyRecommended:= bReadOnly,
//CreateBackup:= bBackup
BOOL CXLAutomation::SaveAs(CString szFileName, int nFileFormat, CString szPassword, CString szWritePassword, BOOL bReadOnly, BOOL bBackUp)
{
if(NULL == m_pdispWorkbook)
return FALSE;
ClearAllArgs();
AddArgumentBool(L"CreateBackup", 0, bBackUp);
AddArgumentBool(L"ReadOnlyRecommended", 0, bReadOnly);
AddArgumentCString(L"WriteResPassword", 0, szWritePassword);
AddArgumentCString(L"Password", 0, szPassword);
AddArgumentCString(L"FileName", 0, szFileName);
if (!ExlInvoke(m_pdispWorkbook, L"SaveAs", NULL, DISPATCH_METHOD, DISP_FREEARGS))
return FALSE;
return TRUE;
}
//Perform Worksheets.Cells(x,y).Value = szStr
BOOL CXLAutomation::SetCellsValueToString(double Column, double Row, CString szStr)
{
if(NULL == m_pdispWorksheet)
return FALSE;
if(szStr.IsEmpty())
return FALSE;
long nBuffSize = szStr.GetLength();
VARIANTARG vargRng;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
AddArgumentCString(NULL, 0, szStr );
if (!ExlInvoke(vargRng.pdispVal, L"Value", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
if(ReleaseVariant(&vargRng)>=1)
{
CString error;
error.Format("Error in outputing row %d, colume %d, value %s", Row, Column, szStr);
AfxMessageBox(error);
}
return TRUE;
}
//Open Microsoft Excel file and switch to the firs available worksheet.
BOOL CXLAutomation::OpenExcelFile(CString szFileName)
{
//Leave if the file cannot be open
if(NULL == m_pdispExcelApp)
return FALSE;
if(szFileName.IsEmpty())
return FALSE;
VARIANTARG varg1, vargWorkbook, vargWorksheet;
ClearAllArgs();
if (!ExlInvoke(m_pdispExcelApp, L"Workbooks", &varg1, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(L"Filename", 0, szFileName);
if (!ExlInvoke(varg1.pdispVal, L"Open", &vargWorkbook, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Now let's get the first worksheet of this workbook
ClearAllArgs();
AddArgumentInt2(NULL, 0, 1);
if (!ExlInvoke(vargWorkbook.pdispVal, L"Worksheets", &vargWorksheet, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Close the empty worksheet
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"Close", NULL, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Remember the newly open worksheet
m_pdispWorkbook = vargWorkbook.pdispVal;
m_pdispWorksheet = vargWorksheet.pdispVal;
return TRUE;
}
//Perform Worksheets.Cells(x,y).Value = szStr
BOOL CXLAutomation::SetCellsValueToString(double Column, double Row, CString szStr)
{
if(NULL == m_pdispWorksheet)
return FALSE;
if(szStr.IsEmpty())
return FALSE;
long nBuffSize = szStr.GetLength();
VARIANTARG vargRng;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
AddArgumentCString(NULL, 0, szStr );
if (!ExlInvoke(vargRng.pdispVal, L"Value", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
ReleaseVariant(&vargRng);
return TRUE;
}
BOOL CXLAutomation::SetWorksheetTitle(CString wsTitle)
{
//Sheets(Sheets.Count).Name = OtherName
ClearAllArgs();
AddArgumentCString(NULL, 0, wsTitle);
if (!ExlInvoke(m_pdispActiveWorksheet, L"Name", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
return TRUE;
}
BOOL CXLAutomation::SetChartTitle(CString cTitle)
{
//Sheets(Sheets.Count).Name = OtherName
ClearAllArgs();
AddArgumentCString(NULL, 0, cTitle);
if (!ExlInvoke(m_pdispActiveChart, L"Name", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
//.ChartTitle.Characters.Text = "My Chart Title"
VARIANTARG vargTitle, vargChar;
ClearAllArgs();
if (!ExlInvoke(m_pdispActiveChart, L"ChartTitle", &vargTitle, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(vargTitle.pdispVal, L"Characters", &vargChar, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, cTitle);
if (!ExlInvoke(vargTitle.pdispVal, L"Text", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
return TRUE;
}
//Set chart's X and Y titles by executing this macros:
//ActiveChart.ChartArea.Select
//ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
//ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = szXTitle
//ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
// ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = szYTitle
//xlCategory = 1
//xlPrimary = 1
//xlValue = 2
BOOL CXLAutomation::SetAxesTitle(CString szXTitle, CString szYTitle)
{
//Leave if there is no chart
if(NULL == m_pdispActiveChart)
return FALSE;
int xlCategory = 1;
int xlPrimary = 1;
int xlValue = 2;
VARIANTARG varg1, varg2;
//Select chart: ActiveChart.ChartArea.Select
ClearAllArgs();
if(!ExlInvoke(m_pdispActiveChart, L"ChartArea",&varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS)) //DISP_FREEARGS))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"Select", NULL, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Set title only if the title has been provided by user
if(!szXTitle.IsEmpty())
{
//ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ClearAllArgs();
AddArgumentInt2(NULL, 0, xlPrimary);
AddArgumentInt2(NULL, 0, xlCategory);
if(!ExlInvoke(m_pdispActiveChart, L"Axes",&varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentBool(NULL, 0, TRUE);
if(!ExlInvoke(varg2.pdispVal, L"HasTitle",NULL, DISPATCH_PROPERTYPUT, DISP_FREEARGS))
return FALSE;
//ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = szXTitle
VARIANTARG vargTitle, vargChar;
ClearAllArgs();
if (!ExlInvoke(varg2.pdispVal, L"AxisTitle", &vargTitle, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(vargTitle.pdispVal, L"Characters", &vargChar, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, szXTitle);
if (!ExlInvoke(vargTitle.pdispVal, L"Text", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
ReleaseVariant(&vargChar);
ReleaseVariant(&vargTitle);
ReleaseVariant(&varg2);
}
if(!szYTitle.IsEmpty())
{
//ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ClearAllArgs();
AddArgumentInt2(NULL, 0, xlPrimary);
AddArgumentInt2(NULL, 0, xlValue);
if(!ExlInvoke(m_pdispActiveChart, L"Axes",&varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentBool(NULL, 0, TRUE);
if(!ExlInvoke(varg2.pdispVal, L"HasTitle",NULL, DISPATCH_PROPERTYPUT, DISP_FREEARGS))
return FALSE;
//ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = szYTitle
VARIANTARG vargTitle, vargChar;
ClearAllArgs();
if (!ExlInvoke(varg2.pdispVal, L"AxisTitle", &vargTitle, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(vargTitle.pdispVal, L"Characters", &vargChar, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, szYTitle);
if (!ExlInvoke(vargTitle.pdispVal, L"Text", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
}
return TRUE;
}
//Insert picture from file szFileName to worksheet
//The left top corner of the picture is position in (Column, nRow)
//on the worksheet
//Size of the picture in % of original size is given by dPicWidth, dPicHeight
//If dPicWidth = 0.0, dPicHeight = 0.0 or dPicWidth = 1.0, dPicHeight = 1.0
//the picture has default (i.e., original) size
BOOL CXLAutomation::InsertPictureToWorksheet(CString szFileName, int Column, int Row, double dPicWidth, double dPicHeight)
{
//No file name provided. Leave.
if(szFileName.IsEmpty())
return FALSE;
if(NULL == m_pdispWorksheet)
return FALSE;
//First, select cell where you want copy the picture (i.e., the top left corner of the picture
//Leave if Column and Row are outside the worksheet
if((Column < 1) || (Row < 1))
return FALSE;
VARIANTARG vargRng, vargActiveCell;
VARIANTARG varg1, varg2;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
if(!ExlInvoke(vargRng.pdispVal, L"Select",&vargActiveCell, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ReleaseVariant(&vargRng);
ReleaseVariant(&vargActiveCell);
//Run this macros to incert picture from file
//ActiveSheet.Pictures.Insert("c:\mypicture.bmp").Select
//or
//ActiveSheet.Pictures.Insert ("C:\mypicture.bmp")
//ActiveSheet.Pictures.ShapeRange.ScaleWidth 0.31, msoScaleFromTopLeft
ClearAllArgs();
if (!ExlInvoke(m_pdispWorksheet, L"Pictures", &varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, szFileName);
if (!ExlInvoke(varg1.pdispVal, L"Insert", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
if((dPicWidth != 0.0) && (dPicHeight != 0.0))
{
//Run this macros to resize the picture:
//Selection.ShapeRange.ScaleWidth dPicWidth, msoFalse, msoScaleFromTopLeft
//Selection.ShapeRange.ScaleHeight dPicWidth, msoFalse, msoScaleFromTopLeft
VARIANTARG vargImage;
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"ShapeRange", &vargImage, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
//msoScaleFromTopLeft = 0 - this argument indicate scaling from top left
AddArgumentInt2(NULL, 0, 0);
AddArgumentDouble(NULL, 0, dPicWidth);
if (!ExlInvoke(vargImage.pdispVal, L"ScaleWidth", NULL, DISPATCH_METHOD, 0)) //DISP_FREEARGS))
return FALSE;
if (!ExlInvoke(vargImage.pdispVal, L"ScaleHeight", NULL, DISPATCH_METHOD, 0)) //DISP_FREEARGS))
return FALSE;
ClearAllArgs();
ReleaseVariant(&vargImage);
}
ReleaseVariant(&varg1);
ReleaseVariant(&varg2);
return TRUE;
}
void LaunchWord(CString sPathWord,CString sPathOutput){
BSTR param2 = sPathWord.AllocSysString();//SysAllocString(OLESTR("HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Windows\\CurrentVersion\\App Paths\\IEXPLORE.EXE"));
BSTR param1 = sPathOutput.AllocSysString();//SysAllocString(OLESTR(""));
BSTR name1 = SysAllocString(OLESTR("PathWord"));
BSTR name2 = SysAllocString(OLESTR("PathOutput"));
AddArgumentCString(name1,0,param1);
AddArgumentCString(name2,0,param2);
VARIANTARG FAR rgvarg[2];
V_BSTRREF(&rgvarg[0]) = ¶m1;
V_BSTRREF(&rgvarg[1]) = ¶m2;
DISPPARAMS dpNoArgs = {g_aVargs, NULL, 2, 0};
VARIANT vResult;
OLECHAR FAR* szFunction;
//BSTR bstrTemp;
// IDispatch pointers for Word's objects
//IDispatch* pDispDocs; //Documents collection
// DISPIDs
DISPID dispid_Docs; //Documents property of Application
//object
//Initialize the COM libraries
::CoInitialize(NULL);
// Create an instance of the Word application and obtain the
// pointer to the application's IDispatch interface.
CLSID clsid;
CLSIDFromString(L"UtilsVB.clsLauncher", &clsid);
IUnknown* pUnk;
HRESULT hr = ::CoCreateInstance( clsid, NULL, CLSCTX_SERVER,
IID_IUnknown, (void**) &pUnk);
IDispatch* pDispApp;
hr = pUnk->QueryInterface(IID_IDispatch, (void**)&pDispApp);
// Get IDispatch* for the Documents collection object
szFunction = OLESTR("Launcher");
hr = pDispApp->GetIDsOfNames (IID_NULL, &szFunction, 1,
LOCALE_USER_DEFAULT, &dispid_Docs);
unsigned int FAR uArgErr;
hr = pDispApp->Invoke (dispid_Docs, IID_NULL,
LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpNoArgs, &vResult, NULL, &uArgErr);
//Execute Internet Explorer
/*
CString sPath;
HINSTANCE hinstance=ShellExecute(
AfxGetMainWnd( )->m_hWnd, //HWND hwnd,
"open", //LPCTSTR lpVerb,
CString(vResult.bstrVal),//"C:\\Archivos de programa\\Internet Explorer\\IEXPLORE.EXE",//"C:\\Archivos de programa\\Microsoft Office\\Office\\WINWORD.EXE",//LPCTSTR lpFile,
sPath,//"\"file://C:\\2002\\DraftCable\\Src\\testfile.htm\"", //LPCTSTR lpParameters,
NULL, //LPCTSTR lpDirectory,
SW_SHOWNORMAL //INT nShowCmd
);
*/
ClearAllArgs();
pDispApp->Release();
pUnk->Release();
//...
::CoUninitialize();
}
