Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Open Excel Workbook with links from Access

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".
0
kobys
Asked:
kobys
  • 3
  • 2
1 Solution
 
kobysAuthor Commented:
Figured out the way the answer to the second part of my question about saving the file.  Used:

 objWkb.Save
 objWkb.Close
0
 
Rey Obrero (Capricorn1)Commented:
how are you opening the excel file

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

xlfile is complete path and name.
0
 
kobysAuthor Commented:
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")
    rsDates.MoveFirst

' 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
        ObjWkb.Save
        ObjWkb.Close
        rsDates.MoveNext

    Loop

    rsDates.Close
    objXL.Quit
    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.

Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
change this

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


with


Set ObjWkb = objXL.Workbooks.Open("Y:\Susan and Rob\Equities\Backtests\Equity Optimizer " & Ret_Date & ".xls",true)
0
 
kobysAuthor Commented:
Seems to work if I add UPDATELINKS:=False to my Set ObjWkb line.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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