Get range from closed workbook

[wut]

Private Function Update(path, file, sheet, value)
Dim theArgument As String
    theArgument = "'" & path & "[" & file & "]" & sheet & _
                "'!" & Range(value).Range("A1").Address(, , xlR1C1)
    Update = ExecuteExcel4Macro(theArgument)
End Function

Sub Updater()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
    thePath = "x:\thepath\"<o:p></o:p>
    theFile = "Inventory Updater.xls"<o:p></o:p>
    theSheet = "Media"<o:p></o:p>
    theValue = "O2"<o:p></o:p>
 
ThisWorkbook.Sheets("Media").Range("a1").value = Update(thePath, theFile, theSheet, theValue)<o:p></o:p>
<o:p> </o:p>
End Sub

I've been using this code to get values from a closed workbook. The only problem is that I'm only able to get values from one cell at a time. If I try to get values from hundreds of cells, there's a lot of latency.

I'd like to know how to get some code like this to work with a range instead of targeting a single cell. Anyone know where I should go from here?

 

[wut]

I see this possible solution, but I'd rather not use formulas. I just want a plain text list.

Any suggestions?

 

[Fazza]

Maybe using ADO.

How much data do you want? Is it in a table, or ?? Excel version?

Google will find plenty of examples of working with tables - like a database. One of my old favourites is http://www.xtremevbtalk.com/showthread.php?t=217783

HTH

 

[wut]

It's in a query table that's about seven hundred rows high, and two columns wide.

I've never worked with ADO before. Is there a way you can see to get that code working above, so that I don't have to start all over again from scratch? Is there any way to just assign a range in place of "theValue"?

 

[Fazza]

If you want each cell value, the method you're using must be called for each cell.

If you wanted, say, the sum of them, that could be in one call.

Why not just use a query table again? ADO is simple enough, btw...

 

[wut]

I tried using a query table again and it either returns a table with no contents or else it returns a table with weirdly varying contents that don't mirror the actual source.

Maybe I'll try ADO sometime. Can't where I'm at now.



But there's something else on my mind.

For Each query in activesheet.querytables
query.delete
next

That doesn't actually delete the tables. I can tell because they're still visible if you zoom out far enough, and what's more, when I try adding a new table with the same name as the old one, it's appended with a "_#".

Can you explain how to get rid of query tables from an active sheet?

 

[Fazza]

Try http://www.dicks-clicks.com/excel/ExternalData4.htm

 

[wut]

Am I missing something? Doesn't that page tell me to use code that will have exactly the same impact as the code I already posted?

The weird thing is that usually when I run the code I posted it deletes all the data, but not always. What never happens is what I want; for the query table itself to go away.

Any other hints?

Edit: One thing I've never tried is actually declaring the object "query" as a "QueryTable". Let me give that a shot, I'll get back on it.

 

[wut]

Didn't help. Deleting the query table leaves the table; it's visible when the user zooms out to 10%.

Edit; And more importantly, when a new query table is created using the same name, a "_#" is always added to the name.

I have to delete the whole sheet to get the query table to go away. Then, when I try to create a new sheet with command buttons on it, I encounter another problem altogether.

So I'd like to know how to just get rid of a query table. I guess I'll just create another thread some other day.