not able to set a sheet name having two dots.

L

Laks

Can any one help? When i try to put this code in excel 2003 and run, i get "Subscript out of range" runtime error 9. But in 2000 it was not there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tmpWB As Workbook
Set tmpWB = Workbooks.Open("c:\208.Face-Centered_CCD.table", 0, True, 2)
Dim tmpSheet As New Worksheet
Dim s As String
s = Left("208.Face-Centered_CCD.table", 31)
MsgBox s
Set tmpSheet = Sheets(Left("208.Face-Centered_CCD.table", 31))
MsgBox tmpSheet.Name
End Sub
 
T

Tom Ogilvy

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tmpWB As Workbook
Set tmpWB = Workbooks.Open("c:\208.Face-Centered_CCD.table", 0, True, 2)
Dim tmpSheet As Worksheet
Dim s As String
s = "208.Face-Centered_CCD.table"
MsgBox s
On Error Resume Next
Set tmpSheet = Sheets(s)
On Error goto 0
if tmpSheet is nothing then
Msgbox "No sheet named " & s
else
MsgBox tmpSheet.Name
End if
End Sub

--
Regards,
Tom Ogilvy

Laks said:
Can any one help? When i try to put this code in excel 2003 and run, i get
Click to expand...
"Subscript out of range" runtime error 9. But in 2000 it was not there.
 
Top