Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Link Appearing Automatically
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. -- Hmm...they have the Internet on COMPUTERS now! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Link Appearing Automatically
What is the wording in the macro where the data is pasted in the new
Workbook? A change there might be what is needed. -- Best wishes, Jim "MDW" wrote: 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. -- Hmm...they have the Internet on COMPUTERS now! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Link Appearing Automatically
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_w orkbook_name, "store_monthly_output", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_name, "monthly_summary", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_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_h ere, 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 -- Hmm...they have the Internet on COMPUTERS now! "Jim Jackson" wrote: What is the wording in the macro where the data is pasted in the new Workbook? A change there might be what is needed. -- Best wishes, Jim "MDW" wrote: 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. -- Hmm...they have the Internet on COMPUTERS now! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Link Appearing Automatically
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 -- Best wishes, Jim "MDW" wrote: 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_w orkbook_name, "store_monthly_output", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_name, "monthly_summary", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_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_h ere, 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 -- Hmm...they have the Internet on COMPUTERS now! "Jim Jackson" wrote: What is the wording in the macro where the data is pasted in the new Workbook? A change there might be what is needed. -- Best wishes, Jim "MDW" wrote: 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. -- Hmm...they have the Internet on COMPUTERS now! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Link Appearing Automatically
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. -- Hmm...they have the Internet on COMPUTERS now! "Jim Jackson" wrote: 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 -- Best wishes, Jim "MDW" wrote: 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_w orkbook_name, "store_monthly_output", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_name, "monthly_summary", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_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_h ere, 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 -- Hmm...they have the Internet on COMPUTERS now! "Jim Jackson" wrote: What is the wording in the macro where the data is pasted in the new Workbook? A change there might be what is needed. -- Best wishes, Jim "MDW" wrote: 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. -- Hmm...they have the Internet on COMPUTERS now! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Link Appearing Automatically
Glad to help.
Jim -- Best wishes, Jim "MDW" wrote: 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. -- Hmm...they have the Internet on COMPUTERS now! "Jim Jackson" wrote: 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 -- Best wishes, Jim "MDW" wrote: 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_w orkbook_name, "store_monthly_output", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_name, "monthly_summary", copy_to_workbook_name) Call copy_this_work_sheet_to_that_worksheet(copy_from_w orkbook_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_h ere, 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 -- Hmm...they have the Internet on COMPUTERS now! "Jim Jackson" wrote: What is the wording in the macro where the data is pasted in the new Workbook? A change there might be what is needed. -- Best wishes, Jim "MDW" wrote: 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. -- Hmm...they have the Internet on COMPUTERS now! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange letters appearing in Excel 97 | Excel Discussion (Misc queries) | |||
values are appearing automatically in the next column | Excel Discussion (Misc queries) | |||
How to stop reviewing bar from automatically appearing | Excel Discussion (Misc queries) | |||
How to prevent "Reviewing" toolbar from "automatically" appearing? | Excel Discussion (Misc queries) | |||
toolbar automatically appearing | Excel Discussion (Misc queries) |