Workbooks.OpenText does not work on XP. HELP !!!

[richmarin]

I am using Excel 2002 and Windows XP.

I have an Excel Macro that works just fine in Excel 97. Now our
department has Excel 2002 on XP platform. The same macro that worked
before, does not work now.

As a test, I recorded a key stroke macro using Excel 2002 on XP. Then I
ran the key stroke macro in VBA mode. It crashed. Below is the line of
code. For whatever reason, Excel 2002 on Windows XP does not like
Workbooks.OpenText

Workbooks.OpenText Filename:=lcFilename2, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2),
Array(2, 5), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7,
2), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 5), Array(15, 5), Array( _
16, 2), Array(17, 2), Array(18, 2), Array(19, 1))

Can anyone help ???

...

 

[Jake Marx]

Hi,

When you say "It crashed", do you mean that an application error was raised
and Excel actually shut down? Or did you get an "method OpenText of object
Workbooks failed" runtime error?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[richmarin]

I got a "method OpenText of object Workbooks failed" runtime error.

I saw similar posts to mine. But, I did not see a solution. Strange.
The command works in Excel 97. If I use the user interface and bring in
a text file in manually it works. But, Excel 2002 can not handle
OpenText.

I don't know if the problem is XP or Excel 2002. And I need a solution
quick cause I am under a tight deadline.

 

[Tom Ogilvy]

Your code worked fine for me in xl2003

Sub ABC()
lcFilename2 = "C:\" & Dir("c:\*.txt")
Workbooks.OpenText Filename:=lcFilename2, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 5), _
Array(3, 2), Array(4, 2), Array(5, 2), _
Array(6, 2), Array(7, 2), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 5), _
Array(15, 5), Array(16, 2), Array(17, 2), _
Array(18, 2), Array(19, 1))
End Sub

I don't have xl2002 handy, but I don't see why there would be a problem.

 

[Jake Marx]

Hi Rich,

I got a "method OpenText of object Workbooks failed" runtime error.

I saw similar posts to mine. But, I did not see a solution. Strange.
The command works in Excel 97. If I use the user interface and bring
in a text file in manually it works. But, Excel 2002 can not handle
OpenText.

I don't know if the problem is XP or Excel 2002. And I need a solution
quick cause I am under a tight deadline.

I tested your exact code in XL 2002 and XL 2003, and it worked in both
places for me.

Where is your text file located - on a local drive or on a network drive?
How big is the text file (how many lines)?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[richmarin]

My Excel macro and textfile is located on a network drive. I hope this
helps.

Hi Rich,

I got a "method OpenText of object Workbooks failed" runtime error.

I saw similar posts to mine. But, I did not see a solution. Strange.
The command works in Excel 97. If I use the user interface and bring
in a text file in manually it works. But, Excel 2002 can not handle
OpenText.

I don't know if the problem is XP or Excel 2002. And I need a solution
quick cause I am under a tight deadline.

I tested your exact code in XL 2002 and XL 2003, and it worked in both
places for me.

Where is your text file located - on a local drive or on a network drive?
How big is the text file (how many lines)?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

Rich,

My Excel macro and textfile is located on a network drive. I hope this
helps.

Does it work if either or both of the files are on a local drive?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]