Solved

Turn off update links using VBA

Posted on 2009-04-13
4
1,931 Views
Last Modified: 2013-11-27
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
Comment
Question by:LizzJ
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24135364
Hello LizzJ,

Try using the constant 3 instead of xlUpdateLinksAlways

Regards,
Chris
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 24135372
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24135384
Just checking  - I presume you want to update links, but kill the message box?
0
 

Author Comment

by:LizzJ
ID: 24135503
Thank you all. But Dave's solution works.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now