Open text file in Excel from Shell with parameters in VB.NET

Hi experts

I built a small program in VB.NET that saves data in a pipe delimited text file. The last line of my VB.NET code is
Shell("C:\Program Files\Microsoft Office\Office11\EXCEL.exe " & textFile, AppWinStyle.NormalFocus)

Everything works fine. Excel opens the file but the whole file is in one column. How do I set parameters in VB, e.g.. delimiter so that MS Excel automatically could open the file and set correct columns?
Thanks
Fero

LVL 6
Fero45Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PaulHewsConnect With a Mentor Commented:
Sample code to write a comma separated file, and display it in Excel.

>Also a easy text to columns in excel solve the problem.

Yes, that's true, but not ideal.
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Cols() As String = {"This,is,first", "This,is,second", "This,is,third"}
        Dim sb As New System.Text.StringBuilder
        For i As Integer = 0 To 9
            For j As Integer = 0 To Cols.GetUpperBound(0)
                sb.Append(Chr(34) & Cols(j) & Chr(34))
                If j < Cols.GetUpperBound(0) Then
                    sb.Append(",")
                End If
            Next
            sb.AppendLine("")
        Next
        Dim Filename As String = "C:\temp\test.csv"
        My.Computer.FileSystem.WriteAllText(filename, sb.ToString, False)
        Process.Start(Filename)
 
    End Sub
End Class

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Why don't you use the excel automation from VB to write in the right columns/rows ?
0
 
Fero45Author Commented:
to jpaulino

well, I would use it if it helps, I do not know how.  Please, can you be more specific.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jorge PaulinoIT Pro/DeveloperCommented:
Well you have already the data and you know how to split to write on the columns/rows ?

Here you have an example that show how to export from a listview to Excel. All you have to do is to apply the same method replacing the listview data with you data.
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23127655.html
0
 
PaulHewsCommented:
It may also be worth noting that a comma separated file (.csv) is an easier file type to open automatically in Excel.  It will be essentially the same thing as your pipe delimited file, but using commas instead of pipes to delimit the columns.  If your data can contain commas, then surround each data field with quotation marks.

"This is the first data column, it contains a comma and is in quotation marks", "Second data column", ... etc.

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You're right PaulHews. Also a easy text to columns in excel solve the problem.
0
 
Fero45Author Commented:
the thing is, a csv file with commas and quotation marks will automatically read into one column in Excel too. Since the piece of software where I used Shell (the only purpose being to save the people here at work a few mouse clicks) should be used by more people even outside so I cannot write any VBA code for them
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
>> Yes, that's true, but not ideal.

Agree
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
But using the excel automation you can "work" the excel file, include header, bolting the header, general formatting, etc. Of couse he may not need it :-)
0
 
PaulHewsCommented:
>But using the excel automation you can "work" the excel file, include header, bolting the header, general formatting, etc.

Agreed here, that's the big advantage of automation over a text file.

0
 
Fero45Author Commented:
to PaulHeuws

oooh maaaan, it is Excel excellent. Thank you :-)
I just have to learn every day.

Fero
0
 
Fero45Author Commented:
It worked perfectly.
0
All Courses

From novice to tech pro — start learning today.