I'm trying to create a DLL function in MS VC++ that when called will simply place a given value in the cell of an Excel spreadsheet. Can anyone provide the code?

Are you using VC6 or VC.NET ?Dominiconnor

The code here is descended from some MS stuff, any bugs of course are mine, but that stilldoesn't mean I promise to fix them...The joy of this approach is that you can use pretty much any facility of Excel that you couldfrom VBA.First thing you need to do is get the type libraries for Excel#import <mso9.dll> no_namespace rename("DocumentProperties", "DocumentPropertiesXL") #import "C:\cdrive\Program Files\Common Files\Microsoft Shared\VBA\vbeext1.olb" no_namespace #import <excel9.olb> rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") rename("DocumentProperties", "DocumentPropertiesXL") no_dual_interfaces// I'm on Excel 9 (2000) here. If you're not, this code won't work // Pretty trivial to fix though, swap the '9' for whatever version you have.// In particular, you may have to hunt over your disk to find vbeext1.olb// Note that you have to exclude a few Excel items since they clash with (inter alia) Win32.// A simple errror catcher#include <stdio.h>#include <tchar.h>struct StartOle { StartOle() { CoInitialize(NULL); } ~StartOle() { CoUninitialize(); }} _inst_StartOle;void dump_com_error(_com_error &e){ _tprintf(_T("Oops - hit an error!\n")); _tprintf(_T("\a\tCode = %08lx\n"), e.Error()); _tprintf(_T("\a\tCode meaning = %s\n"), e.ErrorMessage()); _bstr_t bstrSource(e.Source()); _bstr_t bstrDescription(e.Description()); _tprintf(_T("\a\tSource = %s\n"), (LPCTSTR) bstrSource); _tprintf(_T("\a\tDescription = %s\n"), (LPCTSTR) bstrDescription);}The Guts are this Poke Routinevoid Poke(){ using namespace Excel; _ApplicationPtr pXL; try {// Start Excel 'server' pXL.CreateInstance(L"Excel.Application.9"); pXL->Visible = VARIANT_TRUE; WorkbooksPtr pBooks = pXL->Workbooks;// Make sure you use \\'s else the file can't be find, but you knew that anyway... _WorkbookPtr pBook = pBooks->Open("c:\\willie\\DomSheet.xls"); _WorksheetPtr pSheet = pXL->ActiveSheet; // Say we were here pSheet->Name = "I've been updated";// answer the ultimate question pSheet->Range["A1"]->Value = 42.0;// Save our work pXL->Save(); } catch(_com_error &e) { dump_com_error(e); }}

thanks for the code. I'm using VC6 which unfortunately throws up lots of errors when compiling. I'll give it a try and post the corrections if I can sort it out.

Apparently there are 3 ways to automate Excel from VC++ - MFC, #import, and C/C++. Although not yet working, Dominics #import method seems to be the quickest option when using a DLL.However, referencing using #import can be a pain.Does anyone know how to activate e.g. "Sheet2" before updating a specified cell? Or perhaps another way to update a specific cell of a specific sheet of a specific workbook?Thanks Paul

Im not a expert for C++, but I would like to share a MS VC++ that being called from VBA. Basically, I have a spreadsheet that capture all details of input parameters. Then these parameters will be pass into a C++ program to perform the Monte Carlo Simulation (MCS) and output the result back to the spreadsheet.VBA Code:Option ExplicitDeclare Function mcs Lib _"c:\tb\C++\BasketMCS\Debug\BasketMCS.dll" _(ByVal nDef As Long, dEndDate As Double, dNonDefCF As Double, _ ByVal dNotional As Double, ByVal dDC As Double, ByVal nZeros As Long, _ dZeroTs As Double, dZeros As Double, ByVal nRowDef As Long, _ ByVal nColDef As Long, dCDefTs As Double, dCDefault As Double, _ dCholesky As Double, dRR As Double, ByVal inSim As Long, _ ByVal iSeed As Long, ByVal antithetic As Boolean) As DoubleC++ code:double _stdcall mcs(int nDef, double *EndDate, double *NonDefCF, double Notional, double DC, int nZeros, double *ZeroTs, double *Zeros, int nRowDef, int nColDef, double *CDefTs, double *CDefault, double *cholesky, double *RR, int nSim, int iSeed, bool antithetic) { computation....return output;}U need to create a new text file in yr C++ console application with contents:EXPORTSmcs @1Hope that I provided u with alternative for dll....RgdsRocco

Dominic, using #import do you know how to reference a specific worksheet other than active worksheet?ThanksPaul

Take a walk through the Excel Object Model. You can look up Methods and Properties in your local Excel Help.Regards,Jens.

Thanks Jens but I was trying to find something that would allow me to update a given cell on a specific worksheet (using the #import automation method) ...(this code is ok)pXL.CreateInstance(L"Excel.Application.9");pXL->Visible = VARIANT_TRUE;WorkbooksPtr pBooks = pXL->Workbooks;_WorkbookPtr pBook = pBooks->Open("c:\\willie\\DomSheet.xls");(none of these options work)pBooks->Worksheets("Sheet1")->Activate;pBook->Worksheets("Sheet1")->Activate;_WorksheetPtr pSheet = pBooks->Worksheets("Sheet1");_WorksheetPtr pSheet = pBook->Worksheets("Sheet1");pBook->Worksheets["Sheet1"]->Range["A1"]->Value = 42.0(this code is ok)_WorksheetPtr pSheet = pXL->ActiveSheet;pSheet->Range["A1"]->Value = 42.0;ThanksPaul

Hi,I haven't looked into your code yet. But as a general hint: Go to Excel, open the VB Editor (Alt-F11), enter Application as a "watch expressions" (don't know the exact english title of the window) and click through the hierarchy. Then try to write your code in VBA first, using the code completion and debugging features. If it works: translate it into C++ COM blabla. Or let the VB2VC automation converter handle this task for you.Regards,Jens.

Good tip.The VBA equivalent of my code is easier to get your head around.Another tip is to use the VC++ debug settings to use Excel as the debugging host.There is no reason you can't have VBA debugging going on "under" VC++ debug.If your dealing with COM stuff it can catch stuff like you accidentally calling the wrong methodwhich is easier to cause than you might think/hope. The mappings between types is not always as nice as you would like, and youdo know that true/false have different values under C++ and VB don't you ?I will confess there is another way. DDE.Although I do not advocate it, for really simple interactions it can be the right way to go.DominiConnor

Message me with your Email address and I'll send you the whole project.I tested it under VC6 and it is happy.Dominiconnor

Thanks Dominic please send to: optimization@btinternet.com

Dominic: thanks and please send to optimization@btinternet.comJen: this sounds great but I can't (1) figure how to click through the hierarchy and (2) where to find a VB2VC automation converter (searched MSDN).

futurenets,(1) I can only guess the English titles: Go to Excel (2000), open the VB Editor (Alt-F11), locate the window which allows to add an expression to "watch" (lower right corner, you may need to enable it by clicking an icon with glasses). Right-click and add 'Application' without the '' as expression. Choose "all procedures", "all modules". Expand the entry by clicking the [+] icon in front of it. Watch Excels object model unfold (but do NOT expand 'cells' or similar collections; at least you want to save your work before (2) The automation converter is freeware from MS, but I don't remeber the URL. I can send you a copy if you like.Regards,Jens.