Get range from closed workbook


Dec 13, 2010
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?

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I see this possible solution, but I'd rather not use formulas. I just want a plain text list.

Any suggestions?
Upvote 0
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"?
Last edited:
Upvote 0
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...
Upvote 0
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

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?
Upvote 0
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.
Upvote 0
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.
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back