如何使用VBA遍历文件夹及其子文件夹中的所有excel,并打开后进行修改?
本人刚开始学,小白一个,下面是我的代码,麻烦各位大大们帮忙看看,错出在哪儿了。我想达到的效果是,搜索一个文件夹里的所有excel(包含子文件夹),打开后添加一个图片页眉,...
本人刚开始学,小白一个,下面是我的代码,麻烦各位大大们帮忙看看,错出在哪儿了。
我想达到的效果是,搜索一个文件夹里的所有excel(包含子文件夹),打开后添加一个图片页眉,然后保存再关闭。
Sub Test() Dim MyName, Dic, Did, I, a, T, f, TT, MyFileName Dim Arr(500) As String T = Time Set Dic = CreateObject("Scripting.Dictionary") Set Did = CreateObject("Scripting.Dictionary") Dic.Add ("C:\Users\Desktop\Concern\"), "" I = 0 Do While I < Dic.Count Ke = Dic.keys MyName = Dir(Ke(I), vbDirectory) Do While MyName <> "" If MyName <> "." And MyName <> ".." Then If (GetAttr(Ke(I) & MyName) And vbDirectory) = vbDirectory Then Dic.Add (Ke(I) & MyName & "\"), "" End If End If MyName = Dir Loop I = I + 1 Arr(a) = MyName a = a + 1 Loop For a = 1 To I Workbooks.Open Filename:="C:\Users\Desktop\Concern\" & Arr(a) Application.PrintCommunication = False With ActiveSheet.PageSetup .LeftHeader = "&G" With .LeftHeaderPicture .Filename = “C:\Users\Desktop\c.png" End With End With Application.PrintCommunication = True ActiveWorkbook.Save ActiveWorkbook.Close Next TT = Time - T MsgBox Minute(TT) & "分" & Second(TT) & "秒"End Sub
不好意思,代码很乱,只好上图了~ 展开
我想达到的效果是,搜索一个文件夹里的所有excel(包含子文件夹),打开后添加一个图片页眉,然后保存再关闭。
Sub Test() Dim MyName, Dic, Did, I, a, T, f, TT, MyFileName Dim Arr(500) As String T = Time Set Dic = CreateObject("Scripting.Dictionary") Set Did = CreateObject("Scripting.Dictionary") Dic.Add ("C:\Users\Desktop\Concern\"), "" I = 0 Do While I < Dic.Count Ke = Dic.keys MyName = Dir(Ke(I), vbDirectory) Do While MyName <> "" If MyName <> "." And MyName <> ".." Then If (GetAttr(Ke(I) & MyName) And vbDirectory) = vbDirectory Then Dic.Add (Ke(I) & MyName & "\"), "" End If End If MyName = Dir Loop I = I + 1 Arr(a) = MyName a = a + 1 Loop For a = 1 To I Workbooks.Open Filename:="C:\Users\Desktop\Concern\" & Arr(a) Application.PrintCommunication = False With ActiveSheet.PageSetup .LeftHeader = "&G" With .LeftHeaderPicture .Filename = “C:\Users\Desktop\c.png" End With End With Application.PrintCommunication = True ActiveWorkbook.Save ActiveWorkbook.Close Next TT = Time - T MsgBox Minute(TT) & "分" & Second(TT) & "秒"End Sub
不好意思,代码很乱,只好上图了~ 展开
展开全部
把 .Filename = “C:\Users\Desktop\c.png"换成 .Filename ="C:\Users\Desktop\c.png"试试,=号后面的双引号不是英文字符
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
open那里没有写到具体。xlx格式
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
和规范和规范和规范化
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询