Opening a space delimited textfile in Excel using VB.net

[Jacob.Bruxer]

Hi everyone,
I'm having trouble opening space delimited textfiles in Excel from
Visual Basic.net. I'm able to control, open and modify Excel files
from Visual Basic, so my Excel reference seems to be working ok. I
just don't know what I'm doing when I try to open a space delimited
file and want to put the data into columns. I tried recording a macro
in VBA and going from there, but then I got stuff saying "Name
'xlDelimited' is not declared", etc. So then I started throwing
'Microsoft.Office.Interop.Excel.XLTextParsingType. xlDelimited' in front
because I saw that online somewhere, and things looked like they might
work, but alas, they did not. So here I am. If you haven't figured it
out yet, I'm not a programmer and I'm pretty new to VB, so I would
really appreciate some help on this. I've pasted the not-so-pretty
code I've attempted below solely for your amusement. To be honest, I'm
not even sure what about 90% of it means. With this code I get an
error saying something like "Expression does not produce a value".
Thanks for any help offered.

Dim oExcel As New Microsoft.Office.Interop.Excel.Application
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBook = oExcel.Workbooks.OpenText("C:\TextTest.txt",
Origin:=437, StartRow:=1,
DataType:=Microsoft.Office.Interop.Excel.XlTextPar singType.xlDelimited,
TextQualifier:=Microsoft.Office.Interop.Excel.XlTe xtQualifier.xlTextQualifierDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Microsoft.Office.Interop.Excel.XlColumn DataType.xlGeneralFormat,
TextVisualLayout:=Microsoft.Office.Interop.Excel.X lTextVisualLayoutType.xlTextVisualLTR,
DecimalSeparator:=".", ThousandsSeparator:=",",
TrailingMinusNumbers:=True)

[Jacob.Bruxer]

Wow. I might have solved my problem accidentally. By taking out the
"oBook =" in front of everything (and just using oExcel as my object),
the text file opened just fine, even without all the mumbo-jumbo after
the filename. My code's this now...

Dim oExcel As New Microsoft.Office.Interop.Excel.Application

oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.Workbooks.Open("C:\TextTest.txt")

I assume then that I must just not be dimensioning something correctly.
I wonder why the program knows it's a space delimited file, since as
you can see I haven't stated that anywhere in my code. Thoughts?
Ja**********@ec.gc.ca wrote:

Hi everyone,
I'm having trouble opening space delimited textfiles in Excel from
Visual Basic.net. I'm able to control, open and modify Excel files
from Visual Basic, so my Excel reference seems to be working ok. I
just don't know what I'm doing when I try to open a space delimited
file and want to put the data into columns. I tried recording a macro
in VBA and going from there, but then I got stuff saying "Name
'xlDelimited' is not declared", etc. So then I started throwing
'Microsoft.Office.Interop.Excel.XLTextParsingType. xlDelimited' in front
because I saw that online somewhere, and things looked like they might
work, but alas, they did not. So here I am. If you haven't figured it
out yet, I'm not a programmer and I'm pretty new to VB, so I would
really appreciate some help on this. I've pasted the not-so-pretty
code I've attempted below solely for your amusement. To be honest, I'm
not even sure what about 90% of it means. With this code I get an
error saying something like "Expression does not produce a value".
Thanks for any help offered.

Dim oExcel As New Microsoft.Office.Interop.Excel.Application
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBook = oExcel.Workbooks.OpenText("C:\TextTest.txt",
Origin:=437, StartRow:=1,
DataType:=Microsoft.Office.Interop.Excel.XlTextPar singType.xlDelimited,
TextQualifier:=Microsoft.Office.Interop.Excel.XlTe xtQualifier.xlTextQualifierDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Microsoft.Office.Interop.Excel.XlColumn DataType.xlGeneralFormat,
TextVisualLayout:=Microsoft.Office.Interop.Excel.X lTextVisualLayoutType.xlTextVisualLTR,
DecimalSeparator:=".", ThousandsSeparator:=",",
TrailingMinusNumbers:=True)

[Jeff Glatt]

Hi Jacob,

Would you like to try OrchidGrid, which can parse/import delimited text
files and transfer/export the data to Excel?

Check this,
http://www.springsys.com/netproducts/orchid.asp

Hi everyone,
I'm having trouble opening space delimited textfiles in Excel from
Visual Basic.net. I'm able to control, open and modify Excel files
from Visual Basic, so my Excel reference seems to be working ok. I
just don't know what I'm doing when I try to open a space delimited
file and want to put the data into columns. I tried recording a macro
in VBA and going from there, but then I got stuff saying "Name
'xlDelimited' is not declared", etc. So then I started throwing
'Microsoft.Office.Interop.Excel.XLTextParsingType. xlDelimited' in front
because I saw that online somewhere, and things looked like they might
work, but alas, they did not. So here I am. If you haven't figured it
out yet, I'm not a programmer and I'm pretty new to VB, so I would
really appreciate some help on this. I've pasted the not-so-pretty
code I've attempted below solely for your amusement. To be honest, I'm
not even sure what about 90% of it means. With this code I get an
error saying something like "Expression does not produce a value".
Thanks for any help offered.

Dim oExcel As New Microsoft.Office.Interop.Excel.Application
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBook = oExcel.Workbooks.OpenText("C:\TextTest.txt",
Origin:=437, StartRow:=1,
DataType:=Microsoft.Office.Interop.Excel.XlTextPar singType.xlDelimited,
TextQualifier:=Microsoft.Office.Interop.Excel.XlTe xtQualifier.xlTextQualifierDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Microsoft.Office.Interop.Excel.XlColumn DataType.xlGeneralFormat,
TextVisualLayout:=Microsoft.Office.Interop.Excel.X lTextVisualLayoutType.xlTextVisualLTR,
DecimalSeparator:=".", ThousandsSeparator:=",",
TrailingMinusNumbers:=True)