excel - 自定义功能区按钮打开宏文件但随后切换焦点

Question

我编写了一个解析 URL 列表的 excel 宏，将工作表中的信息保存到每个 URL。

我在功能区上创建了一个按钮来运行宏。

宏存储在它自己的文件中，因为带有 URL 的文件每次都是唯一生成的。

当我点击按钮运行宏时，文件没有加载。Excel 将加载文件并打开一个窗口，其中显示带有空工作表的宏文件。

然后它将在新打开的宏文件上运行宏，而不是在我启动宏的 URL 文件上运行。

事实上，我猜在它切换焦点之前，URL 文件中的第一行中的一两行将由宏处理。

这可以修复吗，所以即使没有先加载宏文件，我也可以以正确的方式从按钮运行宏？或者，第二个选项，如果宏文件尚未打开，则打开它，然后停止，不执行任何行？

谢谢，托马斯

标签： excelribbon

Answer 1

@Luuklag

我找到了使用 Workbook Activate 的方法。代码如下所示，包括“修复”。但这并不是一种好的或稳健的方式。

Sub cmdParsePartsList()
Dim iRow As Long
Dim Count As Long
Dim pctCmpl As Integer
Dim TotalCnt As Long

Dim Wb As Workbook
Set Wb = ActiveWorkbook
Wb.Activate    'To move focus back to the TIKA list if pressing the button to start the macro
               'automatically opened the macro file and switched focus to the macro file.
               ' This method does not feel robust. But it is what I came up with.

iRow = 2  'Start at second row, as first row is Heading.
Count = 0 'Counting number of parts written back
pctCmpl = 1

TotalCnt = Application.WorksheetFunction.Subtotal(3, Range("C:C"))

ProgressForm.LabelProgress.Width = 0
ProgressForm.Show

Do Until IsEmpty(Cells(iRow, 3))
    If Not Cells(iRow, 3).Rows.Hidden Then
        Application.StatusBar = iRow
        Call cmdSaveToTika(HLink(Cells(iRow, 3)), Cells(iRow, 5).Text)
        Count = Count + 1

        pctCmpl = ((100 * Count) / TotalCnt)
        With ProgressForm
            .LabelCaption.Caption = "Processing Row " & iRow & " - " & pctCmpl & "%"
            .LabelProgress.Width = (Count / TotalCnt) * (.FrameProgress.Width)
        End With

        DoEvents

    End If
    iRow = iRow + 1
    Wb.Activate         'To move focus back to the TIKA list if pressing the button to start the macro,
                        'automatically opened the macro file and switched focus to the macro file.
                        ' This method does not feel robust. But it is what I came up with.
Loop

Application.StatusBar = "Done with " & Count & " comments saved back to TIKA! :)"
Unload ProgressForm

End Sub