Get range from closed workbook

wut

Banned
Joined
Dec 13, 2010
Messages
229
Code:
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


Code:
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

wut

Banned
Joined
Dec 13, 2010
Messages
229
I see this possible solution, but I'd rather not use formulas. I just want a plain text list.

Any suggestions?
 

wut

Banned
Joined
Dec 13, 2010
Messages
229
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:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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

Banned
Joined
Dec 13, 2010
Messages
229
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?
 

wut

Banned
Joined
Dec 13, 2010
Messages
229
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

Banned
Joined
Dec 13, 2010
Messages
229
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,590
Messages
5,637,285
Members
416,963
Latest member
samfuge

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 MrExcel.com.
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 "mrexcel.com".
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
Top