"Application-Defined or Object-Defined Error" in QueryTables

[kprit]

I call the function PopData(written below) from any sheet by saying =popData(P1,P2,P3,P4). To get the user name and password from the web server(using http) I call Proc URLGet(written below). My problms are

1) When procedure URLGet is called from PopData then after executing line ".Refresh BackgroundQuery:=False(in URLGet)" the control comes back to start of the PopData and URLGet gets called up again.

2)I get run-time error "Application-defined or object-defined error" at line ActiveSheet.Range("z100&quot.Value = ""

Pls help.....Code is written below....

Function PopData(PRange1 As Range, Optional PRange2 As Range, Optional rank_algorithm As String, Optional return_single_triple As Integer) As Boolean

PopData = False

Dim gUser_Id
Dim gPWD


URLGet ("

http://mdbqa/RankAlgo/ConnectionDetail.asp")

Dim cellVal
cellVal = ActiveSheet.Range("z100&quot.Value
Dim pos
pos = InStr(1, cellVal, ",&quot

gUser_Id = Mid(cellVal, 1, pos - 1)
gPWD = Mid(cellVal, pos + 1)

ActiveSheet.Range("z100&quot.Value = ""
PopData = False
End Function




Sub URLGet(ByVal url As String)
On Error Resume Next
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & url, _
Destination:=Range("z100&quot)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[PHV]

You can try this:
in URLGet
.BackgroundQuery = False
in PopData
ActiveSheet.Range("z100&quot.ClearContents


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kprit]

Get error "ClearContects method of Range class failed".

I have just noticed a problem. As I am unable to clear the contents of cell z100 after calling URLGet. As many times I call function =popData(,,,,) the Destination:=Range("z100&quot of URLGet copies contents into Z100 then in AA100 then in AB100 then AC100....means every time it is copieng data in next column even I have specifically specified Destination:=Range("z100&quot

 

[MakeItSo]

Another problem are these lines:

cellVal = ActiveSheet.Range("z100&quot.Value
If z100 is empty, Value is NULL, not "".
In any case, the code will error at this line:
--> gUser_Id = Mid(cellVal, 1, pos - 1)

Because pos=0, you try to read a "-1 characters" of the string.

You can get around it by using this instead:
[blue]
If pos<>0 then
gUser_Id = Mid(cellVal, 1, pos - 1)
gPWD = Mid(cellVal, pos + 1)
Else
gUser_Id = &quot;&quot;
gPWD = &quot;&quot;
End If
[/blue]

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[kprit]

Thats true MAKEITSO. I will do that....Thanks a lot

But pls look into other problem that is very serious for me. I have to deliver the work by end of the day only 4 hrs are left for me...and I am stuck here.

 

[Lagoon1]

Hi
I've just faced a problem trying to extract data from an OLAP database - Cognos Cubes via Excel's Web query.

I've tried using vba to extract the data as well but with no success.

Any ideas?

Thanks very much.

Regards,

Lagoon1

 

[MakeItSo]

kprit:
Could the URLGet problem be caused by the destination:
Destination:=Range(&quot;z100&quot ?

At which cell do you have your &quot;=PopData(...&quot;
at z100 perhaps?
This could cause an extra call of that function.
Perhaps if you use another cell as destination first and then do a pastespecial (values only), or store your values in variables then assign them to the cell?

Just a shot in the blue.

BTW: nifty function you have set up here! ;-)

 

[kprit]

wrong shot....you caught out...