How to capture a Property as a variable

[Guest]

I need to copy a number of sheets from other workbooks into one workbook.
For ease of identification I name each worksheet by its workbook.name
property.
Thus the copied sheets appear as "name.xls".
I would like to get rid off the .xls in the name.
I am able to do this by the following function
Do Until Mid(t, j, 1) = "."
y = y + Mid(t, j, 1)
j = j + 1
Loop
Since the workbook name changes as many times as I copy sheets.
I need to assign variable t = workbook.name property
I don't know what type of variable I need to declare.
I have tried variant etc but nothing works.
I have also tried to put [workbook.name]
Most of the time i get an error message that says that i require an object.
Am I on the right track? Can someone help?

 

[John]

Tisr,

Firstly the Mid function returns a string, so (as long as y is also a
string) if you're trying to concatenate y and the string then use an
ampersand:

y = y & Mid(t, j, 1)

't', as you you've got it in the "Mid" function should also be a string, so
you could use this:

Dim t As String
t = Workbook.Name

However you could also replace the loop with a "Len" function and count
backwards as you know that .xls is four characters, so:

Dim sNewSheetName As String
Dim wkbSource As Workbook

'Set the source workbook reference
'(Change this to the workbook you're after)
Set wkbSource = Application.ThisWorkbook

'Check wkb name ends with ".xls"
If Right(wkbSource.Name, 4) = ".xls" Then
'If so trim off file extension
sNewSheetName = Mid(wkb.Name, 1, (Len(wkb.Name) - 4))
End If

Anyway, hope I've understood you problem correctly

Best regards

John