How to remove the file link from the formula?

J

Jignesh Gandhi

Hi

I've a button on an excel file and clicking on it will copy sheets
from another excel file. The sheets are copied but what happens is
that in the copied sheets, in the formulas, it copies the file name
from where the sheets are copied. Is there any way where I can just
copy the sheets and formulas without copying links? Manually it can be
done by Edit -> Links and specify the current file name. But I want to
do it programatically.
Following is the code which copies the sheets.


Sub GetFile()
Dim FileName As String
Dim FilePath As String
Dim ControlFile As String
Dim i As Integer
ActiveWorkbook.Sheets("Loan Information").Select
FilePath = ActiveWorkbook.Sheets("Loan
Information").range("FilePath").Value
FileName = ActiveWorkbook.Sheets("Loan
Information").range("FileName").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open FileName:=FilePath & FileName

For i = 1 To Sheets.Count
Sheets(Trim(Sheets(i).Name)).Copy
After:=Workbooks(ControlFile).Sheets(Workbooks(ControlFile).Sheets.Count)
Windows(FileName).Activate
Next
Windows(FileName).Activate
Windows(FileName).Close SaveChanges:=False
ActiveWorkbook.Save
Windows(ControlFile).Activate
End Sub


Thanks & Regards
Jignesh Gandhi
 
S

ShaneDevenshire

Assuming that the formulas should be linked to the same cell but in the
current workbook and not the external workbook, you can choose the command
Edit, Links, Change Source and specify your current workbook.
 
J

Jignesh Gandhi

Hi

Thanks for your reply. But I want to do it programmatically and not
using the menu option. I have mentioned it in my post.

Thanks
Jignesh Gandhi
 
S

ShaneDevenshire

Hi,

You could try this, it works for me:

Cells.Replace What:="[*]", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top