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