CODEWITHSUNDEEP
vbaexcel
JS Bach
JS Bach

Reputation: 83

How to open a file using Excel VBA and turn off updating links

I currently have a macro where I open up another file, copy data, and paste the data in my file with macro. The feeder file where I copy the data has links to other other workbooks. How would I have the macro continue without prompting me to update links.

Sub FeedFiles()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Workbooks.Open Filename:= _
    "T:\Planning\FY Budget\2018 Budget\Director Templates\With updates\2018 Budget PL_HC_CAP - Thomson_V2.xlsx" _
, Password:="Thomson18"
Workbooks("2018 Budget PL_HC_CAP -Thomson_V2.xlsx").Worksheets("Summary").Range("A1:AH227").Activate
Workbooks("2018 Budget PL_HC_CAP - Thomson_V2.xlsx").Worksheets("Summary").Range("A1:AH227").Copy
Workbooks("2018 Budget PL_HC_CAP - Total 802.xlsm").Worksheets("Thomson").Range("A1:AH227").PasteSpecial xlPasteValues
Workbooks("2018 Budget PL_HC_CAP - Thomson_V2.xlsx").Close

 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 End Sub

Upvotes: 1

Views: 16521

Answers (2)

Jaya Gangapurkar
Jaya Gangapurkar

Reputation: 1

Use False to disable updating links; e.g.,

Workbooks.Open(xxx.xlsx, False)

I was further getting a Type Mismatch (Run-time Error 13). In order to suppress the error window, use On Error Resume Next before the Workbook.open command.

The below steps should work seamlessly to avoid this issue.

On Error Resume Next
Set EIB = Workbooks.Open(xxxx, False)
Application.ScreenUpdating = False

Upvotes: -1

Maciej Los
Maciej Los

Reputation: 8591

In A Short: Workbooks.Open method has several parameters. One of them is UpdateLinks which you have to set to false.

Dim wbk As Workbook
Set wbk = Application.Workbooks.Open(FileName:="FullePathToExcelFile", UpdateLinks:=False)

Try! Good luck!

Upvotes: 3

Related Questions