loop for macro
You shouldn't have to loop through all the cells. The edit|replace code should
work for all the cells on that sheet.
But your code didn't compile for me. I have a feeling that you changed it after
you pasted it into your message (based on how that "application data" string was
split).
This should be closer, but I don't think it will do what you want:
Option Explicit
Sub extractMe()
Cells.Replace What:= _
"C:\Documents and Settings\UserName\Application" _
& " Data\Microsoft\Templates\[TR Claim Book.xltx]", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
But I'm guessing that you're copying a worksheet from that template workbook and
it still has formulas that point to sheets/ranges in the template workbook.
If that's the case, then the formula is gonna look like this (simplified)
example:
='C:\(longpathhere)\[TR Claim Book.xltx]Sheet1'!A1
And that means you want to include the initial apostrophe in your string to
replace.
Sub extractMe()
Cells.Replace What:= _
"='C:\Documents and Settings\UserName\Application" _
& " Data\Microsoft\Templates\[TR Claim Book.xltx]", _
Replacement:="='", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Notice that the ='(longpath)[filename]" is replaced with just the ='
============
ps.
Another option may be to use:
Edit|links|change links (in xl2003 menus) to point at the current file (that's
been saved at least once!)
Record a macro when you do it manually and you'll have the code.
pps.
Another option that I like to use.
Another way that I use:
Select the range to copy (in the source worksheet in the template workbook)
Convert the formulas to string
edit|replace
what: = (equal sign)
with: $$$$$= (a nice unused string)
replace all
Now I can copy|paste all these strings and they stay strings.
Then I fix both the source and destination ranges by doing a couple of
edit|replace's.
Select the range to fix
edit|replace
what: $$$$$=
with: =
replace all
Steve wrote:
Howdee all.
I just recorded a macro to strip off the path to a template document, for
some formulas.
The recorded code is:
Sub extractMe()
Cells.Replace What:= _
"C:\Documents and Settings\UserName\Application _
Data\Microsoft\Templates\[TR Claim Book.xltx]" _
, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
End Sub
I just tried placing it in a for loop, and I cannot get it to work right. I
keep getting a 438 error.
My throughts we
Dim MyCell as range
For each MyCell in ActiveSheet
' "replace" code from above
next
What code do I use for a loop that will just run through the single page-- I
want to remove all of the template pathways that exist-- varies from 2 or 3
up to around 10 instances.
Thank you.
--
Dave Peterson
|