下标超出范围错误的文件目录问题 [英] File Directory Issues with Subscript out of Range Error

问题描述

我在一个名为"Payroll_Cleanup"的工作簿中编写了一个宏，以清理/格式化一个名为"Payroll"的文件.

I wrote a macro in a workbook called "Payroll_Cleanup" to clean up/format a a file called "Payroll".

我将宏文件和要清除的文件放在同一文件夹中.

I have the macro file and the files-to-be-cleaned in the same folder.

该宏在 我的计算机 上正常工作.

The macro works fine on my computer.

但是，当将我拥有的相同文件夹放置在另一个用户桌面上以便他们可以运行文件时，他们会收到下标超出范围" 错误.

However, when putting the same folder I had on another users desktop so they can run the file, they receive the "Subscript out of Range" error.

VBA调试器突出显示此行

The VBA debugger highlights this line

Set wb1 = Workbooks("Payroll_Cleanup") 'out of range error

请记住，这是 已打开

Remember, this is the file that is already open

所以我将代码行更改为

Set wb1 = ActiveWorkbook

太好了，所以现在继续进行.

Great, so it now proceeds on.

现在，它给我另一个下标超出范围" 的错误:

Now it gives me another "Subscript out of Range" error for:

  Workbooks.Open ("Payroll.xlsb") 'file to be cleaned that is in the same folder.  

稍后将其设置为 Set wb2 = Workbooks("Payroll")

由于我不能只做另一个"ActiveWorkbook" 行，我还能做什么?我想念什么?我检查了信任中心的宏设置，它们完全匹配.

Since I can't just do another "ActiveWorkbook" line, what else can I do? What am I missing? I checked the trust center macro settings and they match exactly.

推荐答案

文件资源管理器中有一个设置(查看，显示/隐藏，显示文件扩展名)可以(不管是否相信)显示或隐藏文件显示文件名在列表中的扩展名.

There is a setting in the file Explorer (View, Show/Hide, Show File Extensions) to (believe it or not) show or hide the display of file extensions with the file names in a list.

启用此选项(显示扩展名)时，它具有强制VBA的打开的Workbooks集合包含文件扩展名和工作簿名称的不利作用.

When this is turned on (show extensions), it has the unfortunate side effect of forcing VBA's open Workbooks collection to include the file extension with the workbook name.

打开文件资源管理器的显示文件扩展名"后，

When File Explorer's Show file extensions is turned on,

'this will show Subscript out of Range error.
Set wb1 = Workbooks("Payroll_Cleanup")

'this will not
Set wb1 = Workbooks("Payroll_Cleanup.xlsb")

似乎另一位用户的计算机"已启用该选项.

It would seem that 'another user's computer' has that option turned on.

您还可以通过合并后面的两个代码行来节省自己的痛苦.

You could also save yourself some grief by compining the two subsequent code lines.

set wb2 =  Workbooks.Open ("Payroll.xlsb")   '<~~ needs full path
'maybe better as,
set wb2 =  Workbooks.Open (wb1.path & "\" & "Payroll.xlsb")

这篇关于下标超出范围错误的文件目录问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！