• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2009
  • Last Modified:

Turn off update links using VBA

Hi Experts,

I'm having trouble turning off the dialog box for user to choose update/edit the links in a Excel workbook using VBA. The codes are written in Access:

The prompt still appears although I set it as update always.
Can anyone help?
Thanks.
Dim acExcel, wb
     Dim xlPath
     xlPath = "F:\xxxxxx"
     Set acExcel = CreateObject("Excel.Application")
     acExcel.Visible = True
     Set wb = acExcel.Workbooks.Open(Filename:=xlPath, UpdateLinks:=xlUpdateLinksAlways)
     With wb.worksheets("Sheet1")
     '--------work on the data-------------
     End With
     wb.Close False
     Set wb = Nothing
     acExcel.Quit
     Set acExcel = Nothing

Open in new window

0
LizzJ
Asked:
LizzJ
  • 2
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Hello LizzJ,

Try using the constant 3 instead of xlUpdateLinksAlways

Regards,
Chris
0
 
DaveCommented:
You could turn off alerts as below
Cheers
Dave

xlPath = "c:\test2"
     Set acExcel = CreateObject("Excel.Application")
     acExcel.Visible = True
     acExcel.DisplayAlerts = False
     Set wb = acExcel.Workbooks.Open(Filename:=xlPath, UpdateLinks:=xlUpdateLinksAlways)
     With wb.Worksheets("Sheet1")
     '--------work on the data-------------
     End With
     wb.Close False
     Set wb = Nothing
        acExcel.DisplayAlerts = True
     acExcel.Quit
     Set acExcel = Nothing

Open in new window

0
 
DaveCommented:
Just checking  - I presume you want to update links, but kill the message box?
0
 
LizzJAuthor Commented:
Thank you all. But Dave's solution works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now