CODEWITHSUNDEEP
excelvba
Aspiring Developer
Aspiring Developer

Reputation: 680

File Directory Issues with Subscript out of Range Error

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.

The VBA debugger highlights this line 

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

Remember, this is the file that is already open

So I changed that line of code to

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.

This is set later to Set wb2 = Workbooks("Payroll")

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.

Upvotes: 0

Views: 780

Answers (1)

user11121185
user11121185

Reputation:

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.

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")

Upvotes: 5

Related Questions