none
interfacing com data into excel RRS feed

  • Question

  • Hi,

    I have a project where i need to receive some data(approx 50 ascii 2 bytes) that comes into a serial com port vis USB and enter it into an excel spread sheet. I have a C++ program that reads the data successfully using Microsoft Foundation classes but requires DLL files to be included with the .exe file. I have Visual Studio ver 8 Standard Edition for development.

    Ideally I would like to have the reading of the data integrated into an Excel application program and have the data put into cells in the spreadsheet. It has to work on variuos versions of Excel and Windows XP or 7.  What is the best simple solution today to do this?  My head is spining looking at the various solutions I see on the web. I am not a PC programmer, but I do embedded C++ programming for micros. ie, I'm looking for simple solutions.

    Thanks,

     

    Wednesday, June 20, 2012 4:20 PM

Answers

  • This get me a bit confused. If you're not PC programmer, I think some concepts in C++ could be hard for you (for example, conversion between string types... C strings and OLE strings are not the same.) 

    If I were you, I may go code in C#/VB.NET, or even VBA scripts to focus on the problem (It's right that VBA can't flexibly read from serial ports, if you rely on samples on the web, you may find enough code to hack up a C++ program that reads serial port, generates corresponding VBA script file, and run cscript.exe to execute it).

    If you insist to go C++, you may find this sample helpful kickstart. The sample uses Office 2003 but after reviewing a bit, it does nothing uncommon so most code there should work across versions.

    Friday, June 22, 2012 3:29 AM
    Answerer

All replies

  • VS2005 comes with PIA for Office 2003/2007. Just add reference to your project to use the earliest version you wish to support.

    A eariler version reference of library can automate a latter version of Office, but may have less feature than what you want to provide. (Say, file format to save for an obvious example.) Check to see if it can support everything you want to do.

    Thursday, June 21, 2012 2:13 AM
    Answerer
  • Cheong00,

    Thanks for the reply. I guess you are addressing the compatibility issue, and I appreciate your response. I however, would like some guidance as to how I would get the data into the spreadsheet. For example I am able to run the c++ program using a macro sub that calls a Shell( the .exe file). But how do I get the data that the .exe program reads into excel cells? Do I have to store the data to a file then have the excel program access that file?  As I said I'm not a PC programmer.

    Thanks,

    Jerry

    Thursday, June 21, 2012 1:56 PM
  • This get me a bit confused. If you're not PC programmer, I think some concepts in C++ could be hard for you (for example, conversion between string types... C strings and OLE strings are not the same.) 

    If I were you, I may go code in C#/VB.NET, or even VBA scripts to focus on the problem (It's right that VBA can't flexibly read from serial ports, if you rely on samples on the web, you may find enough code to hack up a C++ program that reads serial port, generates corresponding VBA script file, and run cscript.exe to execute it).

    If you insist to go C++, you may find this sample helpful kickstart. The sample uses Office 2003 but after reviewing a bit, it does nothing uncommon so most code there should work across versions.

    Friday, June 22, 2012 3:29 AM
    Answerer
  • This get me a bit confused. If you're not PC programmer, I think some concepts in C++ could be hard for you (for example, conversion between string types... C strings and OLE strings are not the same.) 

    If I were you, I may go code in C#/VB.NET, or even VBA scripts to focus on the problem (It's right that VBA can't flexibly read from serial ports, if you rely on samples on the web, you may find enough code to hack up a C++ program that reads serial port, generates corresponding VBA script file, and run cscript.exe to execute it).

    If you insist to go C++, you may find this sample helpful kickstart. The sample uses Office 2003 but after reviewing a bit, it does nothing uncommon so most code there should work across versions.

    Thanks very much. I do have a C++ program that reads the data into varaibles, but then I'm lost as how to get that data into the Excel. For example, I can run the "Myprogram.exe" in excel, by making a Macro;

    Sub test1()

    Dim RetVal

    Retval= Shell("C:\path\Myprogram.exe",1)

    End Sub

    I'll look through the sample you suggested above.

    Friday, June 22, 2012 3:10 PM
  • ???? Can anyone tell me how to interface the C++ variables back into Excel.
    Tuesday, June 26, 2012 2:34 PM
  • I'm not proficient in C++, but seems you can modify CMSWord::OpenDocument() to Open Excel Workbook instead, then call OLEMethod with DISPATCH_METHOD and "get_Range" to the "cell name" (e.g.: "A1") and call the same method with DISPATCH_PROPERTYSET and "Value" to set the value. (Of course passing the reference you got in the previous step is implied.)

    P.S.: Remember to set "Value", you have to convert it to COleVariant first (See sample code in CMSWord::InserPicture() method).

    Wednesday, June 27, 2012 5:50 AM
    Answerer
  • Thanks,

    Is there some example code for this?

    Wednesday, June 27, 2012 8:06 PM
  • Thanks,

    Is there some example code for this?


    Tuesday, July 3, 2012 11:44 AM
  • Humm... I've already told you I'm not proficient in C++, so any code sample I provide here would likely to be buggy. For this reason, I refrain from posting code regarding this question here.

    That said, using my hints provided you should be able to modify code from that article for your own use. If for some reason you can't, I believe you should reconsider your choice of using C++ for this task.

    Wednesday, July 4, 2012 3:16 AM
    Answerer
  • Thanks Cheong00,

    I wasn't specifically asking you for code, I thought maybe someone else would chime in on pointing to some examples. Also, I'm not hard set on using C++. I can use anything that gets the job done. Is VBA better?


    • Edited by jerryls Wednesday, July 11, 2012 2:35 PM
    Tuesday, July 10, 2012 12:55 PM
  • I'd suggest C#, not only that it's my preferred programming language, the two tasks you mentioned are both common task for this language. This means you can found tons of example easily on search engine. (e.g.: Goog^H^H^H^HBing)

    It has SerialPort class that can do most of the things you want from serial port communication. Example on serial port access can be found, for example here.

    And for writing data to Excel, there's also lots of example.

    Thursday, July 12, 2012 1:17 AM
    Answerer