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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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
PaulHewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.