Disable update links messages on workbooks opened through VBA in template file

[ChrisYbelt]

I have an Excel 2007 macro-enabled template to open all workbooks in a given directory, copy all the sheets back into the template file, then perform some analysis. The issue I'm coming across is that when the code cycles through all the workbooks in my directory, on occasion it will come across a workbook that has links to other files. This causes an alert to pop up saying "This workbook contains one or more links that cannot be updated...." I was able to disable this prompt when the file is opened by adding Application.AskToUpdateLinks = False and Application.DisplayAlerts = False

However, when the worksheet is copied from the source file back into my template file, I get an explorer browser window asking for the path of the file to update the links. Is there was way to disable this too?

 

[GlennUK]

Do you actually need to copy the links to your Template? Can you copy the values of the sheet instead of the sheet object?

 

[ChrisYbelt]

Do you actually need to copy the links to your Template? Can you copy the values of the sheet instead of the sheet object?

I do not need to copy the links, just the data. I think I know where you're going with this...

The code copies the worksheet of the source files to my template file, not just the cell values, so it isn't like I'm just copy/pasting a range of cells (which would be solved with paste special > values). Doing it this way was easier to code rather than having it identify the range of cells to copy into the template and renaming the new sheet in the template to the same as the source file's sheet name.

 

[GlennUK]

You can convert the source sheet to values before you copy the sheet across by using something like:
Activesheet.UsedRange.Value=ActiveSheet.UsedRange.Value
... I assume that you are closing the source workbooks without saving them?

 

[ChrisYbelt]

I tried adding the code you suggested but received some error. It had something to do with one of the files having a sheet with a pivot table in it.

After your first response, I started thinking more about the paste special > values. I ended up adding the code for select all, paste values before the sheet was copied to my combined template.

Problem solved.

 

[GlennUK]

That's great.