[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-13
1
Medium Priority
?
377 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:creativefusion
1 Comment
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 2000 total points
ID: 36966379
I am unsure if you are thinking that your code creates a link to the data in the excel sheet .. it doesn't ..

But, I think you are saying when you execute .Workbook.Open line, you are prompted right?  If that is the case -- There is a setting (in Excel) in the Connection Properties of your linked data that has a property (a check box) that indicates if you want to Refresh your data upon file open.  If I am not mistaken (which I could be :-s ) you can UNCHECK that box, then you won't be prompted.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

867 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