QueryTable ... destination memory

[moroandrea]

Hi All,

I've come across the object QueryTable as the unique alternative to have a macro to pull data from Internet working on both Mac and Windows.

As I need to further re-elaborate the information extracted, I was wondering if there is a way to specify the clipboard as the destination range rather than a cell on a sheet.

Alternatively I was thinking to create a new sheet on the go, however I don't have a clue whether or not this is feasible and efficient.

Any idea and suggestion?

Thanks
Andrea

[Jan Karel Pieterse]

Why would you want to put the data into the clipboard?
I would do as you already suggest: have a (possibly hidden) worksheet just to pull in the data. Then draw the data from that sheet.

[moroandrea]

Hi There

I am intereested in the clipboard because I need to further elaborate the information, which in my case is an XML piece of code that need to be parsed to extract the bit I need.

Ultimately I don't need to preserve the link with the data source, as query table is supposed to do.

I assume that there is not a way, otherwise someone would have already disclosed an example.

So, can you give me some hints on how to create a temporary spreadsheet and store information there?

Thanks.

[pjotter]

You could've just searched for: "vba excel to clipboard" in google or something and get to the following site:
http://www.cpearson.com/excel/Clipboard.aspx

There is described how to use the clipboard or how to set data to it.

If by reading that, you still cannot figure out how to do it. Get back and I'll try and help you solve your problem.

For this to work, you might have to first put data to cells and from thereon put it to clipboard, but I haven't really looked into it, hence I'm not really sure.

[Jan Karel Pieterse]

You don't need querytables then. You could just use the Microsoft XML library or even the Internet explorer object and have that pull in the xml from the web source directly.
Dick Kusleika showed how to get data from tables on a website here:
http://www.dailydoseofexcel.com/archives/2006/11/29/html-tables/

[moroandrea]

Hi pjotter,


You could've just searched for: "vba excel to clipboard" in google or something and get to the following site:


regrettably my clipboard mention was connected on how to use it as destination of the querytable object rather than copy something generic to it.
Query table object has a parameter that is mandatory and do require the destination, which so far can be just a range of cell.

Do you have any idea on this?

[moroandrea]

You don't need querytables then. You could just use the Microsoft XML library or even the Internet explorer object

Unfortunately I have had to rely on the querytable object because it is the only one it is working for both mac and windows.

No intenernet explorer or httpXML library on mac.

[Jan Karel Pieterse]

If inserted as a querytable, how does the data "come in"? Can you perhaps post a sample workbook with the inserted QT?

[moroandrea]

It seems my answer never come in yesterday.

This is an example

sURL = "http : / / api. bing. net/json.aspx?Version=2.2&Query=Test&Sources=web&Market=en-GB&JsonType=function&appId=FF0C946E32D6EC9DC28A78E5E89819CD7B8C7436"


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & sURL, Destination:=Range(A1))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

As you can see the querytable declaration has a destination parameter in which you have to specify the cell(s) where your results need to be delivered.

So, again, do you know a way to copy that value straight to the clipboard?

[Jan Karel Pieterse]

If I run your code (after removing spaces from the url and putting "" around the cell address), it returns one cell with a very long string of text. You can easily pull that string from the cell:

sTxt=Range("A1").Value

Then you can work your way through the string to extract the part you need.

Note that once the querytable is defined (after first time the add method has been performed) you do not need to re-add the query, you can simply use the refresh method of the querytable:

Range("A1").Querytable.Refresh False

[moroandrea]

If I run your code (after removing spaces from the url and putting "" around the cell address), it returns one cell with a very long string of text....

Hi Jan

thanks for your answer. I'm aware about the behaviour and the long string returned, and consequently how to manipulate it through regEx and stuff like that.

My point was to avoid the content initially retrieved, the long string, to be pulled out into the cell. I was after having the content copied into memory straight-away.

I think that I will go for the original solution, create an hidden sheet and use it as a repository.

I've never done this before, but I assume some example should be around.

[Jan Karel Pieterse]

If the URL never changes, just manually add the web query to a sheet, then right-click the sheet's tab and select Hide.
You can then refresh the querytable like so:

Worksheets("HiddenSheetName").Range("A1").QueryTable.Refresh False

And then fetch the string like so:

sTxt=Worksheets("HiddenSheetName").Range("A1").Value