Excel VBA Range Selection Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
Activate the worksheet before attempting to select the range.

xlsWorksheet.Activate
xlsWorksheet.Range("A1:A5").Select
 
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
 
Any ideas why I would get the error:

"Select method of Worksheet class failed"

When I try and select a worksheet?
 
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...
 
Back
Top