Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trimming worksheet name from an imported data source
I'm bringing in data from an external source that changes each month, and to
make the macro run correctly it needs to have the same name each month on the tab (I think. I'm still a novice at VBA). It's name is always the same length except for the month's name at the beginning. I'm having a little trouble getting the Trim function to work in VBA though. Can somebody help me with this? I'm sure I'm missing something easy. If it helps here's the name for the July/August worksheet: JA_20082008_CHRYSLER_300_SERIES Next month the JA will be replaced with AS for August/September. I really would appreciate your help. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trimming worksheet name from an imported data source
=RIGHT(A1,LEN(A1)-FIND("_",A1)+1)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "lonnierudd via OfficeKB.com" <u11209@uwe wrote in message news:879532e6d8f03@uwe... I'm bringing in data from an external source that changes each month, and to make the macro run correctly it needs to have the same name each month on the tab (I think. I'm still a novice at VBA). It's name is always the same length except for the month's name at the beginning. I'm having a little trouble getting the Trim function to work in VBA though. Can somebody help me with this? I'm sure I'm missing something easy. If it helps here's the name for the July/August worksheet: JA_20082008_CHRYSLER_300_SERIES Next month the JA will be replaced with AS for August/September. I really would appreciate your help. -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trimming worksheet name from an imported data source
I'm not totally clear on what you are asking. Do you want to know how to
change the first 2 characters of a string of text? If your string is in a variable named, say, TabName so that currently, TabName = "JA_20082008_CHRYSLER_300_SERIES" and you want to change this to start with AS, then there are two ways you can do this... 1) TabName = "AS" & Mid(TabName, 3) 2) Mid$(TabName, 2) = "AS" You could then assign the TabName back to the tab. If you are asking something else, them I think you will have to provide us with more details. Rick "lonnierudd via OfficeKB.com" <u11209@uwe wrote in message news:879532e6d8f03@uwe... I'm bringing in data from an external source that changes each month, and to make the macro run correctly it needs to have the same name each month on the tab (I think. I'm still a novice at VBA). It's name is always the same length except for the month's name at the beginning. I'm having a little trouble getting the Trim function to work in VBA though. Can somebody help me with this? I'm sure I'm missing something easy. If it helps here's the name for the July/August worksheet: JA_20082008_CHRYSLER_300_SERIES Next month the JA will be replaced with AS for August/September. I really would appreciate your help. -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trimming worksheet name from an imported data source
The database I'm pulling the worksheet from comes from a vendor, and it
automatically assigns the worksheet name. What I'm looking for is to be able to have a macro pull the new numbers each month after I've loaded the new tables. There are 80+ different vehicles I pull this information on, so unless we can get the vendor to give us the data with a consistent name, not likely, then I need to be able to put it into a consistent form so that the worksheets with all the calculations on them will update correctly when the tables themselves are updated. Does that help? So if it'll trim 12 characters off the left side of the worksheet name, then I'd be good. I'd just repeat that on all 80+ worksheets that I need to trim. Trimming from the right would mean different code for each page since there's lots of different types of vehicles. Rick Rothstein (MVP - VB) wrote: I'm not totally clear on what you are asking. Do you want to know how to change the first 2 characters of a string of text? If your string is in a variable named, say, TabName so that currently, TabName = "JA_20082008_CHRYSLER_300_SERIES" and you want to change this to start with AS, then there are two ways you can do this... 1) TabName = "AS" & Mid(TabName, 3) 2) Mid$(TabName, 2) = "AS" You could then assign the TabName back to the tab. If you are asking something else, them I think you will have to provide us with more details. Rick I'm bringing in data from an external source that changes each month, and to [quoted text clipped - 9 lines] Next month the JA will be replaced with AS for August/September. I really would appreciate your help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trimming worksheet name from an imported data source
If you want to "trim off" the first 12 characters from your worksheet name,
you can use the Mid function to do that. Normally, you would specify a 3rd argument to this function telling it how many characters to retrieve from the starting point (specified in the 2nd argument) onward; however, if you omit the 3rd argument, the Mid function will retrieve from the starting point to the end of the text. So, if... WorksheetName = "JA_20082008_CHRYSLER_300_SERIES" then... TrimmedWorksheetName = Mid(WorksheetName, 13) Does that help? Rick "lonnierudd via OfficeKB.com" <u11209@uwe wrote in message news:8795f09169ce2@uwe... The database I'm pulling the worksheet from comes from a vendor, and it automatically assigns the worksheet name. What I'm looking for is to be able to have a macro pull the new numbers each month after I've loaded the new tables. There are 80+ different vehicles I pull this information on, so unless we can get the vendor to give us the data with a consistent name, not likely, then I need to be able to put it into a consistent form so that the worksheets with all the calculations on them will update correctly when the tables themselves are updated. Does that help? So if it'll trim 12 characters off the left side of the worksheet name, then I'd be good. I'd just repeat that on all 80+ worksheets that I need to trim. Trimming from the right would mean different code for each page since there's lots of different types of vehicles. Rick Rothstein (MVP - VB) wrote: I'm not totally clear on what you are asking. Do you want to know how to change the first 2 characters of a string of text? If your string is in a variable named, say, TabName so that currently, TabName = "JA_20082008_CHRYSLER_300_SERIES" and you want to change this to start with AS, then there are two ways you can do this... 1) TabName = "AS" & Mid(TabName, 3) 2) Mid$(TabName, 2) = "AS" You could then assign the TabName back to the tab. If you are asking something else, them I think you will have to provide us with more details. Rick I'm bringing in data from an external source that changes each month, and to [quoted text clipped - 9 lines] Next month the JA will be replaced with AS for August/September. I really would appreciate your help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trimming worksheet name from an imported data source
I think that'll do it. Thank you!
Rick Rothstein (MVP - VB) wrote: If you want to "trim off" the first 12 characters from your worksheet name, you can use the Mid function to do that. Normally, you would specify a 3rd argument to this function telling it how many characters to retrieve from the starting point (specified in the 2nd argument) onward; however, if you omit the 3rd argument, the Mid function will retrieve from the starting point to the end of the text. So, if... WorksheetName = "JA_20082008_CHRYSLER_300_SERIES" then... TrimmedWorksheetName = Mid(WorksheetName, 13) Does that help? Rick The database I'm pulling the worksheet from comes from a vendor, and it automatically assigns the worksheet name. What I'm looking for is to be [quoted text clipped - 41 lines] really would appreciate your help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trimming Data | Excel Discussion (Misc queries) | |||
Hyperlink imported data into worksheet with dated formate | Excel Worksheet Functions | |||
how to fit imported data into one worksheet | Excel Discussion (Misc queries) | |||
how to save imported data in diffrent worksheet cel before refrehs | Excel Worksheet Functions | |||
Can worksheet data be exported/imported to/from flat file? | Excel Programming |