Consulting

Results 1 to 2 of 2

Thread: Sleeper: OnKey Not Working

  1. #1

    Sleeper: OnKey Not Working

    Hello,

    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??

    Thanks,
    Brian

    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
        .NewSearch
        '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
            Foo.Close
            Application.DisplayAlerts = True
            Kill vaFileName
    End If
    Next vaFileName
      End With
    End Sub

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    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
  •