Excel through VB: cell data to string

[pmrankine]

Hi,
The following piece of code is part of a larger procedure but I have posted only this as:
1. it has worked in the past - honest!
2. it works with another spreadsheet
3. it works with an old copy of the spreadsheet - the sheet is another users and I can't see how (or if) they have amended it.

strArtNo = xlsExcelSheet.Cells(r, 3) & _
         xlsExcelSheet.Cells(r, 4) & _
         xlsExcelSheet.Cells(r, 5) & _
         xlsExcelSheet.Cells(r, 6)

r is the row - code around this moves through each row

The values in the four columns are similar to the following:
4 - M C3E6BK

These are picked up okay in the other sheets but in the one that doesn't work:
run time error 13 - Type Mismatch
when you debug it, the cell values are '14500', '-11500', nothing for column 3, and 'Error2023' for the fourth.

I checked Erro 2023 and it's #REF!

I've tried copying the values from the sheets that work and matching the format of the cells. I'm not sure where to start with this as it was previously working.

Thanks,
pmrankine

 

[pmrankine]

Additional info:
There is a couple of rows prior to where it falls over but these are not correct either: all cell values are showing as numbers rather than what is actually there.

 

[pmrankine]

Managed to 'fix' this.
I had previously declared the following:

Dim objExcelApp As Object, xlsExcelSheet As Object

But changed to:

Dim objExcelApp As Excel.Application, xlsExcelSheet As Excel.Worksheet

As I had used this in another project successfully.

Changing the declarations back to the original did the trick. I'm sure the second decalrations work but I'll need to play about with them.