Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to capture a Property as a variable
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to capture a Property as a variable
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 "TISR" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How capture a date variable within a text cell? | Excel Discussion (Misc queries) | |||
range variable won't assign (chartobject.topleftcell property) | Excel Programming | |||
how to use object variable to change the property of a | Excel Programming | |||
variable for columns(??).property | Excel Programming | |||
Capture Custom View in a Variable | Excel Programming |