Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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é |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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 "André" wrote: 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é |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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é |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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 "Daniel.C" skrev i melding ... 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é |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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 "Daniel.C" skrev i melding ... 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é |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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").Ran ge("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 "André" wrote: 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 "Daniel.C" skrev i melding ... 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é |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
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é "Joel" skrev i melding ... 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").Ran ge("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 "André" wrote: 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 "Daniel.C" skrev i melding ... 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é |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using replace in VBA
On Oct 26, 6:32 am, "André" wrote:
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é 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
How to Replace multiple words to replace using excell | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming |