By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,156 Members | 995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,156 IT Pros & Developers. It's quick & easy.

Opening a space delimited textfile in Excel using VB.net

P: n/a
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)

Sep 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Sep 7 '06 #2

P: n/a
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)

Sep 7 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.