Fero45
asked on
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
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
Jorge Paulino
Why don't you use the excel automation from VB to write in the right columns/rows ?
ASKER
to jpaulino
well, I would use it if it helps, I do not know how. Please, can you be more specific.
well, I would use it if it helps, I do not know how. Please, can you be more specific.
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.
https://www.experts-exchange.com/questions/23127655/How-do-you-Export-Data-from-a-ListViewItem-to-Excel-using-VB-NET.html
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.
https://www.experts-exchange.com/questions/23127655/How-do-you-Export-Data-from-a-ListViewItem-to-Excel-using-VB-NET.html
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.
"This is the first data column, it contains a comma and is in quotation marks", "Second data column", ... etc.
You're right PaulHews. Also a easy text to columns in excel solve the problem.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> Yes, that's true, but not ideal.
Agree
Agree
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 :-)
>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.
Agreed here, that's the big advantage of automation over a text file.
ASKER
to PaulHeuws
oooh maaaan, it is Excel excellent. Thank you :-)
I just have to learn every day.
Fero
oooh maaaan, it is Excel excellent. Thank you :-)
I just have to learn every day.
Fero
ASKER
It worked perfectly.