Re: 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 work***
> > 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" <RAP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:ABC1425F-6B26-4D73-A1F8-96076EB011AF@xxxxxxxxxxxxxxxx
> > > > 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
> > >
> > >
> > >
.