Results 1 to 2 of 2

Thread: OnKey Not Working

  1. #1

    OnKey Not Working


    Still working on my Macro. The OnKey line (Foo.Application.OnKey "%{F11}", "") is not disabling the Alt+F11 key sequence in the Excel files that I am opening and closing. Anyone have any idea why its not disabling the Alt+F11 sequence??


    Sub FindClientExcelFiles()
    Dim FS As Office.FileSearch
    Dim vaFileName As Variant
    Dim startdir
    Dim enddir
    Dim Foo As Object
    Dim iCount As Long
    Dim newname As Variant
    Dim fsoObj As Object, TheDate As String
    Dim strPassWord As String
    Dim commandbars As CommandBar

    TheDate = Format(Date, "YYYYMMDD")

    startdir = "C:\Temp\1"
    enddir = ("C:\Temp\" & TheDate & "\")

    Set fsoObj = CreateObject("Scripting.FileSystemObject")
    With fsoObj
    If Not .FolderExists(enddir) Then
    .CreateFolder (enddir)
    End If
    End With
    Set FS = Application.FileSearch
    With FS
    'Clear old search criteria
    'Directory to search
    .LookIn = startdir
    'Include sub folders in search
    .SearchSubFolders = True
    'Look for Excel files
    .FileType = msoFileTypeExcelWorkbooks
    iCount = .Execute
    'List the files in the FoundFiles collection
    For Each vaFileName In .FoundFiles
    If FileDateTime(vaFileName) < Now() - 2 / (24 * 60) Then
    Set Foo = Workbooks.Open(vaFileName)
    Foo.Application.commandbars("Worksheet Menu Bar").Controls("Tools").Enabled = False
    Foo.Application.OnKey "%{F11}", ""
    Application.DisplayAlerts = False
    Foo.SaveAs enddir & Foo.Name
    Application.DisplayAlerts = True
    Kill vaFileName

    End If

    Next vaFileName
    End With
    End Sub

  2. #2
    Moderator VBAX Master austenr's Avatar
    Sep 2004
    Peace of mind is found in some of the strangest places.

Posting Permissions

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