?
Solved

Displaying strings of data in Excel

Posted on 2007-07-25
9
Medium Priority
?
164 Views
Last Modified: 2010-04-30
Hi, I need to save strings of data (numbers) in Excel using Visual Basic. This is what the data looks like:
First Set of data Saved: (First Row)
10.24   100.4   230.45   301.45   75.2   20.56   39.47
   
Second Set of data Saved: (Second Row)
About the same as first set.

Third Set of data Saved (Third Row)
About the same as first.
:
:(N+1 Row)

The data is being saved on a excel file using:
     LogFile = FreeFile
    Open "C:\Log\AllData.xls" For Append As #LogFile
    Print #LogFile, Message
    Close #LogFile

The issue I have is that the data does not get organized into columns and rows, per every set of data saved. Can anyone please tell me how to organized the data sent into columns and rows? The strings of data will always be the same length. About 8 pieces of data per string. Which means eight columns and one row per string.

Thank you,


0
Comment
Question by:caleno
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:Clif
ID: 19567922
What is the value of Message in your code?
0
 

Author Comment

by:caleno
ID: 19568388
Clif,
Message is a String which contains the data to be saved. The data in Message will always look like this:

Message = 10.32 11.05 425.34 94 492.34 190.23 936.83 220.23     (8 sets of numbers each separated  
                                                                                                           by a space)
Then that data will be saved on a excel spreadsheet. When the next set of data comes in, it will be saved on the next row of the spreadsheet and so forth. Is this what you are looking for?
0
 
LVL 10

Expert Comment

by:Clif
ID: 19568471
Yes.  If I understand your question, I believe, instead of spaces, you should be separating the values with tabs.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:caleno
ID: 19568537
Clif, your suggestion may work, but how do I do that? Is there any code? I am not an expert on this. Please be specific.

Thanks
0
 
LVL 10

Expert Comment

by:Clif
ID: 19568606
Well, it depends on how the Message variable is built.

If your code is building it, then simply replace the space with chr$(9) (or vbTab)

If it's already built, or you don't want to modify it, then simply create a new variable and use VB's "Replace()" function to change the spaces to tabs:

    LogFile = FreeFile
    Open "C:\Log\AllData.xls" For Append As #LogFile
    Dim sNewVariable As String
    sNewVariable = Replace(Message, " ", Chr$(9))
    Print #LogFile, sNewVariable
    Close #LogFile
0
 

Author Comment

by:caleno
ID: 19569369
Clif, that worked very good. I had to use the Replace fucntion method. When I was doing the test. I noticed I forgot to mention something. At the top (only) of each colum I need to place a label (name), which differentiates each column. Could you tell me how to do that? For instance:

Col1               Col2           Col3                Col4          ....... Column number
Monday      Tuesday     Wednes.       Thursday      ....... Column label (name)

Thank you
0
 
LVL 10

Expert Comment

by:Clif
ID: 19569539
That would be quite simple.

If the example is exactly what you need, it's even more simple.
This will produce two variables containing the example rows from your last post:
Dim i As Integer
Dim sHead1 As String
Dim sHead2 As String
For i = 1 To 7
    sHead1 = sHead1 & "Col" & Trim$(Str$(i))
    sHead2 = sHead2 & WeekdayName(i, False, vbMonday)
    If i < 7 Then
        sHead1 = sHead1 & Chr$(9)
        sHead2 = sHead2 & Chr$(9)
    End If
Next i

Now, on the other hand, if the column names are different than your example above, while still fairly easy, I can't give you specific code, since I don't know what your column names actually are.  However, using your example as a starting place, the code might look something like this:

Dim sHead1 As String
Dim sHead2 As String
sHead1 = "Col1" & Chr$(9) & "Col2" & Chr$(9) & "Col3" & Chr$(9) & "Col4" 'And so forth
sHead2 = "Monday" & Chr$(9) & "Tuesday" & Chr$(9) & "Wednesday" & Chr$(9) & "Thursday" 'And so forth

Once you have sHead1 and sHead2 completed, you can write them to the file as you do with Message.
0
 

Author Comment

by:caleno
ID: 19571426
Clif, I have not tried the code yet. I think I could use this portion of the code for my purposes since the columns names are not the days of week.

Dim sHead1 As String
Dim sHead2 As String
sHead1 = "Col1" & Chr$(9) & "Col2" & Chr$(9) & "Col3" & Chr$(9) & "Col4" 'And so forth
sHead2 = "Monday" & Chr$(9) & "Tuesday" & Chr$(9) & "Wednesday" & Chr$(9) & "Thursday" 'And so forth
However, you said once I have this completed I could write them to the file as with Message.
That sounds like sHead1 and sHead2 are going to be saved everytime I save message. Is that right? If so, that is not what I need. I need to show a table (excel) with the title of the colums at the top (1st row) and then my data (Message) will follow down the table. So the names of the colums will need to be saved or shown only once, at the first row of my table. Its your suggestion doing that? You are very knowledgable on VB6 I have some other questions I will be posting later on.
Thanks
0
 
LVL 10

Accepted Solution

by:
Clif earned 200 total points
ID: 19573720
To write the headings only at the top of the file, a line of code testing whether or not "C:\Log\AllData.xls" exists, and then writing sHead1 and sHead2 if it doesn't should be what you need.

Add this code:
Dim bExists As Boolean
bExists = Dir("C:\Log\AllData.xls") <> ""
If Not bExists Then
     LogFile = FreeFile
    Open "C:\Log\AllData.xls" For Append As #LogFile
    sHead1 = "Col1" & Chr$(9) & "Col2" & Chr$(9) & "Col3" & Chr$(9) & "Col4" 'And so forth
    sHead2 = "Monday" & Chr$(9) & "Tuesday" & Chr$(9) & "Wednesday" & Chr$(9) & "Thursday" 'And so forth
    Print #LogFile, sHead1
    Print #LogFile, sHead2
    Close #LogFile
End If
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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

840 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