Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Opentext - oldskool '97
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Opentext - oldskool '97
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Opentext - oldskool '97
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Opentext - oldskool '97
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" wrote in message oups.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Opentext - oldskool '97
Fantastic - that works a treat. Thanks for the help Peter, I didn't realise you could use call in that way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbooks.OpenText problem in XL2000 | Excel Programming | |||
Workbooks.OpenText StartRow:=2 Not Skipping Row 1 | Excel Programming | |||
Workbooks.OpenText does not work on XP. HELP !!! | Excel Programming | |||
How to open UTF-8 files with Workbooks.OpenText | Excel Programming | |||
Workbooks.OpenText | Excel Programming |