creativefusion
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.