ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Apply LEN result to string? (https://www.excelbanter.com/excel-programming/336784-apply-len-result-string.html)

RAP

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

Bob Phillips[_6_]

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




Edward Ulle

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 ***

RAP

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





RAP

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 ***


Bob Phillips[_6_]

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







Stefan Hojnowski

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





RAP

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







RAP

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




Bob Phillips[_6_]

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