brianlois
03-15-2005, 08:42 AM
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
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