Shop OBEX P1 Docs P2 Docs Learn Events
Getting data into Excel — Parallax Forums

Getting data into Excel

ArchiverArchiver Posts: 46,084
edited 2001-06-24 05:21 in General Discussion
Hi folks,

I am working on a system that takes real time data from a stamp and then
interfaces to a standard x86 box via the serial port. I want to get the
information into MS Excel spreadsheet. I'm pretty good at writing macros in
Excel and I have VisualBasic5 Pro. The question is: what is the most
reasonable way to get small amounts of data from a VB5 program into Excel
automatically.

The ways I am looking at are:
1. Use the clipboard
2. code a VB5 dll and use a call from Excel (not sure about this, never done
it).
3. Use OLE/activex and getobject (again, not sure about this, never done it).

Ideas would be much appreciated.

Thanks,
Scott.

Comments

  • ArchiverArchiver Posts: 46,084
    edited 2001-06-23 03:34
    Simply....
    save the file as a ".csv" (comma separated values) file. eg. "myfile.csv".

    The .csv extension automatically gives the file an excell icon.

    Try this, in notepad exactly as follows...then save it as "sample.csv"
    Then from 'Documents' menu, double click it to open the file in excell..
    Regards, Chris


    Heading,22/06/2000

    Col 1,Col2,Col3,Col4
    3,5,15,6789
    3.76,22389,67



    Original Message
    From: <scottsmith13@a...>
    To: <basicstamps@yahoogroups.com>
    Sent: Saturday, June 23, 2001 9:41 AM
    Subject: [noparse][[/noparse]basicstamps] Getting data into Excel


    > Hi folks,
    >
    > I am working on a system that takes real time data from a stamp and then
    > interfaces to a standard x86 box via the serial port. I want to get the
    > information into MS Excel spreadsheet. I'm pretty good at writing macros
    in
    > Excel and I have VisualBasic5 Pro. The question is: what is the most
    > reasonable way to get small amounts of data from a VB5 program into Excel
    > automatically.
    >
    > The ways I am looking at are:
    > 1. Use the clipboard
    > 2. code a VB5 dll and use a call from Excel (not sure about this, never
    done
    > it).
    > 3. Use OLE/activex and getobject (again, not sure about this, never done
    it).
    >
    > Ideas would be much appreciated.
    >
    > Thanks,
    > Scott.
    >
    > To UNSUBSCRIBE, just send mail to:
    > basicstamps-unsubscribe@yahoogroups.com
    > from the same email address that you subscribed with. Text in the Subject
    and Body of the message will be ignored.
    >
    >
    > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
    >
    >
  • ArchiverArchiver Posts: 46,084
    edited 2001-06-23 04:22
    Hi Chris,

    Thanks for the idea, but the data has to be transfered to Excel in real time
    and without user intervention. Excel has a looped macro that formats and
    displays the information (also in real time).

    Regards,
    Scott

    > Simply....
    > save the file as a ".csv" (comma separated values) file. eg. "myfile.csv".
    >
    > The .csv extension automatically gives the file an excell icon.
    >
    > Try this, in notepad exactly as follows...then save it as "sample.csv"
    > Then from 'Documents' menu, double click it to open the file in excell..
    > Regards, Chris
    >
  • ArchiverArchiver Posts: 46,084
    edited 2001-06-23 04:30
    Hi,
    How about _appending_ the data it to an existing text file.
    Excel would be pre-linked to that text file via ODBC. You
    would need a macro in excel that ran on a timer interval to
    regularly refresh the query...Bet a paycheck that will work...

    Ken Ambrose

    with enough thrust, pigs fly just fine.



    ---- On Fri, 22 Jun 2001, scottsmith13@a...
    (scottsmith13@a...) wrote:

    > Hi Chris,
    >
    > Thanks for the idea, but the data has to be transfered to
    Excel in real
    > time
    > and without user intervention. Excel has a looped macro that
    formats and
    >
    > displays the information (also in real time).
    >
    > Regards,
    > Scott
    >
    > > Simply....
    > > save the file as a ".csv" (comma separated values) file.
    eg.
    > "myfile.csv".
    > >
    > > The .csv extension automatically gives the file an excell
    icon.
    > >
    > > Try this, in notepad exactly as follows...then save it as
    > "sample.csv"
    > > Then from 'Documents' menu, double click it to open the
    file in
    > excell..
    > > Regards, Chris
    > >
    >
    > To UNSUBSCRIBE, just send mail to:
    > basicstamps-unsubscribe@yahoogroups.com
    > from the same email address that you subscribed with. Text
    in the
    > Subject and Body of the message will be ignored.
    >
    >
    > Your use of Yahoo! Groups is subject to
    > http://docs.yahoo.com/info/terms/
    >
    >
    >
    >
  • ArchiverArchiver Posts: 46,084
    edited 2001-06-23 11:55
    You can control Excel with Visual Basic, insert your data, plot etc. I
    recently did a program that processes raw data files and beautifies them
    in Excel, all from the VB executable. You could modify this technique to
    give the desired effect. I can send you my source if you'd like, and a
    sample data file.

    Stew Benedict

    The power in technology lies in getting it to
    work the way you do, we can help.
    Stew Benedict *** AYS Enterprises *** stewb@c...
    http://www.crosswinds.net/~stewb ICQ: 18063833
    http://www.aysenterprises.com Phone/Fax: 216-228-3983

    On Fri, 22 Jun 2001, Ken Ambrose wrote:

    > Hi,
    > How about _appending_ the data it to an existing text file.
    > Excel would be pre-linked to that text file via ODBC. You
    > would need a macro in excel that ran on a timer interval to
    > regularly refresh the query...Bet a paycheck that will work...
    >
    > Ken Ambrose
    >
    > with enough thrust, pigs fly just fine.
    >
    >
    >
    > ---- On Fri, 22 Jun 2001, scottsmith13@a...
    > (scottsmith13@a...) wrote:
    >
    > > Hi Chris,
    > >
    > > Thanks for the idea, but the data has to be transfered to
    > Excel in real
    > > time
    > > and without user intervention. Excel has a looped macro that
    > formats and
    > >
    > > displays the information (also in real time).
    > >
    > > Regards,
    > > Scott
    > >
    > > > Simply....
    > > > save the file as a ".csv" (comma separated values) file.
    > eg.
    > > "myfile.csv".
    > > >
    > > > The .csv extension automatically gives the file an excell
    > icon.
    > > >
    > > > Try this, in notepad exactly as follows...then save it as
    > > "sample.csv"
    > > > Then from 'Documents' menu, double click it to open the
    > file in
    > > excell..
    > > > Regards, Chris
    > > >
    > >
    > > To UNSUBSCRIBE, just send mail to:
    > > basicstamps-unsubscribe@yahoogroups.com
    > > from the same email address that you subscribed with. Text
    > in the
    > > Subject and Body of the message will be ignored.
    > >
    > >
    > > Your use of Yahoo! Groups is subject to
    > > http://docs.yahoo.com/info/terms/
    > >
    > >
    > >
    > >
    >
    >
    > To UNSUBSCRIBE, just send mail to:
    > basicstamps-unsubscribe@yahoogroups.com
    > from the same email address that you subscribed with. Text in the Subject and
    Body of the message will be ignored.
    >
    >
    > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
    >
    >
    >
  • ArchiverArchiver Posts: 46,084
    edited 2001-06-23 15:14
    scottsmith13@a... wrote:
    >
    > The ways I am looking at are:
    > 1. Use the clipboard
    > 2. code a VB5 dll and use a call from Excel (not sure about this, never done
    > it).
    > 3. Use OLE/activex and getobject (again, not sure about this, never done it).
    >
    If you want to add and alter the data in real time, your best bet
    would be #3. If I remember correctly, you should be able to do
    anything with OLE that you would normally do via the user interface.
    For example, you could have your VB app open Excel, get a saved
    spreadsheet, set the values of specific cells, and close it again.

    Here's an example from Microsoft:

    http://support.microsoft.com/support/kb/articles/Q112/4/17.ASP

    SD

    --
    /===================================================================\
    | Steve DeGroof (http://degroof.home.mindspring.com/) |
    \===================================================================/
  • ArchiverArchiver Posts: 46,084
    edited 2001-06-24 05:21
    Gotcha...
    Original Message
    From: <scottsmith13@a...>
    To: <basicstamps@yahoogroups.com>
    Sent: Saturday, June 23, 2001 11:22 AM
    Subject: Re: [noparse][[/noparse]basicstamps] Getting data into Excel


    > Hi Chris,
    >
    > Thanks for the idea, but the data has to be transfered to Excel in real
    time
    > and without user intervention. Excel has a looped macro that formats and
    > displays the information (also in real time).
    >
    > Regards,
    > Scott
    >
    > > Simply....
    > > save the file as a ".csv" (comma separated values) file. eg.
    "myfile.csv".
    > >
    > > The .csv extension automatically gives the file an excell icon.
    > >
    > > Try this, in notepad exactly as follows...then save it as "sample.csv"
    > > Then from 'Documents' menu, double click it to open the file in
    excell..
    > > Regards, Chris
    > >
    >
    > To UNSUBSCRIBE, just send mail to:
    > basicstamps-unsubscribe@yahoogroups.com
    > from the same email address that you subscribed with. Text in the Subject
    and Body of the message will be ignored.
    >
    >
    > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
    >
    >
Sign In or Register to comment.