querytable refresh method not working

[sin_rodeos]

Hi,

I have created an excel application in Excel 2003 for Windows that uses
a querytable to access a webservice and return the data the application
needs. I have tried running the application in one Mac machine under OS
9 and Excel 2001 and OS X and Excel 2004 and it gives me an error with
the Refresh method in both cases. In another Mac machine with OS 9 and
Excel 2001 it runs beautifully. Does anyone know why can this be? Is
there any configuration in Excel and/or the operating system that may
be stopping the refresh method from accessing the internet???

Please help!!!!

Thanks.

 

[Jim Gordon MVP]

Hi,

I think it should work the same in Excel 2001 and Excel 2004. I'm
puzzled as to why you are having a problem. Can you give the exact error
message?

Does the query already exist in the application you are distribting or
are you making a new query programatically?

-Jim

 

[sin_rodeos]

HI Jim,

I will try to send you the exact error on Monday, as the application is
in the office. The error is with the refresh method of the querytable.
What it is most surprising is that in one machine it works under excel
2001, but under the other one it doesn't matter whether it is excel
2001 or 2004, it doesn't work. The machine where it doesn't work has
more recent version of OS 9 and as well OS X. In this machine we ran
excel 2001 under OS 9 and Excel 2004 under OS X. I had the problem in
both of them. That is why I was wondering if more recent versions of
the system have a problem with the refresh method.

The query table is done programatically. I have a VBA code that creates
a new querytable by calling a web service that I have created in C# and
pass the variables through the post property of the querytable. The web
service then returns the data in a string to the querytable. After that
I parse the returned text in excel to set it up how the application
needs it.

Sorry I can't remember the message from memory. I will send you more
info on Monday. Thanks for the help.

 

[Jim Gordon MVP]

Hi,

There is a known problem with web queries in Excel v.X, but 2001 and
2004 should be OK.

If you want to send a copy of the workbook to me that's OK with me and
I'll try it if you want. You should be able to figure out my email
address if you substitute "hot" for warmer than warm.

-Jim

 

[sin_rodeos]

Hi,

the error message I get is the following:

Run-time error '1004':
Method 'Refresh' of object '_QueryTable' failed

The appropriate part of the code is the following:

Dim qry As QueryTable
Set qry =
xlResult.QueryTables.Add(Connection:="URL;http://cge.beaconhill.org/NYC/NYC.asmx/RUN",
Destination:=xlResult.Range("A1"))
With qry
.PostText = "Y06=" & xlCode.Range("FY06Change") & "&Y07=" &
xlCode.Range("FY07Change") _
& "&Y08=" & xlCode.Range("FY08Change") & "&Y09=" &
xlCode.Range("FY09Change") & "&Y10=" & xlCode.Range("FY10Change") _
& "&CHOICE=0"
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh (False)
.SavePassword = False
.SaveData = True
End With
qry.Delete

Again, in an old machine it works, but in machines with newer versions
of the operating system it doesn't. Am I missing something????

Thanks.

 

[Jim Gordon MVP]

Hi,

I fiddled a bit and got the code to get past the add table part, but
still no joy.

Here's an article with some syntax concerning the POST method.
http://support.microsoft.com/kb/157482/en-us

It looks to me like there is nothing wrong with your code. I alerted
the Excel people to take a look at this. Meanwhile, look over that
article and try some alternate syntax and see if you can get it to work.

-Jim

 

[sin_rodeos]

Thanks Jim, I will. Reading other posts that had the same message, and
that I saw you participated in, some guys were saying that maybe it had
to do with the length of the post text, and another guy was saying that
maybe it was the size of the data... I really don't know what it is.
And it only happens on machines that have OS X. I thought it could be a
thing with the firewall it brings, but no, with the firewall off it
still has this problem.

One other thing I was wondering is if there was any other way of
accessing a web service that it wasn't a query table in Excel.

Again thanks for the help.

 

[Jim Gordon MVP]

Hi,

The only way I know of to access a web service within Excel is using a
web query. The URL you are using is not terribly long, so I doubt that
is the problem.

My hunch is that Excel in OSX does not properly deal with xlResult and
XLCode.Range.

When I changed xl.Result.QueryTables.Add to ActiveSheet.QueryTables.Add
I was able to get to the next line.

I think if you change the syntax for the POST text it might work, but I
don't know what syntax to suggest.

-Jim

 

[sin_rodeos]

Have you tried the name of a range in brackets??? I think that already
specifies a range without having to specify the page as the range is
already defined for a page. However, what is strange is that you were
getting stuck in the Add stage. When I tested it and got the error and
clicked on debug the vb editor was selecting the refresh line. I will
try with the [ ] format to see if that works in mac world. I'll keep
you posted.

Alfonso.