Run-time error '91' when Range initialization run twice!! 1

[yeungsprite]

This should be an easy question, but as a novice programmer, I am not sure how to solve it. If I have the simple code below:


Private Sub Command1_Click()
Dim ExcelApp As Excel.Application 'Excel-object

Dim colRange As Range ' Column in which to read values from
Dim i As Range ' value of the current cell


Set ExcelApp = New Excel.Application
ExcelApp.Application.Visible = True

'Initialize and show Excel application and Import file
ExcelApp.Workbooks.Open "C:\somefile"
Set colRange = ActiveSheet.Range("A1", ActiveSheet.Cells.SpecialCells(xlLastCell)) 'error appears here

For Each i In colRange

Next i

'Disable 'save on close' dialog boxes
ExcelApp.ActiveWorkbook.Saved = True

ExcelApp.Workbooks.Close

'end application
ExcelApp.Quit
Set ExcelApp = Nothing
End Sub

why does the Run-time error '91'-> "Object variable or With block variable not set" occur if I run the function more than once?? It has something to do with the 'Set' call. Please help!! Your help is greatly appreciated.

Thanks in advance.

 

[yeungsprite]

The solution may be related to the following thread, but I am not sure exactly what to do!

http://support.microsoft.com/default.aspx?scid=kb;EN-US;178510

 

[VBslammer]

The reason you get an error the second time around is because you have used an implicit reference to 'ActiveSheet:'

Set colRange =

ActiveSheet

.Range("A1",

ActiveSheet

.Cells.SpecialCells(xlLastCell)) 'error appears here

Use explicit references:

ExcelApp.ActiveWorkBook.ActiveSheet.Range(etc...)

and you won't get an error. I normally declare variables for any object I need to reference more than once:

  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim sht As Excel.Worksheet

  Set xl = New Excel.Application
  
  xl.Visible = True
  
  xl.Dialogs(xlDialogOpen).Show "C:\Excel\*.xlt"
  
  Set wb = xl.ActiveWorkbook
  
  Set sht = wb.Sheets("Sheet1")

  ' etc.

VBSlammer

Unemployed in Houston, Texas