Strangest error yet! - "method of sheets object failed"

[cfcanter]

I am running the module below from Reflections Basic. I have used similiar modules so I don't think reflections is the problem.

The first time I run the module, it works fine. I then close xl and run the module again I get an error message "method of sheets object failed". If I remove the sheets.select line, the module runs the 1st time and the second time I get a "method " range" of object failed. This same "running once failing twice" error occurs if I close all xl sessions or not. The pattern repeats: 3rd time ok, 4th time fail, 5th time ok...

Any suggestions are appreciated!


Sub OpenXlSlowMove()
Dim xls As Excel.Application
'On Error GoTo ErrorHandler:
' written by C. Canter as stolen from Ali Safavi (hey I think I taught him this!)
' March 20, 2002
' this routine opens the slow moving lot file "slow.dif", adds in a random number,
' and save the file to "slow.xls" to link into MS Access file slow.mdb
'
Set xls = CreateObject("Excel.Application&quot
'
With xls
.Visible = True
DoEvents
.Workbooks.Open "c:\slow_move\slow.dif"

' this sections adds a random number generator for the cycle counting in Access
'
Sheets(&quot;slow&quot.Activate ' <-----here is where the module fails
Range(&quot;a1&quot.Select
ActiveCell.Value = &quot;iRandKey&quot;
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, 1) = &quot;&quot;
ActiveCell.FormulaR1C1 = &quot;=round(rand(),5)&quot;
ActiveCell.Offset(1, 0).Select
Loop
End With
'
' this section saves the file
'
With xls
.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=&quot;C:\slow_move\slow.xls&quot;, FileFormat:= _
xlNormal, password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, ReadOnlyRecommended:=False _
, CreateBackup:=False
.DisplayAlerts = True 'necessary to turn alerts back on
.Workbooks.Close
End With
Set xls = Nothing
Exit Sub
errorhandler:
Select Case Err.Number
Case Is = 10044
MsgBox (&quot;Please close all open xl workbooks with 'Slow.dif' open and then click on 'OK' &quot, vbExclamination + vbOKOnly
Resume
Session.StopMacro
Case Else
MsgBox Err.Description, vbExclamation + vbOKOnly
End Select
Set xls = Nothing
End Sub Craig Canter
Cost Accountant
Sipex
Milpitas, CA

 

[dsi]

Not sure why the code is embedded in the With statment when you are not using the xls object.

Dim xls As Excel.Application
Dim wb As Workbook
dim ws As Worksheet

Set xls = CreateObject("Excel.Application")
xls.Visible = True

Set wb = xls.Workbooks.Open "c:\slow_move\slow.dif"

Set ws = wb.Sheets("slow")

With ws
  .Activate
  .Range("a1").Select
  .ActiveCell.Value = "iRandKey"
  .ActiveCell.Offset(1, 0).Select
  Do Until .ActiveCell.Offset(0, 1) = ""
    .ActiveCell.FormulaR1C1 = "=round(rand(),5)"
        .ActiveCell.Offset(1, 0).Select
    Loop
End With

Hope this helps...