not able to set a sheet name having two dots.
 

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

not able to set a sheet name having two dots.
 

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" wrote in message
...
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