Excel VBA Range Selection Error

[scottydel]

Hello,

I'm using Excel and Access 2003. From within Access I open an Excel doc
with VBA, and am having trouble selecting ranges in the Excel doc. The
behavior is inconsistent in that sometimes my code works, and sometimes it
doesn't.

Code Snippet 1: Works every time

xlsWorksheet.Range("A1:A5").NumberFormat = "#,##0"

Code Snippet 2: Works sometimes, doesn't work sometimes

xlsWorksheet.Range("A1:A5").Select <--- this line fails
With xlsApp.Selection
.NumberFormat = "#,##0"
End With

The error message is: "Select method of Range class failed"

It seems accessing a range's properties directly works every time, but
selecting a range first then trying to access the selection's properties
fails during the "Select" method.

This error usually does not happen when I first open Access/Excel. But as I
start to develop, it starts happening after awhile.

The funny part is for some of the code I have recorded a macro to obtain the
code, and the maco always returns code similar to Snippet 2, the one that
fails.

Could it have to do with Excel process running in the background (I'm
setting break points and debugging and leave some processes running)?

Any ideas?

Your thoughts are appreciated!

-Scott

 

[Jim Thomlinson]

You can not select a range until you have first selected the sheet. If the
sheet is not active then you will get the range select error. For that reason
you are best to just avoid selecting. 99% of the time the only reason you
want to select a sheet or a range is to display it to the user.

 

[Gary Keramidas]

never done anything within access, but there is no need to select in excel, so
maybe this will work

With xlsWorksheet.Range("A1:A5")
.NumberFormat = "#,##0"
End With

 

[Les]

Activate the worksheet before attempting to select the range.

xlsWorksheet.Activate
xlsWorksheet.Range("A1:A5").Select

 

[scottydel]

Thanks for the info about selections, it's helpful. I'm going to avoid
selecting whenever possible.

FYI, I set the workseet to the workbooks's activesheet, but nowhere did I
deliberately "select" the worksheet. Any ideas why this would work
sometimes, and not work sometimes?

-Scott

 

[Jim Thomlinson]

I'd need to see the code... Chances are you are changing the activesheet
somewhere...

 

[scottydel]

Any ideas why I would get the error:

"Select method of Worksheet class failed"

When I try and select a worksheet?

 

[Jim Thomlinson]

To select a range the Worksheet must be the active sheet. To select a
Worksheet the Workbook must be the Active Workbook. If you can manage it you
are best off to try to avoid selecting anything. Post your code is you want
help getting rid of the select statements...