![]() |
Apply LEN result to string?
Using LEFT formula and LEN result, I need to apply the result (8) in a new
LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
Maybe not be understanding, but take a look at
http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
Try this
Option Explicit Sub Test() Dim strFullFileName As String Dim i As Integer strFullFileName = "FileXX.dat" i = InStr(strFullFileName, ".") - 1 MsgBox Left(strFullFileName, i) End Sub *** Sent via Developersdex http://www.developersdex.com *** |
Apply LEN result to string?
Hello, Bob
As I re-read my entry, I can see how unclear it really was. My apologies. What I am trying to do is: put into VB script what I can do using worksheet functions in cells. This is my method of learning VB on an "as needed" basis. School is scheduled, but I need to "make do" until then. Many thanks for all input from members of this discussion group. My "on-line" time is limited where I am currently located, so I can only search & read this discussion group for a short time. What I am currently doing is: 1. Using VB to fetch the filename and placing it in cell B1., 2. Counting the filename characters using " LEN(B1) placed in D1. , (12 in this case) 3. Cell D2 is assigned the number 4, which equals the number of characters that make up the ".xls" portion of the filename I want to remove., 4. In cell D3 I have the formula: "=D1-D2" (12-4=8)., 5. Cell D2 has the formula: "=LEFT(B1,(D1-D2))", which returns the filename, minus the file extension. I am linking cell D2 to a chart title. I'm building a template file that is going to be duplicated approximately 30 times, and I am attempting to use good programming techniques to minimize "hard-coding" various aspects of the template. Re-stated, now with more info, my question is how to achieve the same results using VB only. I believe I have the solution worked out, except that I am having difficulty using variables in the VB equivalent to the formula used in Step 4. Hope this helps. Thanks for the input. The link you sent will be read thoroughly. Randy "Bob Phillips" wrote: Maybe not be understanding, but take a look at http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
Edward,
Thank you for your time and input. I already have a solution worked out in VB code, and can even get the answer to display in a msgbox, like the solution you presented. When I re-read my post, I see where I left out a lot of info in an effort to be short & sweet. Too much cropping this time. My apologies. I simply cannot get the syntax correct to get the worksheet function LEFT to accept variables instead of hard-coded text and numbers. Please see my reply to Bob for detail ad-nauseum on what I am attempting to do. Thanks again for your input. -Randy "Edward Ulle" wrote: Try this Option Explicit Sub Test() Dim strFullFileName As String Dim i As Integer strFullFileName = "FileXX.dat" i = InStr(strFullFileName, ".") - 1 MsgBox Left(strFullFileName, i) End Sub *** Sent via Developersdex http://www.developersdex.com *** |
Apply LEN result to string?
Hi Randy,
I will make an assumption that the filename will be assigned to a variable. Post back if not so. Dim sFilename As String, cLen As Long Dim sFilenameSans As String, cLenSans As Long sFilename = "myTest01.xls" cLen = Len(sFilename) cLenSans = cLen - 4 sFilenameSans = Left(sFilename, cLenSans) Those are step by step, although it could all be amalgamated Dim sFilename As String, cLen As Long Dim sFilenameSans As String, cLenSans As Long sFilename = "myTest01.xls" sFilenameSans = Left(sFilename, Len(sFilename) - 4) -- HTH Bob Phillips "RAP" wrote in message ... Hello, Bob As I re-read my entry, I can see how unclear it really was. My apologies. What I am trying to do is: put into VB script what I can do using worksheet functions in cells. This is my method of learning VB on an "as needed" basis. School is scheduled, but I need to "make do" until then. Many thanks for all input from members of this discussion group. My "on-line" time is limited where I am currently located, so I can only search & read this discussion group for a short time. What I am currently doing is: 1. Using VB to fetch the filename and placing it in cell B1., 2. Counting the filename characters using " LEN(B1) placed in D1. , (12 in this case) 3. Cell D2 is assigned the number 4, which equals the number of characters that make up the ".xls" portion of the filename I want to remove., 4. In cell D3 I have the formula: "=D1-D2" (12-4=8)., 5. Cell D2 has the formula: "=LEFT(B1,(D1-D2))", which returns the filename, minus the file extension. I am linking cell D2 to a chart title. I'm building a template file that is going to be duplicated approximately 30 times, and I am attempting to use good programming techniques to minimize "hard-coding" various aspects of the template. Re-stated, now with more info, my question is how to achieve the same results using VB only. I believe I have the solution worked out, except that I am having difficulty using variables in the VB equivalent to the formula used in Step 4. Hope this helps. Thanks for the input. The link you sent will be read thoroughly. Randy "Bob Phillips" wrote: Maybe not be understanding, but take a look at http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
If the end result is to strip the extension off a filename then you could do
several things. 1) If you wish to pass the length of the extension try this: (this assumes you include the . in the length so for .xla you would pass 4) Public Function RemoveExtension(ByVal FileName As String, ByVal ExtensionLength As Integer) As String If Len(FileName) = ExtensionLength Then RemoveExtension = Left(FileName, Len(FileName) - ExtensionLength) Else 'Not enough characters in filename 'return unmodified RemoveExtension = FileName End If End Function 2) if you assume the extension is .something then this approach will work: Public Function RemoveExtension(ByVal FileName As String) As String Dim DotPosition As Long DotPosition = InStrRev(FileName, ".") If DotPosition 0 Then RemoveExtension = Left(FileName, DotPosition - 1) Else 'No extension to remove RemoveExtension = FileName End If End Function 3) and lastly, the really lazy way: (for this you will need a reference to Microsoft Scripting Runtime scrrun.dll) Public Function RemoveExtension(ByVal FileName As String) As String Dim FSO As FileSystemObject Set FSO = New FileSystemObject RemoveExtension = FSO.GetBaseName(FileName) Set FSO = Nothing End Function "RAP" wrote: Hello, Bob As I re-read my entry, I can see how unclear it really was. My apologies. What I am trying to do is: put into VB script what I can do using worksheet functions in cells. This is my method of learning VB on an "as needed" basis. School is scheduled, but I need to "make do" until then. Many thanks for all input from members of this discussion group. My "on-line" time is limited where I am currently located, so I can only search & read this discussion group for a short time. What I am currently doing is: 1. Using VB to fetch the filename and placing it in cell B1., 2. Counting the filename characters using " LEN(B1) placed in D1. , (12 in this case) 3. Cell D2 is assigned the number 4, which equals the number of characters that make up the ".xls" portion of the filename I want to remove., 4. In cell D3 I have the formula: "=D1-D2" (12-4=8)., 5. Cell D2 has the formula: "=LEFT(B1,(D1-D2))", which returns the filename, minus the file extension. I am linking cell D2 to a chart title. I'm building a template file that is going to be duplicated approximately 30 times, and I am attempting to use good programming techniques to minimize "hard-coding" various aspects of the template. Re-stated, now with more info, my question is how to achieve the same results using VB only. I believe I have the solution worked out, except that I am having difficulty using variables in the VB equivalent to the formula used in Step 4. Hope this helps. Thanks for the input. The link you sent will be read thoroughly. Randy "Bob Phillips" wrote: Maybe not be understanding, but take a look at http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
Hello, Bob
I guess I'm "in-between" using pure worksheet function and pure VB, as I have not assigned the filename to a variable. Currently, I'm using "ActiveCell = ActiveWorkbook.Name". Variable would be better. Care to give me the "assign filename to variable" lesson? I'm ready... Thanks, Randy "Bob Phillips" wrote: Hi Randy, I will make an assumption that the filename will be assigned to a variable. Post back if not so. Dim sFilename As String, cLen As Long Dim sFilenameSans As String, cLenSans As Long sFilename = "myTest01.xls" cLen = Len(sFilename) cLenSans = cLen - 4 sFilenameSans = Left(sFilename, cLenSans) Those are step by step, although it could all be amalgamated Dim sFilename As String, cLen As Long Dim sFilenameSans As String, cLenSans As Long sFilename = "myTest01.xls" sFilenameSans = Left(sFilename, Len(sFilename) - 4) -- HTH Bob Phillips "RAP" wrote in message ... Hello, Bob As I re-read my entry, I can see how unclear it really was. My apologies. What I am trying to do is: put into VB script what I can do using worksheet functions in cells. This is my method of learning VB on an "as needed" basis. School is scheduled, but I need to "make do" until then. Many thanks for all input from members of this discussion group. My "on-line" time is limited where I am currently located, so I can only search & read this discussion group for a short time. What I am currently doing is: 1. Using VB to fetch the filename and placing it in cell B1., 2. Counting the filename characters using " LEN(B1) placed in D1. , (12 in this case) 3. Cell D2 is assigned the number 4, which equals the number of characters that make up the ".xls" portion of the filename I want to remove., 4. In cell D3 I have the formula: "=D1-D2" (12-4=8)., 5. Cell D2 has the formula: "=LEFT(B1,(D1-D2))", which returns the filename, minus the file extension. I am linking cell D2 to a chart title. I'm building a template file that is going to be duplicated approximately 30 times, and I am attempting to use good programming techniques to minimize "hard-coding" various aspects of the template. Re-stated, now with more info, my question is how to achieve the same results using VB only. I believe I have the solution worked out, except that I am having difficulty using variables in the VB equivalent to the formula used in Step 4. Hope this helps. Thanks for the input. The link you sent will be read thoroughly. Randy "Bob Phillips" wrote: Maybe not be understanding, but take a look at http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
Stephan,
I know there is usually more than one way to accomplish a task in VB, but you take the prize with 3 options in one reply. Way cool! Thank you. I can't wait to try them all out. - Randy "Stefan Hojnowski" wrote: If the end result is to strip the extension off a filename then you could do several things. 1) If you wish to pass the length of the extension try this: (this assumes you include the . in the length so for .xla you would pass 4) Public Function RemoveExtension(ByVal FileName As String, ByVal ExtensionLength As Integer) As String If Len(FileName) = ExtensionLength Then RemoveExtension = Left(FileName, Len(FileName) - ExtensionLength) Else 'Not enough characters in filename 'return unmodified RemoveExtension = FileName End If End Function 2) if you assume the extension is .something then this approach will work: Public Function RemoveExtension(ByVal FileName As String) As String Dim DotPosition As Long DotPosition = InStrRev(FileName, ".") If DotPosition 0 Then RemoveExtension = Left(FileName, DotPosition - 1) Else 'No extension to remove RemoveExtension = FileName End If End Function 3) and lastly, the really lazy way: (for this you will need a reference to Microsoft Scripting Runtime scrrun.dll) Public Function RemoveExtension(ByVal FileName As String) As String Dim FSO As FileSystemObject Set FSO = New FileSystemObject RemoveExtension = FSO.GetBaseName(FileName) Set FSO = Nothing End Function "RAP" wrote: Hello, Bob As I re-read my entry, I can see how unclear it really was. My apologies. What I am trying to do is: put into VB script what I can do using worksheet functions in cells. This is my method of learning VB on an "as needed" basis. School is scheduled, but I need to "make do" until then. Many thanks for all input from members of this discussion group. My "on-line" time is limited where I am currently located, so I can only search & read this discussion group for a short time. What I am currently doing is: 1. Using VB to fetch the filename and placing it in cell B1., 2. Counting the filename characters using " LEN(B1) placed in D1. , (12 in this case) 3. Cell D2 is assigned the number 4, which equals the number of characters that make up the ".xls" portion of the filename I want to remove., 4. In cell D3 I have the formula: "=D1-D2" (12-4=8)., 5. Cell D2 has the formula: "=LEFT(B1,(D1-D2))", which returns the filename, minus the file extension. I am linking cell D2 to a chart title. I'm building a template file that is going to be duplicated approximately 30 times, and I am attempting to use good programming techniques to minimize "hard-coding" various aspects of the template. Re-stated, now with more info, my question is how to achieve the same results using VB only. I believe I have the solution worked out, except that I am having difficulty using variables in the VB equivalent to the formula used in Step 4. Hope this helps. Thanks for the input. The link you sent will be read thoroughly. Randy "Bob Phillips" wrote: Maybe not be understanding, but take a look at http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
Apply LEN result to string?
sFilename = ActiveWorkbook.Name or sFilename = ThisWorkbook.Name -- HTH Bob Phillips "RAP" wrote in message ... Hello, Bob I guess I'm "in-between" using pure worksheet function and pure VB, as I have not assigned the filename to a variable. Currently, I'm using "ActiveCell = ActiveWorkbook.Name". Variable would be better. Care to give me the "assign filename to variable" lesson? I'm ready... Thanks, Randy "Bob Phillips" wrote: Hi Randy, I will make an assumption that the filename will be assigned to a variable. Post back if not so. Dim sFilename As String, cLen As Long Dim sFilenameSans As String, cLenSans As Long sFilename = "myTest01.xls" cLen = Len(sFilename) cLenSans = cLen - 4 sFilenameSans = Left(sFilename, cLenSans) Those are step by step, although it could all be amalgamated Dim sFilename As String, cLen As Long Dim sFilenameSans As String, cLenSans As Long sFilename = "myTest01.xls" sFilenameSans = Left(sFilename, Len(sFilename) - 4) -- HTH Bob Phillips "RAP" wrote in message ... Hello, Bob As I re-read my entry, I can see how unclear it really was. My apologies. What I am trying to do is: put into VB script what I can do using worksheet functions in cells. This is my method of learning VB on an "as needed" basis. School is scheduled, but I need to "make do" until then. Many thanks for all input from members of this discussion group. My "on-line" time is limited where I am currently located, so I can only search & read this discussion group for a short time. What I am currently doing is: 1. Using VB to fetch the filename and placing it in cell B1., 2. Counting the filename characters using " LEN(B1) placed in D1. , (12 in this case) 3. Cell D2 is assigned the number 4, which equals the number of characters that make up the ".xls" portion of the filename I want to remove., 4. In cell D3 I have the formula: "=D1-D2" (12-4=8)., 5. Cell D2 has the formula: "=LEFT(B1,(D1-D2))", which returns the filename, minus the file extension. I am linking cell D2 to a chart title. I'm building a template file that is going to be duplicated approximately 30 times, and I am attempting to use good programming techniques to minimize "hard-coding" various aspects of the template. Re-stated, now with more info, my question is how to achieve the same results using VB only. I believe I have the solution worked out, except that I am having difficulty using variables in the VB equivalent to the formula used in Step 4. Hope this helps. Thanks for the input. The link you sent will be read thoroughly. Randy "Bob Phillips" wrote: Maybe not be understanding, but take a look at http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "RAP" wrote in message ... Using LEFT formula and LEN result, I need to apply the result (8) in a new LEFT statement and display the results in a cell. I am getting the filename, removing the file extension, and want to apply the formula result (which = # characters to display from original filename), and display in a new cell. How can I apply my variables in a new LEFT statement? Thanks, Randy |
All times are GMT +1. The time now is 09:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com