Strange Link Appearing Automatically

[MDW]

My department uses an Excel workbook with macro-driven calculation and
modeling. The file is called Model.xls. The model has links to two external
input files, also Excel workbooks.

After the model is done calculating, it creates an output file by saving
itself as output_Model.xls and then copying each worksheet and pasting the
values. This process has been in place for years and we haven't experienced
any problems with it.

What we've discovered is that this output file has a link listed under the
"Edit - Links" menu. Strangely enough the link isn't to either of the input
files, but rather to the file "Model.xls". I can't locate any cells that
actually reference this link - as I said, the final step in the creation of
the output file is to copy/paste values on each sheet.

Does anyone know how that link could have gotten there? It appears to be on
every output file I can locate. It seems completely benign, but I'm just
curious as to what process would have caused it to happen in the first place.
As a bonus, if anyone has any suggestions for how we can prevent it from
happening, that would be great as well.

 

[Jim Jackson]

What is the wording in the macro where the data is pasted in the new
Workbook? A change there might be what is needed.

 

[MDW]

Here's the code. This is legacy stuff, and unfortunately my boss is not apt
to want to move to a different methodology. Watch for line wrap. The
variables Path, copy_from_workbook_name, and copy_to_workbook_name can be
assumed to be "C:\Some Folder\","Model.xls","output_Model.xls", respectively.

Function copy_paste_values_to_new_workbook(Path, copy_from_workbook_name,
copy_to_workbook_name)

'
-------------------------------------------------------------------------------
' 1) add a new worksheet 2) rename by saving
'
-------------------------------------------------------------------------------

Workbooks.Add

ActiveWorkbook.SaveAs Filename:=Path & copy_to_workbook_name

'
-------------------------------------------------------------------------------
' the newly created worksheet is open and has been saved once
' next individual worksheets are copied from the target file to
the newly created file
'
-------------------------------------------------------------------------------
Call
copy_this_work_sheet_to_that_worksheet(copy_from_workbook_name,
"store_monthly_output", copy_to_workbook_name)

Call
copy_this_work_sheet_to_that_worksheet(copy_from_workbook_name,
"monthly_summary", copy_to_workbook_name)

Call
copy_this_work_sheet_to_that_worksheet(copy_from_workbook_name,
"summary_report", copy_to_workbook_name)

'
-------------------------------------------------------------------------------
' clear the clipboard and save the active spreadsheet
'
-------------------------------------------------------------------------------

Application.CutCopyMode = False 'Clear Clipboard

ActiveWorkbook.Close SaveChanges:=True

End Function



Function copy_this_work_sheet_to_that_worksheet(copy_from_here, sheet_name,
copy_to_here)

'
-------------------------------------------------------------------------------
' the first step copies everything (formulas & formats to the new
worksheet
'
-------------------------------------------------------------------------------

Workbooks(copy_from_here).Sheets(sheet_name).Copy
Workbooks(copy_to_here).Sheets("sheet1")

'
-------------------------------------------------------------------------------
' the second step copies the new worksheet and pastes valuse over
top itself
'
-------------------------------------------------------------------------------

Cells.Select: Selection.Copy: Selection.PasteSpecial
Paste:=xlValues

End Function

 

[Jim Jackson]

Best I can tell, since the routine is copying a sheet and pasting the sheet
in the new workbook, the Link is an automatic. If it were simply a matter of
selecting sheet1 and selecting the cells to copy, the paste links would not
occur.

I may be way off but I can see no other clues. I hope this helps somewhat.

Jim

 

[MDW]

Yeah, I think you're right. If I manually replicate what the macro does, I
get the link too. Hm. Wierd.

Well, at least we know for sure it's harmless. Thanks.

 

[Jim Jackson]

Glad to help.

Jim
--
Best wishes,

Jim

Yeah, I think you're right. If I manually replicate what the macro does, I
get the link too. Hm. Wierd.

Well, at least we know for sure it's harmless. Thanks.