Question about filename formula ..

[Matt]

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

 

[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

 

[Gord Dibben]

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

 

[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 ' ?

 

[Matt]

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)"

 

[Matt]

Got it!

(Tom)


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

 

[Zack Barresse]

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)



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)"

 

[Matt]

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

 

[Zack Barresse]

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

 

[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

 

[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

 

[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

 

[Zack Barresse]

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

 

[Matt]

cool. works now


this doesnt work however:

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

Matt

 

[Zack Barresse]

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.