时间戳

首页 > 解决方案 > 在所有打开的 Excel 文件中运行宏

excel - 在所有打开的 Excel 文件中运行宏

问题描述

刚接触 VBA 代码并努力弄清楚为什么我的代码不起作用。我想在所有打开的 Excel 文件中运行一个宏。一次处理一个文件的宏是：

```
Sub DeleteRow()
Dim R As Range, cellsToDel As String
'looking for cells that contain the word '1. Staff Home'
Set R = ActiveSheet.Range("A:A").Find("1. Staff Home", LookIn:=xlValues)
'if found
Do While Not R Is Nothing
'get cells address to be deleted
cellsToDel = "A1" & ":K" & R.Row - 1
'delete the cells
ActiveSheet.Range(cellsToDel).Delete xlShiftUp
'looking for cells that contain the word '1. Staff Home' again
Set R = ActiveSheet.Range("A:K").Find("1. Staff Home", LookIn:=xlValues)
Loop
End Sub

Sub Unwrap_Text()
'unwrap text in all cells'
ActiveSheet.Range("A:K").WrapText = False
End Sub

但是当我尝试将它包装在一个宏中以在所有打开的工作簿上运行时它不起作用：

```
Sub DoAll()
Dim wb As Workbook
For Each wb In Application.Workbooks
Dim R As Range, cellsToDel As String
'looking for cells that contain the word '1. Staff Home'
Set R = ActiveSheet.Range("A:A").Find("1. Staff Home", LookIn:=xlValues)
'if found
Do While Not R Is Nothing
'get cells address to be deleted
cellsToDel = "A1" & ":K" & R.Row - 1
'delete the cells
ActiveSheet.Range(cellsToDel).Delete xlShiftUp
'looking for cells that contain the word '1. Staff Home' again
Set R = ActiveSheet.Range("A:K").Find("1. Staff Home", LookIn:=xlValues)
Loop
Next wb
End Sub
```

我已经看到可以在目录中的 Excel 文件上运行的宏示例，但是我目录中的文件是我在 Excel 中打开的 .html 文件，并且想要删除一堆与我无关的导航链接行需要。

标签： excelvba

解决方案

如果只有一张纸：

Sub DoAll()
    
    Dim wb As Workbook, ws As Worksheet
    Dim R As Range, cellsToDel As String
    
    For Each wb In Application.Workbooks
        Set ws = wb.Sheets(1)
        Set R = ws.Range("A:A").Find("1. Staff Home", LookIn:=xlValues)
        Do While Not R Is Nothing
            ws.Range("A1:K" & (R.Row - 1)).Delete xlShiftUp
            Set R = ws.Range("A:K").Find("1. Staff Home", LookIn:=xlValues) 'A:A ?
        Loop
    Next wb
    
End Sub

请注意，您发布的Find()两条线路正在查看不同的范围。这是故意的吗？

推荐阅读