Select method of Range class failed when building Excel SS

[AndrewForrest]

I am getting an intermittent problem "Select method of Range class failed" whilst building an excel spread sheet from a VS2005 VB program.

I have tried both
xlSheetCol.Range("a134").Select()
and xlSheetCol.Columns("A").Select()

they both work in testing for a while and then give the error for a while then work again !!!
Any Ideas?

excerpt from program:-

Imports Excel = Microsoft.Office.Interop.Excel


Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheetCol As Excel.Worksheet

xlBook = xlApp.Workbooks.Add
xlSheetCol = xlBook.Worksheets("Sheet1")
xlSheetCol.Name = "Full List"
With xlSheetCol
strSql = "A1" & iRowEnd
'********* this bit fails
.Range(strSql).Select()
'*********
.Columns.Select()
.Columns.NumberFormat = "0.00"
.Columns("A").EntireColumn.AutoFit()
strSql = "K2:O" & iRowEnd
.Range(strSql).Select()
.Shapes.AddChart.Select()
End With
' xlSheetCol.Columns("A").Select()
' xlSheetCol.Columns.NumberFormat = "0.00"
' Insert Chart into Spread sheet
'strSql = "K2:O" & iRowEnd
'xlSheetCol.Range(strSql).Select()
'xlSheetCol.Shapes.AddChart.Select()


"Don't worry about the world coming to an end today.
It’s already tomorrow in Australia." - Charles Schulz

 

[DjangMan]

Try xlSheetCol.Activate first.

 

[AndrewForrest]

Thanks Djang,
I will give it a go.

I use .Activate() when I switch to the 4 other sheets, but as I have 50+ lines of code loading cells between xlSheetCol.Name = "Full List"
and
With xlSheetCol
which all work ok, it seemed redundant.

"Don't worry about the world coming to an end today.
It’s already tomorrow in Australia." - Charles Schulz