| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Range Selection Error
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
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. -- HTH... Jim Thomlinson "scottydel" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
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 -- Gary "scottydel" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
Activate the worksheet before attempting to select the range.
xlsWorksheet.Activate xlsWorksheet.Range("A1:A5").Select -- Les Torchia-Wells "scottydel" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
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" wrote: 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. -- HTH... Jim Thomlinson "scottydel" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
I'd need to see the code... Chances are you are changing the activesheet
somewhere... -- HTH... Jim Thomlinson "scottydel" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "scottydel" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
Any ideas why I would get the error:
"Select method of Worksheet class failed" When I try and select a worksheet? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "scottydel" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Range Selection Error
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... -- HTH... Jim Thomlinson "scottydel" wrote: Any ideas why I would get the error: "Select method of Worksheet class failed" When I try and select a worksheet? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "scottydel" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Repeatable crash in Custom Error Bar range selection | Excel Discussion (Misc queries) | |||
| Range(Selection, Selection.End(xlToRight)).Select | Excel Programming | |||
| Excel VBA help - Range selection | Excel Programming | |||
| Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming | |||
| Strange or error : For each cl in Selection 'cl as range | Excel Programming | |||
Linear Mode
