Workbooks.Opentext - oldskool '97

[bradsalmon]

Hi all,

I can't seem to figure this out no matter what I try so I'd really
appreciate if anyone could answer a couple of quick questions on this
bit of code (I know it's old skool now but it needs to be in Office 97)
-

I've got an Access 97 database trying to mess around with a couple of
excel files -
Dim xlApp as object, mysheet as object, mysheet2 as object
set xlApp = CreateObject("Excel.Application")
set mysheet = xlApp.Workbooks.Open("myfile.xls").Sheets(1)
set mysheet2 = xlApp.Workbooks.OpenText("myfile.txt", , 1, xlDelimited,
xlTextQualifierNone, False, False, False, False, False, True, "|",
Array(1,2)).Sheets(1)

but when it gets to mysheets2 I get run time error 424 - Object
Required. Although strangely when I make Excel visible I can see that
it's opened the file. Hence my questions are -
- Can the vba version of Excel open to workbooks at the same time (I
would assume so)?
- Or is the mysheet2 object trying to reference sheet 1 before the file
has been converted?
- Other than that, what am I doing wrong

I want to move forward with -
mysheet2.cells(2,8).Formula = "=A1*A2"
but as mysheet2 doesn't reference anything I can't get past it.

Any help would be much appreciated, thanks,

Brad

[Jim Cone]

Brad,
This is only a guess, try using a separate object variable for
the second workbook...

Dim objWB2 as Excel.Workbook
Dim mysheet2 as Excel.Worksheet

Set objWB2 =xlApp.Workbooks.OpenText("myfile.txt", , 1, xlDelimited,
xlTextQualifierNone, False, False, False, False, False, True, "|",
Array(1,2))
Set mysheet2 = objWB2.Sheets(1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"bradsalmon"
<bradsalmon@yahoo.com>
wrote in message
Hi all,
I can't seem to figure this out no matter what I try so I'd really
appreciate if anyone could answer a couple of quick questions on this
bit of code (I know it's old skool now but it needs to be in Office 97)
-
I've got an Access 97 database trying to mess around with a couple of
excel files -
Dim xlApp as object, mysheet as object, mysheet2 as object
set xlApp = CreateObject("Excel.Application")
set mysheet = xlApp.Workbooks.Open("myfile.xls").Sheets(1)
set mysheet2 = xlApp.Workbooks.OpenText("myfile.txt", , 1, xlDelimited,
xlTextQualifierNone, False, False, False, False, False, True, "|",
Array(1,2)).Sheets(1)

but when it gets to mysheets2 I get run time error 424 - Object
Required. Although strangely when I make Excel visible I can see that
it's opened the file. Hence my questions are -
- Can the vba version of Excel open to workbooks at the same time (I
would assume so)?
- Or is the mysheet2 object trying to reference sheet 1 before the file
has been converted?
- Other than that, what am I doing wrong

I want to move forward with -
mysheet2.cells(2,8).Formula = "=A1*A2"
but as mysheet2 doesn't reference anything I can't get past it.
Any help would be much appreciated, thanks,
Brad

[bradsalmon]

Thanks for the reply Jim but that produces the same error message.

I've also tried making objwb2 a variant, and as in your example an
excel.workbook but no luck there either.

Anyone else have any ideas?

Jim Cone wrote:
> Brad,
> This is only a guess, try using a separate object variable for
> the second workbook...
>
> Dim objWB2 as Excel.Workbook
> Dim mysheet2 as Excel.Worksheet
>
> Set objWB2 =xlApp.Workbooks.OpenText("myfile.txt", , 1, xlDelimited,
> xlTextQualifierNone, False, False, False, False, False, True, "|",
> Array(1,2))
> Set mysheet2 = objWB2.Sheets(1)
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware

[Peter T]

Looks like it should work but doesn't for me either. When assigning an
object variable either to the workbook, or with .Sheets(1) to the sheet the
compiler doesn't seem to recognize "OpenText". Not sure why.

This kludge worked for me

call xlApp.Workbooks.OpenText(arg's etc) 'or without call and no brackets
Set mysheet2 = xlApp.activesheet

Regards,
Peter T

I'm not
"bradsalmon" <bradsalmon@yahoo.com> wrote in message
news:1149854324.196102.278220@g10g2000cwb.googlegroups.com...
>
> Thanks for the reply Jim but that produces the same error message.
>
> I've also tried making objwb2 a variant, and as in your example an
> excel.workbook but no luck there either.
>
> Anyone else have any ideas?
>
> Jim Cone wrote:
> > Brad,
> > This is only a guess, try using a separate object variable for
> > the second workbook...
> >
> > Dim objWB2 as Excel.Workbook
> > Dim mysheet2 as Excel.Worksheet
> >
> > Set objWB2 =xlApp.Workbooks.OpenText("myfile.txt", , 1, xlDelimited,
> > xlTextQualifierNone, False, False, False, False, False, True, "|",
> > Array(1,2))
> > Set mysheet2 = objWB2.Sheets(1)
> > --
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
>

[bradsalmon]

Fantastic - that works a treat.

Thanks for the help Peter, I didn't realise you could use call in that
way.