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 |
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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 09:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com