VBA快速修改多个Excel的内容 求vba代码批量修改文件夹下Excel指定单元格内容？
excel\u4e2d\u7528vba\u5feb\u901f\u5220\u9664\u591a\u4e2a\u5de5\u4f5c\u8868\u5185\u6307\u5b9a\u7684\u5185\u5bb9Vba\u4ee3\u7801\uff0c\u82e5\u4f60\u7684\u5de5\u4f5c\u8868\u540d\u79f0\u6ca1\u6709\u89c4\u5f8b\u53ef\u4ee5\u5c31\u7a0d\u5fae\u8d39\u4e8b\u70b9\uff0c\u82e5\u662f\u6709\u89c4\u5f8b\u53ef\u4ee5\u7528 For each ...in...next..
sub Delete_All()
with sheets("Sheet1")
.range("A2:C5").clearContents'\u7f16\u8f91\u4f60\u6307\u5b9a\u7684\u8303\u56f4\uff0c\u8fd9\u662f\u6e05\u9664\u5185\u5bb9\uff0c\u5220\u9664\u5355\u5143\u683c\u8981\u7528Delete
end with
'\u628a\u8fd9\u4e09\u884c\u4ee3\u7801\u590d\u5236\u7c98\u8d34\uff0c\u7136\u540e\u4fee\u6539Sheet\u540d\u5b57
end sub
Excel\u600e\u6837\u5feb\u901f\u6279\u91cf\u5c06\u76f8\u540c\u8868\u5f0f\u591a\u6587\u4ef6\u6570\u636e\u4fee\u6539
Sub MultiModi()
Dim wb As Workbook
Dim ws As Worksheet
Dim fn
fn = Dir("盘符:\文件所在的完整路径名\*.xls") '取得第一个工作簿的文件名（如果是2007版，则后缀名请改为 *.xlsx）
Do While fn <> ""
Set wb = Workbooks.Open(fn) '打开工作簿
For Each ws In wb.Worksheets '循环工作簿中的所有Sheet
With ws
.Range("M6").Formula = "=F6*100" '在M6中输入公式 =F6*100
'将M6的公式复制粘贴到M:P列中，行数到F列的最后一行为止
.Range("M6").Copy Destination:=.Range("M6").Resize(.Range("F65536").End(xlUp).Row - 5, 4)
End With
Next ws '循环下一张Sheet
wb.Close savechanges:=True '保存并关闭工作簿
fn = Dir() '取得下一个工作簿的文件名
Loop '循环
End Sub
把文件放到桌面上 文件夹"12"中 代码如下
Sub 文件批量修改()
Dim myfiles
Dim i As Long, j
myfiles = Dir("C:\Documents and Settings\Administrator\桌面\12\*.xls*") '文件位置
On Error Resume Next
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Do While myfiles <> ""
Workbooks.Open Filename:="C:\Documents and Settings\Administrator\桌面\12\" & myfiles
For j = 1 To Sheets.Count
Sheets(j).Activate
For i = 6 To ActiveSheet.UsedRange.Rows.Count
Cells(i, 13) = Cells(i, 6) * 100
Cells(i, 14) = Cells(i, 7) * 100
Cells(i, 15) = Cells(i, 8) * 100
Cells(i, 16) = Cells(i, 9) * 100
Next
Next
ActiveWorkbook.Save
ActiveWorkbook.Close
myfiles = Dir
Loop
End Sub
for i=1 to n
for j=6 to 9
sheets(i).cells(6,i+7)=sheets(i).cells(6,i)*100
next j
next i
绛旓細鍦 Excel 涓墦寮 VBA 缂栬緫鍣ㄣ浣犲彲浠ラ氳繃鎸変笅 Alt + F11 蹇嵎閿潵鎵撳紑瀹冦傚湪 VBA 缂栬緫鍣ㄤ腑锛岄夋嫨 "鎻掑叆" 鑿滃崟锛岀劧鍚庨夋嫨 "妯″潡"銆傚湪鏂板垱寤虹殑妯″潡涓紝澶嶅埗骞剁矘璐翠互涓嬩唬鐮侊細vba澶嶅埗浠ｇ爜 Sub鎵归噺淇敼Excel鏂囦欢()Dim MyFolder As String Dim MyFile As String Dim MyWorkbook As Workbook ' 璁剧疆鏂...
绛旓細鎸堿LT+F11锛屾墦寮VBE缂栬緫鍣紝鎻掑叆鈥斺旀ā鍧楋紝澶嶅埗涓嬮潰鐨勪唬鐮侊紝鎸塅5閿繍琛岋紝鍗冲彲瀹屾垚瀹炵幇excel鎵归噺鏇挎崲涓嬫爣銆係ub 鎵归噺鏇挎崲涓嬫爣() For i = 1 To [a65536].End(3).Row Cells(i, 1).Characters(Len(Cells(i, 1)), 1)...
绛旓細Sub MultiModi()Dim wb As WorkbookDim ws As WorksheetDim fnfn = Dir("鐩樼:\鏂囦欢鎵鍦ㄧ殑瀹屾暣璺緞鍚峔*.xls") '鍙栧緱绗竴涓伐浣滅翱鐨勬枃浠跺悕锛堝鏋滄槸2007鐗堬紝鍒欏悗缂鍚嶈鏀涓 *.xlsx锛塂o While fn <> "" Set wb = Workbooks.Open(fn) '鎵撳紑宸ヤ綔绨 For Each ws In wb.Worksheets '寰...
绛旓細鍦ㄤ竴浜涙搷浣滀腑锛屽線寰浼氶渶瑕佸皢澶氫釜宸ヤ綔绨胯繘琛屽悎骞躲涓鑸殑鎿嶄綔鏂规硶閮芥槸鎵撳紑涓や釜宸ヤ綔绨匡紝鐒跺悗閫変腑闇瑕佺Щ鍔ㄧ殑宸ヤ綔琛紝鍙抽敭鍗曞嚮浠ュ悗閫夋嫨鈥滅Щ鍔ㄦ垨澶嶅埗鈥銆傛帴涓嬫潵鍦ㄦ柊鐨勭獥鍙ｉ噷闈㈣繘琛岃缃氨鍙互浜嗐傝繖绉嶆柟娉曢傚悎鍦ㄧЩ鍔ㄦ暟閲忚緝灏戠殑宸ヤ綔琛ㄧ殑鏃跺欎娇鐢ㄣ傚鏋滄湁寰堝鐨勫伐浣滅翱锛岄兘闇瑕佽繘琛岀Щ鍔ㄧ殑璇濓紝涓涓竴涓墦寮鐒跺悗鍐嶆搷浣...
绛旓細濡備綍瀵瑰悓涓鏂囦欢澶逛笅澶氫釜鐩稿悓EXCEL琛ㄦ牸涓浉鍚屽崟鍏冩牸鍐呭杩涜鎵归噺淇敼 杩欎釜寤鸿浣跨敤vba鏉ラ亶鍘嗘枃浠讹紝骞惰繘琛屼慨鏀圭殑 鍙互浣跨敤dir鎴栬協so鏂囦欢绯荤粺杩涜鏂囦欢閬嶅巻澶勭悊鐨 濡備綍瀵瑰悓涓涓枃浠跺す涓嬪嚑鍗佷釜鐩稿悓excel琛ㄦ牸鐩稿悓鐨勫崟鍏冩牸杩涜鎵归噺鏇挎崲 鏈変袱涓柟妗堬細1.鐢ㄥ嚱鏁帮紙濡傛灉鏂囦欢鏁伴噺涓嶆槸寰堝鐨勮瘽锛夛細閫夊畾涓涓负涓昏〃锛...
绛旓細澶勭悊澶氫釜Excel鏂囦欢鏃讹紝灏嗘枃浠朵緷娆℃墦寮锛岀劧鍚庡啀鍒╃敤浠ｇ爜澶勭悊鏁版嵁鍗冲彲 涓嬮潰浠ｇ爜涓烘墦寮Excel鏂囦欢锛屽苟鎻愬彇sheet1涓殑绗竴琛屾暟鎹埌鏈〃涓細Sub main()f = Dir(ThisWorkbook.Path & "\*.xlsx")'鎵撳紑鍒跺畾鏂囦欢澶逛腑鐨勬墍鏈塃xcel鏂囦欢 Do While f <> ""Workbooks.Open (ThisWorkbook.Path & "\" & f)'渚濇...
绛旓細Sub 鎵归噺鎿嶄綔()Application.ScreenUpdating = False '//鍏抽棴灞忓箷鍒锋柊 Application.DisplayAlerts = False '//鍏抽棴绯荤粺鎻愮ず n = 1 mypath = ThisWorkbook.Path & "\"myfile = Dir(mypath)benname = ThisWorkbook.Name Do While myfile <> ""If myfile <> ThisWorkbook.Name Then Workbooks.Open ...
绛旓細澶勭悊澶氫釜Excel鏂囦欢鏃讹紝灏嗘枃浠朵緷娆℃墦寮锛岀劧鍚庡啀鍒╃敤浠ｇ爜澶勭悊鏁版嵁鍗冲彲 涓嬮潰浠ｇ爜涓烘墦寮Excel鏂囦欢锛屽苟鎻愬彇sheet1涓殑绗竴琛屾暟鎹埌鏈〃涓細Sub main()f = Dir(ThisWorkbook.Path & "\*.xlsx")'鎵撳紑鍒跺畾鏂囦欢澶逛腑鐨勬墍鏈塃xcel鏂囦欢 Do While f <> ""Workbooks.Open (ThisWorkbook.Path & "\" & f)'渚濇...
绛旓細strings=Array("鏄熸湡涓鈥滐紝鈥濇槦鏈熶簩鈥滐紝...)m=Ubound(strings) //鑾峰彇strings涓婃爣 With Sheets("琛ㄥ悕")r=.Range("A1:F65535").end(xlup).row //鍋囪浣犵殑鏁版嵁琛ㄧ敤鐨凙鍒癋锛岀敤r鑾峰彇鏈澶ц鏁 for i=1 to r for j=1 to 6 //涓婇潰鍋囪浣犵殑鏁版嵁鍙敤鍒癋鍒 for n=0 to m If Trim(...
绛旓細鍦VBA涓紝鎮ㄥ彲浠ヤ娇鐢―ir鍑芥暟鏉ヨ幏鍙栨枃浠跺す涓嬬殑鏂囦欢鍚嶇О锛屽苟灏嗚繖浜涙枃浠跺悕鐢ㄤ簬鑾峰彇鍜屾眹鎬诲悇涓Excel鏂囦欢涓殑鏁版嵁銆備互涓嬫槸涓涓熀鏈殑姝ラ鎸囧崡锛氭墦寮涓涓柊鐨凟xcel鏂囦欢銆傛墦寮Visual Basic锛屾坊鍔犱竴涓ā鍧楀拰涓涓繃绋嬶紝绉颁箣涓衡滄眹鎬绘枃浠垛濄傚皢姹囨绘枃浠跺悕鐨勪唬鐮佸鍒跺埌涓婅堪杩囩▼涓紝鍐嶅畾涔変竴涓彉閲弚b涓哄伐浣滅翱绫诲瀷銆傛坊鍔...
