Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Create Excel From Access Turn Off Excel Alerts

    Hi all,

    Using Office 2016.

    Doing some work with Excel files from MS Access. Working well except I can't seem to get rid of the "User Replace Warning"

    I tried turning off alerts

    Code:
     'Turn off Excel Alerts
            oXl.DisplayAlerts = False
    and I tried LocalSessionChanges for ConflictResolution

    Code:
        'Save the workbook
            oWb.SaveAs FileName:=strFolderTemp & strFileName, _
                       ConflictResolution:=gclxlLocalSessionChanges
    Any ideas?

    thx
    w

    Complete code:
    Code:
    Private Sub cmdImportJDEExtractFile_Click()
        
        'Declare variables
            Dim strFullFileName As String
            Dim strFileName As String
            Dim blnExist As Boolean
            
        'Declare Objects
            Dim oXl As Object
            Dim oWb As Object
            Dim oWs As Object
            
        'Declare constants
            Const strFolderTemp As String = "C:\Tmp\"
            
        'Does the temp folder exist
            blnExist = FileFolderExists(strFullPath:=strFolderTemp)
        
        'If temp folder does not exists, create it
            If blnExist = True Then
                'Do nothing
            Else
                MkDir (strFolderTemp)
            End If
    
        'Get file for import - returns full file name
            strFullFileName = GetFDObjectName(strDialogType:="File")
            
        'Get just file name and extension
            strFileName = GetSegmentsFromFullFileName(strCompleteFileName:=strFullFileName, _
                                                      strSubstringType:="File Name")
        
        'Get Excel Application
            Set oXl = GetExcelApp
            
        'Turn off Excel Alerts
            oXl.DisplayAlerts = False
            
        'Get Excel Workbook
            Set oWb = GetExcelWorkbook(xl:=oXl, _
                                       strFileName:=strFullFileName)
                                       
        'Get Excel Worksheet
            Set oWs = GetExcelWorksheet(wb:=oWb)
            
        'Field 28 of the extract file needs to be renamed
            oWs.Range("AB1").Value = "Field28"
        
        'Save the workbook
            oWb.SaveAs FileName:=strFolderTemp & strFileName, _
                       ConflictResolution:=gclxlLocalSessionChanges
            
        'Close the workbook
            oWb.Close
            
        'Quit Excel
            oXl.Quit
            
        'Tidy up
            Set oWs = Nothing
            Set oWb = Nothing
            oXl.DisplayAlerts = True
            Set oXl = Nothing
    
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You could try using the access macro set warnings.

    If you do use it then remember to turn back on after whatever you want suppressing has completed otherwise you will have mysterious unfixable errors as messages keep being suppressed
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    Unfortunately, that did not resolve the Warning Dialog.
    The title on the Dialog does read "Excel"

    thx
    w

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •