#1
|
|||
|
|||
Subscript out of range error
Hi All,
I am new to this forum. Hope u guys can help me wif my problem. I developed a small tool for my company using EXCEL 2003 - VBA. It works well on some machine but not on others. For few machine, they would get the above error. I have a master sheet on the excel where user click 'Refresh ALL' button, all data on all the sheets will be updated with current data and each sheet has a button to refresh its data too. The button on each sheet works very well. But for the 'Refresh All' button, user will encounter the error. Below is the part i suspected is giving the error: Sheet2.Range("A3").CopyFromRecordset rs Hope u guys out there can help me. Thks in advance. |
#2
|
|||
|
|||
Subscript out of range error
This error normally is a result of refering to an object that does not exist or you cannot access. In your case you may find that the users log on credentials don't allow you to access the recordset (datasource). Try each machine to see if you can access the data manually first to rule out a network security issue.
|
#3
|
|||
|
|||
Hi. Thks for ur reply
User can actually retrieve data from each individual 'refresh' button for each sheet. But when user tries to retrieve data for all sheets (18 sheets) from the master sheet, user will encounter the problem..... Sheet1 (Master sheet) -> 'Refresh All' button = retrieve data for all sheets (error!) Sheet2 to Sheet18 -> each have a 'Refresh' button = retrieve data for each individual sheet (No error) Below is a sample code for 1 of the refresh button. This sub will be tied up to a button on a sheet : Sub RefreshKPI101() Dim rs As Object Dim sProc As String Set rs = CreateObject("ADODB.Recordset") sProc = "EXEC BUD_AF_GM '" + sBranch + "','" + sPeriod + "'" rs.Open sProc, conn Sheet10.Unprotect sPass Sheet10.Range("A3").CopyFromRecordset rs Sheet10.Protect sPass End Sub My doubt is why this code works for the button if i call from Sheet10.... It does not work if i call this sub from Sheet1....... One more funny issue is the above work for most PC in my compny but the error happens on a few......... Pls help me. Thks. |
#4
|
|||
|
|||
Subscript out of range error
Try selecting each sheet first before you get (refresh/requery) the data. Technically you should not need to do this however I have found this to work for me in past.
|
#5
|
|||
|
|||
Hi caholmes,
I tried the soultion u suggested but the problem still arise...... I have a doubt..... Is this issue related to some settings? Bcos it works on most machine...... Only few not working... regards |
#6
|
|||
|
|||
Subscript out of range error
Are they are the same version of Excel?
|
#7
|
|||
|
|||
This error normally is a result of refering to an object that does not exist or you cannot access. In your case you may find that the users log on credentials don't allow you to access the recordset (datasource). Try each machine to see if you can access the data manually first to rule out a network security issue.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Range names and length stuff. Please help-a-noob. | contiw | Excel | 2 | 09-06-2007 07:59 AM |
Mysterious "Subscript out of range" error | rnstewart | Excel | 4 | 12-29-2005 01:04 PM |