I found this formula, which was posted in 2004.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)


I wonder what does the cell A1 stand for? I removed it and it works
also ..

Matt

and next question:

How can i get a macro to put this in a cell?

tried that:

Range("AA10").Select
ActiveCell.FormulaR1C1 =
"=MID(CELL('filename',A1),FIND('[',CELL('filename',A1),1)+1,FIND(']',CELL('filename',A1),1)-FIND('[',CELL('filename',A1),1)-1)"

results in errors ;;

Matt

Matt

See Bob Phillips' site for an explanation of why you need the A1.

http://www.xldynamic.com/source/xld.xlFAQ0002.html

Then go back and undo your deletion.


Gord Dibben Excel MVP

On 27 Sep 2005 14:00:27 -0700, "Matt" wrote:


I found this formula, which was posted in 2004.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)


I wonder what does the cell A1 stand for? I removed it and it works
also ..

Matt

cool :)

Now, how do I get this to work when a macro puts the forumla into a
cell? Does it need the R1C1 clutter for the A1? and all " replaced
with ' ?

anybody? If pasted like that it causes errors :(

Range("AA10").Select
ActiveCell.FormulaR1C1 =
"=MID(CELL('filename',A1),FIND('[',CELL('filename',A1),1)+1,FIND(']',CELL('*filename',A1),1)-FIND('[',CELL('filename',A1),1)-1)"

Got it!

(Tom)


Range("B1").Value =
left(activeworkbook.Name,len(ActiveWorkbook.name)-4)

First of all, the formula does work, just not the way you have it. You have
a single apostrophe character ' instead of a " quote character. When using
VBA, however, you must use double quote "" signs. This will make it work,
however there is an easier way. How about ...

With ActiveWorkbook
Range("AA10").Value = Left(.Name, Len(.Name) - 4)
End With

This will give you the name without the ".xls" on the end. If you do want
that, how about ...

Range("AA10").Value = ActiveWorkbook.Name

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



"Matt" wrote in message
oups.com...
anybody? If pasted like that it causes errors :(

Range("AA10").Select
ActiveCell.FormulaR1C1 =
"=MID(CELL('filename',A1),FIND('[',CELL('filename',A1),1)+1,FIND(']',CELL('*filename',A1),1)-FIND('[',CELL('filename',A1),1)-1)"

I used this in my macro:

Dim tlm As String

tlm = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

Range("Z3").Value = tlm


the problem is now that it will cut off leading zeros ... I understood
it shouldnt do that because I dimed it as string .. what do i have todo
to keep leading zeros?

Matt

Matt,

With just one line, why even use a variable? Seems like a waste of 2 lines.
Are you using the variable in anything else? If more than two times, I'd
suggest using the variable, otherwise, it's pretty much a waste. Or if
you're going to provide multiple checks on it..

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Matt" wrote in message
oups.com...
I used this in my macro:

Dim tlm As String

tlm = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

Range("Z3").Value = tlm


the problem is now that it will cut off leading zeros ... I understood
it shouldnt do that because I dimed it as string .. what do i have todo
to keep leading zeros?

Matt

I used it so I can dim it as string :)

I am not a programmer so I dont knwo how to do these things right...
but the problem is that it omits leading zeros :(

Matt

The macro opens a form with fields. One of the fields shows the
filename, ecept .xls., it cuts leadign zeros. When i abort the macro
and restart it it DOES show leading zeros ... how can that be? and how
can it be done so it shows them from the beginning ...?

Matt

I got it to work by adding this:

Range("Z2:Z3").Select
Selection.NumberFormat = "@"

not sure why .. my programming is like trial and error ;)

Matt

You only need to set the format once, so you shouldn't need to run that
again. But in your code, instead of using the .Value property, you can use
the .Text property and it will use your leading zeros. If you're setting
the value of a cell as your variable, you can either set the format before
hand or use a single apostrophe preceeding the data entered into the cell.
The apostrophe will not show in the value of the cell, but denotes the
entire cell contents to be a textual value. This is primarily used when
using numerical values and wanting leading zeros to show up in the cell.
Note that this no longer becomes numerical, but textual, and will need to be
mitigated as such if using in any subsequent calculations.

Dim tlm As String
tlm = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
Range("Z3").NumberFormat = "@"

Range("Z3").Value = tlm
'or..
Range("Z3").Text = "'" & tlm

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)




"Matt" wrote in message
ups.com...
I got it to work by adding this:

Range("Z2:Z3").Select
Selection.NumberFormat = "@"

not sure why .. my programming is like trial and error ;)

Matt

cool. works now:)


this doesnt work however:

Range("Z3").Text = "'" & tlm

Matt

Oh, I'm sorry ...

Range("Z3").Value = "'" & tlm

Can't change the Text property but you can change the Value property. You
can only read the Text property. Sorry 'bout that.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Matt" wrote in message
oups.com...
cool. works now:)


this doesnt work however:

Range("Z3").Text = "'" & tlm

Matt