Solved

Turn off update links using VBA

Posted on 2009-04-13
4
1,933 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

929 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

10 Experts available now in Live!

Get 1:1 Help Now