Link to home
Start Free TrialLog in
Avatar of creativefusion
creativefusionFlag for Australia

asked on

Urgent!! Access VBA Turn Off Excel External Link Prompt when using DAO RS Transfer

All,

I am having a problem trying to find out how to disbale a prompt that keeps appearing when I am transferring a recordset from Access to Excel.

The code enclosed, transfers a SQL query recordset using DAO fine, however, when it opens the workbook, I keep getting a prompt about external links, do I want to update them? etc. I need to somehow disable this.

Can anyone tell me how to fix this please?

CF
Public Function ExportReserves()
On Error GoTo Err_Handler
    
    Dim rs As DAO.Recordset
    Dim xlObj As Object, Sheet As Object
    Dim xlFile As String
       
    Call showProcessStatus("Transferring latest reserve capacities to MCP workbook...")
    Forms("SF_PROCESS_STATUS").Repaint
        
    'SPECIFYING THE FILENAME TO USE ==>
    xlFile = "\\Nthocp05fs001\NetworkPlanning\MCP\01_DEVELOPMENT\MCP_3.23_OCTOBER_2011.xls"
    
    'CREATING EXCEL OBJECT ==>
    Set xlObj = CreateObject("Excel.Application")
    xlObj.DisplayAlerts = False
    xlObj.Workbooks.Open xlFile
    xlObj.Visible = True
    
    'COPY THE RECORDS FROM THE QUERY TO EXCEL FILE ==>
    Set rs = CurrentDb.OpenRecordset("Q_RESERVES_BY_DC_LOCATION")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Reserves")
    Sheet.Range("A3").CopyFromRecordset rs  'copy the data
    Set rs = Nothing
        
    'SAVING THE FILE ==>
    xlObj.ActiveWorkbook.Save
    
    'SETTING A DELAY OF 2 SECONDS TO ENSURE SAVE PROCESS LAG IS ELIMINATED ==>
    xlObj.Application.Wait Now + TimeValue("00:00:09")
    
    'CLOSING CONNECTIONS ==>
    Set Sheet = Nothing
    xlObj.Application.DisplayAlerts = True
    xlObj.Quit
    Set xlObj = Nothing
     
Err_Handler:

If Err.Number = 0 Then
    Call addLogFile("SYSTEM", 5, "Info! Transfered latest reserve capacitites to MCP workbook")
Else
    Call addLogFile("SYSTEM", 6, "Error! Transfering latest reserves capacities to MCP workbook")
End If

Call hideProcessStatus

End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of datAdrenaline
datAdrenaline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial