CODEWITHSUNDEEP
excelvba
Bill_B
Bill_B

Reputation: 11

Unintended file dialog when updating links in Excel 2003 VBA

I'm keeping sets of interrelated Excel 2003 spreadsheets for each of my company's projects.

I want to copy some template XLS files to the project name and change the links that connect them to each other.

For example, the file TEMPLATE_ScanProgress.xls links to TEMPLATE_Film_Review.xls.

I am copying them both to 123456_ScanProgress.xls and 123456_Film_Review.xls, and updating the link in 123456_ScanProgress.xls.

Sample code of what I'm doing:

If Dir("WorkOrder & "_ScanProgress.xls") = "" Then
    FileCopy "TEMPLATE_ScanProgress.xls", WorkOrder & "_ScanProgress.xls"
    Workbooks.Open Filename:=WorkOrder & "_ScanProgress.xls", UpdateLinks:=0
    ActiveWorkbook.ChangeLink "TEMPLATE_Film_Review.xls", _
                              WorkOrder & "_Film_Review.xls", _
                              xlLinkTypeExcelLinks
    Workbooks(WorkOrder & "_ScanProgress.xls").Close SaveChanges:=True
Else
    FileExists = True
    FileExistsWarning_7 = WorkOrder & "_ScanProgress.xls"
End If

The problem is that when the code tries to update the link I get a file dialog asking me to choose a file for the change, even though I already specified which file I want in the code.

Upvotes: 1

Views: 531

Answers (1)

Todd
Todd

Reputation: 6169

Try setting DisplayAlerts to False. DisplayAlerts is on the Application object and is used to suppress dialog boxes for example when overwriting a file. It may work in this case too.

Upvotes: 1

Related Questions