Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
Hi everyone,
I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
Do you workbook names have dots in them?
-- Rick (MVP - Excel) "Diddy" wrote in message ... Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
It is removing the extension of activeworkbook and assigning that name to the
sheet name. If you already have a . (dot) it works wrongly. Replace the below line ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) with ActiveSheet.Name = Left(ActiveWorkbook.Name, _ InStrRev(ActiveWorkbook.Name, ".") - 1) If this post helps click Yes --------------- Jacob Skaria "Diddy" wrote: Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
On Aug 3, 11:20*am, Diddy wrote:
Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub Diddy, You may want to start by reading the help file on the LEFT and FIND functions. (Insert a function, search for LEFT (and FIND), then select "Help on this function", and read the file). This will give you a good background of what these functions do. The biggest question I would have is this: Does your file name have "." in them other than the "." prior to the file extension? If the answer is yes, then after you read about FIND and LEFT, you'll know why the procedure isn't working to your liking. ActiveWorkbook.Name will return the file name with the extension, e.g. Prototype Analysis Tool (2007-08-03) v9.xlsm. Again, start with LEFT and FIND. If you have a question after this, then reply again to the post. Best, Matthew Herbert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
Hi Jacob,
That works! Thank you :-) Cheers Diddy "Jacob Skaria" wrote: It is removing the extension of activeworkbook and assigning that name to the sheet name. If you already have a . (dot) it works wrongly. Replace the below line ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) with ActiveSheet.Name = Left(ActiveWorkbook.Name, _ InStrRev(ActiveWorkbook.Name, ".") - 1) If this post helps click Yes --------------- Jacob Skaria "Diddy" wrote: Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
Hi Rick,
No they don't. How do I change the code to suit my workbook names? C:\Documents and Settings\diddy\My Documents\Data\Plot1\yr2 Data 2009 Cheers Diddy (feeling dopey) "Rick Rothstein" wrote: Do you workbook names have dots in them? -- Rick (MVP - Excel) "Diddy" wrote in message ... Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
Hi Matthew,
Thanks for replying. So the Left and Find, find the "." and then return the filename to the left of it. Now I get why Rick asked if there were other "." in the file name (I think!). I looked up InStrRev (Jacob Skaria suggested code below). The -1 is the default for the starting position. Is the -1 in the original code the same? ActiveSheet.Name = Left(ActiveWorkbook.Name, InStrRev_(ActiveWorkbook.Name, ".") - 1) Still swimming through mud :-) cheers Diddy "Matthew Herbert" wrote: On Aug 3, 11:20 am, Diddy wrote: Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub Diddy, You may want to start by reading the help file on the LEFT and FIND functions. (Insert a function, search for LEFT (and FIND), then select "Help on this function", and read the file). This will give you a good background of what these functions do. The biggest question I would have is this: Does your file name have "." in them other than the "." prior to the file extension? If the answer is yes, then after you read about FIND and LEFT, you'll know why the procedure isn't working to your liking. ActiveWorkbook.Name will return the file name with the extension, e.g. Prototype Analysis Tool (2007-08-03) v9.xlsm. Again, start with LEFT and FIND. If you have a question after this, then reply again to the post. Best, Matthew Herbert |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename str cut too short
Hi Rick,
Still feeling dopey but I think the penny has dropped. You're asking if there are "." in filename because the Find is looking for "." Sorry I'm so dense! Cheers Diddy "Diddy" wrote: Hi Rick, No they don't. How do I change the code to suit my workbook names? C:\Documents and Settings\diddy\My Documents\Data\Plot1\yr2 Data 2009 Cheers Diddy (feeling dopey) "Rick Rothstein" wrote: Do you workbook names have dots in them? -- Rick (MVP - Excel) "Diddy" wrote in message ... Hi everyone, I've nabbed this bit of code off the web and I'm using it with Chip Pearson's code that loops through files in a folder to change the tab name into the file name. All workbooks have only one sheet and code is working fine except that it is cutting the filename too short. I don't understand how the Left and -1 are working but think that the intention is to remove the path and file extension. If that's what the left and -1 are doing then why does the filename get shortened? Could any kind person please explain how this is working and show me how to amend to give the unshortened filename without path and extension? Thanks Diddy Sub renametabaswkbkname(myWB As Workbook) On Error Resume Next ActiveSheet.Name = Left(ActiveWorkbook.Name, Application.Find(".", ThisWorkbook.Name) - 1) myWB.Close savechanges:=True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs Filename:=filename, FileFormat:=xlCSV | Excel Programming | |||
Converting a Variable Filename to a Constant Filename | Excel Programming | |||
set filename to <filename-date on open | Excel Worksheet Functions | |||
set excel <filename to <filename-date | Excel Programming | |||
Saving filename same as import filename | Excel Programming |