Subscript out of range error

[vcsphx]

Hello, I have been trying to fix this error but I have no clue why it's not
working. Any help would be greatly appreciated! I am getting "Subscript out
of range error" when I execute the coding given below, only when I HAVE AN
EXCEL INSTANCE OPEN. If I close all the excel instance and run the code
again, it works just fine. Here is the coding:

------------------------------
Dim xlsApp As Object
'I included this ExcelIsOpen function to see if an excel instance is already
open
'and if so, use that...but that doesn't seem to fix the issue

If ExcelIsOpen Then
Set xlsApp = GetObject(, "Excel.Application")
xlsApp.Visible = True
Else
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True
End If

xlsApp.Workbooks.Open Filename:="C:\USERNAME\Import_UnmappedMoves.xls",
Password:="password", WriteResPassword:="password"

'am getting the error when the next line executes

Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate

 

[OssieMac]

Try activating the workbook first then activating the worksheet.

Workbooks("Import_unmappedmoves.xls").activate
worksheets("Moves").Activate

 

[Tausif]

Hi,
The error is occuring because you have .xls in the line below
Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate

Just remove the .xls
Workbooks("Import_unmappedmoves").worksheets("Moves").Activate
That shld work.

HTH,

 

[vcsphx]

Thanks for the response! But I have tried both of the suggested solutions
(activating the Workbook first before trying to activate the worksheet and
having the workbook name without .xls) with no luck. Do you know if there is
anything else that might be causing error?

Thanks a lot for your time and help! I really appreciate it!

 

[Tausif]

Hi Again,

I think this should def work.

The line on which you get error is
Workbooks("Import_unmappedmoves").worksheets("Moves").Activate
bcoz I think you are not referencing the xlsApp variable.
You need to prefix xlsApp.

Try
xlsApp.Workbooks("Import_unmappedmoves").worksheets("Moves").Activate

OR
xlsApp.Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate

HTH,

 

[vcsphx]

WOW!!! Thank you very much!! That did it...

Hi Again,

I think this should def work.

The line on which you get error is
Workbooks("Import_unmappedmoves").worksheets("Moves").Activate
bcoz I think you are not referencing the xlsApp variable.
You need to prefix xlsApp.

Try
xlsApp.Workbooks("Import_unmappedmoves").worksheets("Moves").Activate

OR
xlsApp.Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate

HTH,