Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error "5" after range().delete runs before querytable.add
#1 I'm not a programmer, just learning this stuff so please excuse the
"stupid" questions. Pleae explain the reason or fix in plain english so I can understand and learn from this. I'm pulling data from from the internet using the following WEBQUERY: as a gosub routine. It works perfectly every single time! But when I add either of the two following commands, I get the runtime error "5" error. pgname.Range("A1:R100").Delete (xlShiftUp) pgname.Range("A1:R100").Delete When I remove the code the WEBQUERY: works fine without a hitch. Please help me i) understand why this is causing a problem and ii) how can I get the specified range cleared before the WEBQUERY: routine runs. By the way, the ...... broken code excerpt ..... WEBQUERY: pgname.Range("A1:R100").Delete (xlShiftUp) '<-- executes! With pgname.QueryTables.Add(Connection:= _ '<-- errors! "URL;" & mwURL & scantype & "" _ , Destination:=rng) .Name = scantype ... .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error "5" after range().delete runs before querytable.add
Try clearing the range rather than deleting it. Selection.Clear |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error "5" after range().delete runs before querytable.add
Try clearing the range rather than deleting it. Selection.Clear |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error "5" after range().delete runs before querytable.add
ward376 wrote:
Try clearing the range rather than deleting it. Selection.Clear Ward, thanks for the suggestion it worked like a champ. Now can you help me understand why the Selection.Delete caused the error but Selection.Clear worked without a hitch? Again thanks for helping me with this simple problem! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error "5" after range().delete runs before querytable.add
I'm not positive, because your adding the query table connection with
code, but query tables are associated with automatically created named ranges and when they're deleted, the query is also. It may also have to do with your variable rng but I don't know how your setting it. Ward376 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error "5" after range().delete runs before querytable.add
ward376 wrote:
I'm not positive, because your adding the query table connection with code, but query tables are associated with automatically created named ranges and when they're deleted, the query is also. It may also have to do with your variable rng but I don't know how your setting it. Ward376 Yeah it doesn't make too much sense to me especially if the code for the qurey table connection remains COMPLETELY UNCHANGED and works PERFECTLY until the .DELETE is introduced. So I guess the question is why does .CLEAR works fine but not .DELETE?? Very odd in my opinion, really don't think it has anything to do with the variables. But here's the ranges spelled out. And the reason I'm using variables is I'm pulling data from 15 different website tables and placing them into 8 different excel worksheets. Again thanks for the help and education Ward! ......................................... Set pgname = Sheet2 Set rng = pgname.Range("A1") WEBQUERY: pgname.Range("A1:R100").Clear With pgname.QueryTables.Add(Connection:= _ "URL;" & mwURL & scantype & "" _ , Destination:=rng) ......................................... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error "1004" Select method of range class failed | Excel Discussion (Misc queries) | |||
Macro that runs entered value through "low" and "high" range | Excel Programming | |||
"Subscript out of range" error for: Workbooks("Test1.xls").Save | Excel Programming | |||
RunTime Error "1004" QueryTable is Invalid | Excel Programming | |||
"Run-time error '1004'" Method 'Range' of object '_global' failed. | Excel Programming |