Open Excel Workbook with links from Access

Posted on 2011-05-13
Last Modified: 2013-11-27
I am opening up Excel files from Access but the files have links so I get prompted each time about these.  Is there a way to supress these pop-up windows or force a "Yes" and then a "Continue".

Also, I want to close the workbook and save changes but using
objWkb.Close SaveChanges:=True
It prompts me to ask if I want to save the file as "Copy of filename".
Question by:kobys

    Author Comment

    Figured out the way the answer to the second part of my question about saving the file.  Used:

    LVL 119

    Accepted Solution

    how are you opening the excel file

         try this
    dim xlObj as object
    set xlObj=createobject("excel.application") xlFile, true

    xlfile is complete path and name.

    Author Comment

    Here's my code:
    Public Sub WriteExpRet_Obj()
    ' Make sure expected returns are in table ZScore_Optimizer_Input
    ' Change name of destination file
        Dim Ret_Date As String
        Dim strQuery As String
        Dim rsDates As Recordset
        Dim rsMRets As Recordset
        Dim CurrDate As Date
        Set objXL = New Excel.Application
        Set rsDates = CurrentDb.OpenRecordset("ME_TD_to_ME_Dates")
    ' Loop through all Months
        Do While Not rsDates.EOF
            CurrDate = rsDates.fields("[Date]")
            strQuery = "SELECT * FROM All_Optimizer_Inputs where Date = #" & CurrDate & "# ORDER BY All_Optimizer_Inputs.Exp_Ret DESC"
            CurrentDb.QueryDefs("Monthly_Opt_Inputs").SQL = strQuery
            Ret_Date = Format(CurrDate, "yyyymmdd")
            Set ObjWkb = objXL.Workbooks.Open("Y:\Susan and Rob\Equities\Backtests\Equity Optimizer " & Ret_Date & ".xls")
            Set rsMRets = CurrentDb.OpenRecordset("Monthly_Opt_Inputs", dbOpenSnapshot)
            Set ObjSht = ObjWkb.Worksheets("Monthly_Opt_Inputs")
            ObjSht.Range("A1").CopyFromRecordset rsMRets
            Set rsMRets = Nothing
        Set objXL = Nothing
        Set ObjWkb = Nothing
        Set ObjSht = Nothing
    End Sub

    Open in new window

    The particularly strange thing is I don't have any references to any files in my excel cells.  When I view links just my personal.xla macro file and a third-party add-in show up.

    LVL 119

    Expert Comment

    by:Rey Obrero
    change this

    Set ObjWkb = objXL.Workbooks.Open("Y:\Susan and Rob\Equities\Backtests\Equity Optimizer " & Ret_Date & ".xls")


    Set ObjWkb = objXL.Workbooks.Open("Y:\Susan and Rob\Equities\Backtests\Equity Optimizer " & Ret_Date & ".xls",true)

    Author Comment

    Seems to work if I add UPDATELINKS:=False to my Set ObjWkb line.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    754 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

    25 Experts available now in Live!

    Get 1:1 Help Now