"Lee" <hu*********@pacbell.net> schrieb
I have been trying for *many* hours to figure out how to
use the OpenText method in Excel. I have a data file in
text format (ASCII) and I want to import this data into
an Excel worksheet using space delimiting. In other
words, there are spaces between the data. Please refer
to the following code:
Dim app As Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim strFileName1 As String = "C:\Temp\Data1.txt"
Dim strFileName2 As String = "C:\Temp\Data2.xls"
app = CType(CreateObject("Excel.Application"),
Excel.Application)
book = CType(app.Workbooks.Open(strFileName2),
Excel.Workbook)
app.Visible = True
Could someone please tell me how to use the OpenText
method in place of the Open method and make it space
delimited? Everything I have tried imports each line of
the data into the first column instead separating out
into different columns.
Sorry that I didn't answer as promised!
First, here is also the quote from your other thread so that I can refer to
it in this post:
I tried your suggestion but the software tells
me "Expression does not produce a value" for the first
argument of the CType function. I guess I don't know
enough to solve the problem as you suggested.
I copied the code above and it can be compiled without a problem. Now, I add
this line from the other thread:
book = app.Workbooks.OpenText(strFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Space:=True)
After replacing the constant names by their qualified name, the line is:
book = app.Workbooks.OpenText( _
strFileName1, _
Origin:=437, StartRow:=1, _
DataType:=Excel.XlTextParsingType.xlDelimited, _
TextQualifier:=Excel.XlTextQualifier.xlTextQualifi erDoubleQuote, _
ConsecutiveDelimiter:=True, Space:=True _
)
The error message I get is the same as you got: "Expression does not produce
a value". The reason is obvious: OpenText is a sub, not a function. A sub
does not return a value, so there is nothing to assign to 'book'. After
removing 'book =' the correct line is:
app.Workbooks.OpenText( _
strFileName1, _
Origin:=437, StartRow:=1, _
DataType:=Excel.XlTextParsingType.xlDelimited, _
TextQualifier:=Excel.XlTextQualifier.xlTextQualifi erDoubleQuote, _
ConsecutiveDelimiter:=True, Space:=True _
)
As I am not very familiar with the Excel object model, I do not know how to
get a reference to the opened workbook. Maybe it is always the workbook with
the highest index? You have to try this because I also didn't find an answer
anywhere else. I think you should ask in the Excel VBA group because the
question is related to the Excel object model.
--
Armin
http://www.plig.net/nnq/nquote.html http://www.netmeister.org/news/learn2quote.html 
