OpenText method in Excel

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

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.

Thank you in advance,
Lee
 
Lee said:
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.
Click to expand...

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.
Click to expand...


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.xlTextQualifierDoubleQuote, _
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.xlTextQualifierDoubleQuote, _
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.
 
That did it!!!!!!
Thank you sooooo much Armin.
-Lee
-----Original Message-----


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 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, _
Click to expand...
TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDouble
Quote, _
ConsecutiveDelimiter:=True, Space:=True _
)

The error message I get is the same as you
Click to expand...
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, _
Click to expand...
TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDouble
Quote, _
 
Back
Top