批量更新不同目录指定工作簿工作表单元格内容

1、新建一个Excel文件，按ALT+掂迎豢畦F11打开VBE编辑器，新建一个模块，粘贴如下代码：Option ExplicitSub 批量修改不同目录指定工作簿工作表单元格内容()Dim d As Object, f, h, l, t As String, wb As Workbook, th As Workbook, i As Integer, shname As Stringshname = "Sheet1" '此处设置需要更改的工作表名称,这里默认是sheet1Application.ScreenUpdating = FalseApplication.DisplayAlerts = FalseSet th = ThisWorkbookgh:h = InputBox("请输入类似1、2、3……65536", "请正确输入行坐标")If h = "" Then Exit SubIf h Like "*[!0-9]*" Or h > 65536 Then GoTo ghgl:l = InputBox("请输入类似A、B、C……IV", "请正确输入列坐标")If l = "" Then Exit SubIf l Like "*[!a-zA-Z]*" Or l Like "[a-zA-Z]*" = False Or _ (l Like "[a-zA-Z]?" And l Like "?[a-vA-V]" = False) Then GoTo glt = InputBox("更新内容", "请输入内容")Set d = Application.FileDialog(msoFileDialogFilePicker)With Application.FileDialog(msoFileDialogFilePicker).AllowMultiSelect = True.InitialFileName = ThisWorkbook.Path & "\".Title = "选择需要修改的Excel文件".InitialView = msoFileDialogViewDetails.Filters.Add "Excel文件", "*.xls;*.xlsx", 1.ShowIf .Show ThenFor Each f In .SelectedItems If Dir(f) <> th.Name Then '判断是否为当前工作表 Set wb = Workbooks.Open(f) For i = 1 To wb.Sheets.Count If wb.Sheets(i).Name = shname Then '判断是否存在指定的工作表 wb.Sheets(i).Cells(h, l) = t wb.Save End If Next i wb.Close Else For i = 1 To th.Sheets.Count If th.Sheets(i).Name = shname Then th.Sheets(i).Cells(h, l) = t Next i End IfNextEnd IfEnd WithSet d = NothingApplication.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd Sub

2、按ALT+F8，打开宏对话框，执行“批量修改不同目录指定工作簿工作表单元格内容”宏。

3、在弹出的“请正确输入行坐标”对话框中，假设输入2，点确定。

4、在弹出的“请正确输入列坐标”对话框中，假设输入B，点确定。

5、在弹出的“请输入内容”对话框中，假设输入：啊不错的风格hi，点确定。

6、在弹出的“选择需要修改的Excel文件”打开框中，选中需要修改的Excel文件，点确定。

7、最终的效果如下图：