|
本帖最后由 乐乐2006201506 于 2016-6-24 11:45 编辑
怎样使下面代码在打开一个工作薄写入代码后,间隔一定时间另存,关闭,然后再打开第二个工作簿,间隔一定时间保存,关闭……以此类推。谢谢!
附件在([已解决]实现另存为代码正常运行,http://www.excelpx.com/thread-419221-1-1.html)
Dim ArrFiles(1 To 10000)
Dim cntFiles%
Public Sub ListAllFiles()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim strPath$
Dim i%
Dim fso As New FileSystemObject, fd As Folder
strPath = "C:\Users\Administrator\Desktop\写入代码多层子文件夹\花名册测试文件\"
cntFiles = 0
Set fd = fso.GetFolder(strPath)
SearchFiles fd
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Sub SearchFiles(ByVal fd As Folder)
- Dim fl As File
- Dim sfd As Folder
- For Each fl In fd.Files '通过循环把文件逐个放在数组内
- cntFiles = cntFiles + 1
- ArrFiles(cntFiles) = fl.Path
- If fl Like "*.xls" Then
- Workbooks.Open fl
- With ActiveWorkbook.VBProject.VBComponents("thisworkbook").CodeModule
- .InsertLines 1, "sub test()"
- .InsertLines 2, "msgbox ""just a test""" '双引号中的双引号,2个代表1个.
- .InsertLines 3, "end sub"
- End With
- ActiveWorkbook.SaveAs Filename:=Replace(fl, ".xls", ".xlsm"), FileFormat:=52
- ActiveWorkbook.Close True
- End If
- Next fl
- If fd.SubFolders.Count = 0 Then Exit Sub 'SubFolders返回由指定文件夹中所有子文件夹(包括隐藏文件夹和系统文件夹)组成的 Folders 集合
- For Each sfd In fd.SubFolders '在 Folders 集合进行循环查找
- SearchFiles sfd '使用递归方法查找下一个文件夹
- Next
- End Sub
加个延迟API函数最简单: - Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
- Dim ArrFiles(1 To 10000) '创建一个数组空间,用来存放文件名称
- Dim cntFiles% '文件个数
- Public Sub ListAllFiles()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim strPath$ '声明文件路径
- Dim i%
- 'Set fso = CreateObject("Scripting.FileSystemObject")
- Dim fso As New FileSystemObject, fd As Folder '创建一个FileSystemObject对象和一个文件夹对象
- strPath = "C:\Users\Administrator\Desktop\写入代码多层子文件夹\花名册测试文件" '"设置要遍历的文件夹目录
- cntFiles = 0
- Set fd = fso.GetFolder(strPath) '设置fd文件夹对象
- SearchFiles fd '调用子程序查搜索文件
- ' Sheets(1).Range("A1").Resize(cntFiles) = Application.Transpose(ArrFiles) '把数组内的路径和文件名放在单元格中
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
- Sub SearchFiles(ByVal fd As Folder)
- Dim fl As File
- Dim sfd As Folder
- For Each fl In fd.Files '通过循环把文件逐个放在数组内
- cntFiles = cntFiles + 1
- ArrFiles(cntFiles) = fl.Path
- If fl Like "*.xls" Then
- Workbooks.Open fl
- With ActiveWorkbook.VBProject.VBComponents("thisworkbook").CodeModule
- .InsertLines 1, "sub test()"
- .InsertLines 2, "msgbox ""just a test""" '双引号中的双引号,2个代表1个.
- .InsertLines 3, "end sub"
- End With
- Sleep 3000 '延迟时间,毫秒
- ActiveWorkbook.SaveAs Filename:=Replace(fl, ".xls", ".xlsm"), FileFormat:=52
- ActiveWorkbook.Close True
- End If
- Next fl
- If fd.SubFolders.Count = 0 Then Exit Sub 'SubFolders返回由指定文件夹中所有子文件夹(包括隐藏文件夹和系统文件夹)组成的 Folders 集合
- For Each sfd In fd.SubFolders '在 Folders 集合进行循环查找
- SearchFiles sfd '使用递归方法查找下一个文件夹
- Next
- End Sub
复制代码
|
|