自定义功能区选项卡下的宏按钮尝试打开旧的Excel文件 [英] Macro button under customized ribbon tab tries to open old Excel file

问题描述

我像Excel_app_v1.xlsm一样在Excel上创建了一个自定义功能区选项卡，该功能区选项卡下的按钮连接到宏.因此，当我单击此按钮时，宏会执行一些表导入应用程序.

I created a custom ribbon tab on my Excel like Excel_app_v1.xlsm, and a button under this ribbon tab is connected to a macro. So when I click this button, the macro does some table importing applications.

第一个奇怪的是，我仅为此Excel文件创建了此功能区选项卡和按钮，但是即使未打开原始Excel文件Excel_app_v1.xlsm，功能区选项卡和按钮也出现在所有其他Excel文件中.

The first strange thing is that I created this ribbon tab and the button for only this Excel file, but the ribbon tab and the button appear in all other Excel files, even if the original Excel file Excel_app_v1.xlsm is not open.

第二个问题是我使用另存为"选项创建了以前的Excel文件的第二个版本.因此，新的Excel文件类似于Excel_app_v2.xlsm.当我单击功能区选项卡下的按钮时，即使未打开，它也会打开第一个Excel文件Excel_app_v1.xlsm.我删除了第一个Excel文件，但随后出现类似在路径上找不到Excel_app_v1.xlsm"的错误.

The second problem is that I created a second version of my previous Excel file with "Save-as" option. So the new Excel file is like Excel_app_v2.xlsm. When I click the button under the ribbon tab, it opens the first Excel file Excel_app_v1.xlsm, even if it is not open. I deleted the first Excel file, but then I got an error like "Couldn't find the Excel_app_v1.xlsm on the path".

因此，显然，自定义功能区选项卡下的宏按钮已链接到第一个Excel文件，但是我找不到菜单选项来更改此设置.我在vba代码中的所有sheet表达式之前添加了ThisWorkbook，但是并不能解决问题.单击按钮仍在尝试打开旧的excel文件.

So obviously the macro button under the customized ribbon tab is linked to the first Excel file, but I couldn't find the menu option to change this. I added ThisWorkbook before all the sheet expressions in the vba code, but it didn't solve the problem. The button-click is still trying to open the old excel file.

VBA代码在下面.该按钮链接到Sub ImportTable.首先，它询问用户是否要继续该过程.单击按钮后，它会立即打开上一个Excel文件，同时显示消息框".

The VBA code is below. The button is linked to the Sub ImportTable. Firstly it asks the user if the user wants to continue with the process. It opens the previous Excel file right after clicking on the button, at the same time as the Message Box appears.

Sub ImportTable()

  Application.ScreenUpdating = False
  YearMonth = ThisWorkbook.Sheets("tab1").Cells(11, 2).Value
  ' The Macro button opens the previous Excel file before clicking Yes or No on the message box
  answer = MsgBox("Warning! Brings the newest source file. You want to continue?", vbYesNo + vbQuestion, "")

  If answer = vbYes Then

     RunSASCodeViaBatFile  ' Another Sub which runs bat file to run a SAS-code. But it doesn't matter. Because the problem happens before I click on Yes or No.  
     InsertSASFileIntoExcel

   Else  ' Nothing happens if clicking No on the Message Box
   End If

End Sub

推荐答案

roncruiser在下面稍作改动就发布了快速解决此问题的线索.

The clue to fixing this quickly was posted below by roncruiser, with one slight twist.

网络上的每个人似乎都认为PERSONAL.XLSB是这里的关键—没有.实际上，播放该文件只会使我困惑的时间更长.这是我所做的:

Everyone on the web seems to feel that PERSONAL.XLSB is the key here — nope. In fact, playing with that file only confounded me for even longer. Here's what I did instead:

1. 右键单击功能区，然后选择自定义功能区"；
2. 导航到使用按钮安装的有害宏；
3. 找到并单击导入/导出"；
4. 导出您的自定义按钮(这些宏就可以正常运行)；

5. 打开该生成的文件，并删除对另一个文件的令人讨厌的引用，这会引起您极大的痛苦—例如:

1. Right click the Ribbon and select Customize The Ribbon;
2. Navigate to the offending macros that you've installed with buttons;
3. Find and click on Import/Export;
4. Export your custom buttons (the macros will go right along just fine);

5. Open that resulting file, and edit out the offending references to the other file that's causing you so much grief — example:

<mso:button idQ="x1:HideRows_0_EA10D6" label="HideRows" imageMso="_3DPerspectiveDecrease" onAction="!HideRows" visible="true"/>

我删除了idQ-"x1...之后的所有内容，直到宏的实际名称为止.我还拿出了与onAction="...中的所有外部参考相同的外部参考.

I took out everything after idQ-"x1... up to the actual name of the macro. I also took out the same external reference found in onAction="... Take everything up to the bang mark.

以您想要的名称保存此名称，但使用相同的扩展名(在我的设置中，其名为ExportedCustomizations.exportedUI(是，扩展名很长))；

Save this under whatever name you wish, but with the same extension (for my setup, it was called ExportedCustomizations.exportedUI (yes, that long an extension));

在这里重复前几步，但是这次导入您的编辑文件.

Repeat the first few steps here, but this time import your edited file.

Voila，一切都很黄金.

Voila, all is golden.

不要把所有现有的工作都抹掉并重新开始.为我创造了魅力，因此给roncruiser一个大提示，以寻求线索.

No messing around with wiping out existing work and starting all over. Worked a charm for me, so a big tip o' The Hat to roncruiser for the clue.

这篇关于自定义功能区选项卡下的宏按钮尝试打开旧的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！