Re: 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 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
> >
> >
> >
.