modifying inactive sheets

[unxzst]

how do I write to inactive sheets in VB6? instead of activating, i'd like to keep one sheet on top, while writing data to sheets at random.

Set oExcel = CreateObject("Excel.Application")
For N = 1 To num
        SheetName = "Sheet" + CStr(N)
        'oExcel.Sheets(SheetName).Select
        oExcel.Sheets(SheetName).Name = CStr("Layer " + CStr(N))
        Set therange = oExcel.Range(oExcel.sheets(SheetName).Cells(1, 1), oExcel.sheets(SheetName).Cells(12, 6)) 'this gives me runtime error 9 - subscript out of range
        therange.Value = thearray
        oExcel.Sheets(SheetName).Columns("A:A").EntireColumn.AutoFit
Next N

or should I be going this with WorkBook object?
thanks for helping me!

[si_the_geek]

If you want to work with a particular Workbook, you should always specify that - and the same goes for a WorkSheet.

The Application can contain multiple Workbooks, each of which contains WorkSheets, each of which contains Cells/Ranges/Columns/etc. In many cases you aren't specifying the Workbook (and in some cases not the sheet either), so you can't tell which will be used - in effect, Excel will make an educated guess, which is likely to be wrong (and is the cause of your problem).

For example, by using oExcel.Range, you have missed out 2 objects (the WorkBook and the WorkSheet), so you don't even know which Workbook it will be working with, let alone which WorkSheet.

Whether or not you create extra variables for the WorkBook and Sheet(s) is a matter of choice, but it is what I always do as it makes the code simpler and faster (and only slightly longer).

I have no idea how you are currently creating/setting/opening the workbook, but something like this should work:

Set oExcel = CreateObject("Excel.Application")
Dim oBook as Object
Dim oSheet as Object
Set oBook = oExcel.WorkBooks.Open(filename)  'change as apt!
For N = 1 To num
        SheetName = "Sheet" + CStr(N)
        Set oSheet = oBook.Sheets(SheetName)
        oSheet.Name = CStr("Layer " + CStr(N))
        Set therange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(12, 6))
        therange.Value = thearray
        oSheet.Columns("A:A").EntireColumn.AutoFit
Next N

For more advice/examples (including how to close/release the variables appropriately), see my Excel tutorial (link in my signature).

[unxzst]

cool, thanks!
I'm working with a single workbook, so I havn't concerned myself with that yet, but thanks for pointing it out.
to open my workbook i do this:

Set oExcel = CreateObject("Excel.Application")    
oExcel.SheetsInNewWorkbook = num
Set oWB = oExcel.Workbooks.Add