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

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 ???

...

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@earthlink.net wrote:
> 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 ???
>
> ..

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.

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.

--
Regards,
Tom Ogilvy

<richmarin@earthlink.net> wrote in message
news:1117201697.605436.159900@o13g2000cwo.googlegroups.com...
>
> 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.
>

Hi Rich,

richmarin@earthlink.net wrote:
> 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]

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


Jake Marx wrote:
> Hi Rich,
>
> richmarin@earthlink.net wrote:
> > 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]

Rich,

richmarin@earthlink.net wrote:
> 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]