Web Query

[Lagoon1]

Hi
Can someone please help out.

I am trying to create a web query and I've been going through these few steps.

Please kindly advise where I've gone wrong.


1 Save a txt file in notepad with the .iqy extension
2 In notepad, I've the following codes.

Comments Codes
Web
1
Servers/File

http://www...........................................,exe

Parameters AB=.............................................................

3 Tried getting external data via Excel, but error msg prompt - "An unsuspected error has occured"

Thanks.

Regards,

Lagoon1

 

[Lagoon1]

Hi
Is there anyone who might be able to help out?

Thanks.

Rgds,

Lagoon1

 

[xlbo]

If you are happy to use VBA - this should get you going - don't need to save dqy or iqy files either this way:

Sub Read_URL_Test()
With ActiveSheet.QueryTables.Add(Connection:="URL;[URL unfurl="true"]http://This/Is/a/test/",[/URL] Destination:=Range("a1"))
     'write web-page to sheet
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Lagoon1]

Hi Geoff
The data that I am trying to get off the web is from an OLAP source - Cognos Cubes. I'm not sure about the compatability or are there any sort of implications that I need to consider or if this can be just a straightforward query.

Having entered the url address into the vba code, do I need to enter the parameters into a separate line?

As I've tried putting the whole url address (including parameters) into the vba code and it comes up with error 1004 - Cannot locate Internet server or proxy server.

Please kindly advise.

Thanks very much.

Regards,

lagoon1.

 

[Lagoon1]

Hello
Can someone please kindly assist whereever possible.

Thanks.

Rgds,

lagoon1

 

[xlbo]

To be honest, I don't know - have never tried querying cognos cubes - never mind through the web.

Try recording yourself creating a web query:

Data>Import External Data> New Web Query

and see what it gives you....

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[xlbo]

This is what I got when recording going to TekTips - looks like parameters ARE passed through the URL argument - looks liek you need to prefix them with a ?

"With ActiveSheet.QueryTables.Add(Connection:= _
"URL;

http://www.tek-tips.com/userthreadparticipate.cfm?handle=xlbo",

_
Destination:=Range("A1&quot)"

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Lagoon1]

Hi Xlbo
Thanks for responding.
I've tried your suggestion and it seems to work pretty well with the exception that there are a few things which I wouldnt want to show up on my extract in excel.

Example

On the web site, there seems to be those dropdown arrows and when I extract the data onto excel, it seems that the arrow changes to strings "Sort Ascending" or "Sort Descending".

1. Is it possible to extract only the tables within the web?

2. Or, is it possible to choose the chunks of data I would like to extract from the web page?

Thanks.

Rgds,

Lagoon1

 

[PHV]

From my VBA Excel help file:

Set shFirstQtr = Workbooks(1).Worksheets(1) 
Set qtQtrResults = shFirstQtr.QueryTables _
  .Add(Connection := "URL;[URL unfurl="true"]http://datasvr/98q1/19980331.htm",[/URL] _
    Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
  .WebFormatting = xlNone[COLOR=blue]
  .WebSelectionType = xlSpecifiedTables
  .WebTables = "1,2"[/color]
  .Refresh
End With

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

 

[Lagoon1]

Hi
Thanks for all the help.

1. Is it possible to exclude all the hyperlinks and also when the data is copied into excel, it seems those drop-down arrows turned into text with hyperlinks (i.e. sort descending or ascending).

2. Does anyone know of the method to extract the data via the url address from Cognos Cubes? I've tried successfully, but it seems that the url address is not static and keeps changing dynamically. Is there any way of overcoming this problem?

Any help will be greatly appreciated.

Thanks.

Rgds,
Lagoon1.