transfering data from vb to excel

Posted on 2003-02-19
Medium Priority
Last Modified: 2010-04-07
I am currently writing a control program in vb, which reads values from an interface board and then runs a program in a loop (a continuous loop). However, for the purposes of documenting the inputs from the sensors I want to intermittently write the input valus to an excel (or a text file) for the purposes of producing graphs and performing futher analysis etc. I therefore was hoping someone could advise me about how to write the values, and the time (when the measurements were taken). Whether this could be done individually when the measurements were taken, or whether the values would have to be held in a temporary table before transfer would also be much appreciated...
P.S. as you may have realised I am new to programming...
Question by:bdprgrmr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

85Ybody earned 100 total points
ID: 7983363
This should get you started on writing to a cell. Make references to Microsoft Excel Object Library.

Create a form with a text box and a command button, leave names as default names. Insert text into textbox and hit command1.

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Private Sub Command1_Click()
On Error Resume Next

Set xlApp = GetObject("Excel.Application") 'if excel is open
If Error <> 0 Then
    Set xlApp = CreateObject("Excel.Application") 'if excel is not open
End If
xlApp.Visible = True 'shows excel

Set xlWB = xlApp.Workbooks.Open("D:\Temp\1234.XLS")
Set xlWS = xlWB.ActiveSheet

xlWS.Cells(1, 1).Value = Text1.Text 'populates cell A1 with text from your box

Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

End Sub

Assisted Solution

Taconvino earned 100 total points
ID: 7984168
That is the correct way of doing it, but there is another one. Try this:

Do while X    '<--- this is your loop sentence
     Open "c:\ListOfValues.xls" For Append As #1
     Print #1, VarHoldingValue & vbTab & Now
     Close #1

This creates plain text file, with two columns of values separated by a "Tab" character.  Excel assumes this as a CSV file (comma separated values), and neatly places each column in the respective columns of the work sheet.

Hope this helps!


Assisted Solution

dmang earned 100 total points
ID: 7989550
depending on the amount of sampling output you are creating, you may want to capture the timestamp before the output loop to provide a consistent basis for later analysis of all samples generated during a particular cycle.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

ID: 8627236
Solved your problem yet?

Expert Comment

ID: 8901430
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 49

Expert Comment

ID: 8959689
Moderator, my recommended disposition is:

    Split points between: 85Ybody and Taconvino and dmang

DanRollins -- EE database cleanup volunteer

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question