Using replace in VBA

[André]

Hi,

I have a macro that updates values and formulas from another workbook. When
copy a range from Book1 to Book2 I get the file-reference in Book2, which I
want to remove. The formula I copy from Book1 refers to a cell on another
sheet in the same workbook. When I copy this formula to Book2, the formula
refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or copy
(with vba code) the formula in a way that not includes the path+filename
from Book1.


André

 

[Joel]

I simplified the instruction below. The only reason it may not work is the
sigle quote. because you are replacing the string with a single quote I
removed the single qoute from the What and the Replacemnt properties.

Activesheet.cells.Replace
What:="C:\Temp\Book1.xls]", _
Replacement:= "", _
LookAt:=xlPart, _
MatchCase:=False

 

[Daniel.C]

Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel

 

[André]

Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when
including this in the main macro. When I copied the code and run the
replace-part as a separate macro, it worked!

Why don't the code work when included in the main macro? (see code below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Åpner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub

Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel

Hi,

I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2,
the formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and
also tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André

 

[Daniel.C]

Strange. Is your macro attached to a commandbutton ? In such a case,
"activesheet" refers to the command button sheet; but I think you
should have noticed it when pasting on it. If you get no valid answer,
perhaps you could upload your file on a site such as
http://www.filedropper.com
after blurring confidential data

Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when including
this in the main macro. When I copied the code and run the replace-part as a
separate macro, it worked!

Why don't the code work when included in the main macro? (see code below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i angitt
katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle filer.
'Åpner workbook x og setter en variabel til den
Set wbResults = Workbooks.Open(filename:=.FoundFiles(lCount),
UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub

Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel

Hi,

I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2, the
formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André

 

[Joel]

Th eonly reason the code wouldn't run in another macro is that the wrong
sheet is activated. I usually resolve these problem by alwasy refering to
the sheets by names or objects. I rewrote yoiur code like the way I would
normally write the code. The only thing I found problematic is with this
statement

with wbResults.ActiveSheet

Often problem can occur when you open a workbook and not specify which sheet
you ared using. It is not a problem when you have a worksobbk with one
sheet. The active sheet when you open a workbook is the sheet that was
active when it was closed. You can't guarentee which sheet will be the
active sheet. alway specify which is the worksheet when you open a workbook.

Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle filer.
'Ã…pner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open( _
filename:=.FoundFiles(lCount), _
UpdateLinks:=0)

Sheets("Historikk").Visible = True

with Sheets("Historikk")
.Unprotect
.Range("J1").Select
end with

with Sheets("Historikk")
.Columns("J:J").Copy _
destination:=wbResults.ActiveSheet

Application.CutCopyMode = False
end with

with wbResults.ActiveSheet
.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", _
Type:=xlExcelLinks 'NB! Endre sti og filnavn til
koblingen her
end with

with wbMal.Sheets("Sammenligning ny")

.Columns("B:K").Copy _

destination:=wbResults.Sheets("Sammenligning").Range("B1")

Application.CutCopyMode = False

'HERE IS THE REPLACE-CODE
.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
.Range("B1").Select
end with

wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub

Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when
including this in the main macro. When I copied the code and run the
replace-part as a separate macro, it worked!

Why don't the code work when included in the main macro? (see code below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Ã…pner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub

Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel

Hi,

I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2,
the formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and
also tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André

 

[André]

Hi and thank you all for answering,

I still don't get the replace function to work as expected, even if I
specify which sheet that is active before doing the replace.....

I solved it by moving the code out of the macro and calling the separate
code at the end of the main macro. Then it worked like a charm!

Don't understand why, but I'm just an economist that likes to play with
vba

BR
André

Th eonly reason the code wouldn't run in another macro is that the wrong
sheet is activated. I usually resolve these problem by alwasy refering to
the sheets by names or objects. I rewrote yoiur code like the way I would
normally write the code. The only thing I found problematic is with this
statement

with wbResults.ActiveSheet

Often problem can occur when you open a workbook and not specify which
sheet
you ared using. It is not a problem when you have a worksobbk with one
sheet. The active sheet when you open a workbook is the sheet that was
active when it was closed. You can't guarentee which sheet will be the
active sheet. alway specify which is the worksheet when you open a
workbook.

Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Åpner workbook x og setter en variabel til
den
Set wbResults =
Workbooks.Open( _
filename:=.FoundFiles(lCount), _
UpdateLinks:=0)

Sheets("Historikk").Visible = True

with Sheets("Historikk")
.Unprotect
.Range("J1").Select
end with

with Sheets("Historikk")
.Columns("J:J").Copy _
destination:=wbResults.ActiveSheet

Application.CutCopyMode = False
end with

with wbResults.ActiveSheet
.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", _
Type:=xlExcelLinks 'NB! Endre sti og filnavn til
koblingen her
end with

with wbMal.Sheets("Sammenligning ny")

.Columns("B:K").Copy _

destination:=wbResults.Sheets("Sammenligning").Range("B1")

Application.CutCopyMode = False

'HERE IS THE REPLACE-CODE
.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]",
_
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
.Range("B1").Select
end with

wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub

Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in
the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when
including this in the main macro. When I copied the code and run the
replace-part as a separate macro, it worked!

Why don't the code work when included in the main macro? (see code
below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med
F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Åpner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB!
Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!",
vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub

Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel

Hi,

I have a macro that updates values and formulas from another
workbook.
When copy a range from Book1 to Book2 I get the file-reference in
Book2,
which I want to remove. The formula I copy from Book1 refers to a cell
on
another sheet in the same workbook. When I copy this formula to Book2,
the formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
_
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro,
but
nothing is replaced! I have tried to change lookat to xlFormulas, and
also tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André

 

[dbKemp]

Hi,

I have a macro that updates values and formulas from another workbook. When
copy a range from Book1 to Book2 I get the file-reference in Book2, whichI
want to remove. The formula I copy from Book1 refers to a cell on another
sheet in the same workbook. When I copy this formula to Book2, the formula
refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or copy
(with vba code) the formula in a way that not includes the path+filename
from Book1.

André

Try this:
Dim sFormula As String
Dim rCell As Excel.Range

For Each rCell In ActiveCell.CurrentRegion
sFormula = rCell.Formula
sFormula = Replace(sFormula, "[Book1.xls]", "")
rCell.Formula = sFormula
Next