Reference a Table in another workbook

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Reference a Table in another workbook

Post by syswizard »

I tried to simple perform an Insert, Table in one workbook by simply selecting another table in a separate workbook.
Excel threw some strange error. I entered the table reference directly in the RefEdit box.
(I should have taken a hint when the RefEdit control would not permit access to another workbook !)

This is a huge issue IMHO...being able to reference a dynamic table in another workbook.

The workaround was awful:
You have to select the entire table range in the current workbook where you want the "copy" and the range must "match" the source table range:

1) Go to the other workbook, determine the entire range of all of the cells in the table to be "copied" or referenced.
2) Select that range in the current workbook in the worksheet of interest.
3) Enter the formula in the formula bar: =[Book2]Sheet1!Table1[#All] and then array-enter it via Ctrl-Shift-Enter

Whew...what a hassle for large tables. Is there an easier way ?

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Reference a Table in another workbook

Post by Rudi »

See if this code can help.
It's designed to copy LARGE blocks of data to other locations. I just played around with it to convert the destination to a table.
You can tweak it as needed to remove the range input boxes since it seems you want to integrate it into a form with a RefEdit control...

Code: Select all

Sub CopyTable()
Dim screenUpdateState As Boolean, calcState As String
Dim eventsState As Boolean, statusBarState As Boolean
Dim displayPageBreakState As Boolean
    'Get current state of various Excel settings
    screenUpdateState = Application.ScreenUpdating
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    statusBarState = Application.DisplayStatusBar
    displayPageBreakState = ActiveSheet.DisplayPageBreaks    'Sheet-level setting
    'Turn off some Excel functionality to run code faster
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
    ActiveSheet.DisplayPageBreaks = False    'Sheet-level setting
    
Dim DataRange As Variant
Dim rSource As Range, rDest As Range, lR As Long, lC As Long, sWN As String
    On Error GoTo EH:
    Set rSource = Application.InputBox("Select a cell in the range to copy.", Type:=8)
    Set rSource = rSource.CurrentRegion
    Set rDest = Application.InputBox("Select first cell in range to paste.", Type:=8)
    'Read all the values at once from the Excel grid, put into an array
    DataRange = rSource
    'Writes all the results back to the range at once
    rDest.Resize(RowSize:=rSource.Rows.Count, ColumnSize:=rSource.Columns.Count).Value = DataRange
    sWN = rDest.Parent.Name
    Worksheets(sWN).ListObjects.Add(xlSrcRange, rDest.Cells(1).CurrentRegion, , xlYes).Name = "Table1"

Reset:
    'After your code runs, restore state
    ActiveSheet.DisplayPageBreaks = displayPageBreakState    'Sheet-level setting
    Application.DisplayStatusBar = statusBarState
    Application.EnableEvents = eventsState
    Application.Calculation = calcState
    Application.ScreenUpdating = screenUpdateState
    Exit Sub
EH:
    Resume Reset
End Sub
I hope it helps.
I have used it successfully to transfer large data blocks very quickly and effectively.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Reference a Table in another workbook

Post by syswizard »

Thanks for that Rudi....nicely done.
However, that's a static approach...I think....and therefore that operation must be done every time the data changes
and I would prefer dynamic.

Or am I wrong and the new table will dynamically re-size based on the source table ?

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Reference a Table in another workbook

Post by Rudi »

The data range of the destination is calculated dynamically based on the source dimensions, however, since the data is transferred via a virtual array, it is entered into the destination range as static data. Are you looking for the destination to explicitly contain a formula linking to the value in the source table?
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Reference a Table in another workbook

Post by syswizard »

Essentially I am looking for the ability to point to an external table, that's all.
If the source table has 2000 rows x 100 columns, then the reference table should have 2000 rows x 100 columns.

If the source table goes to 1500 rows, the reference table should be at 1500 automatically...without running a macro, etc.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Reference a Table in another workbook

Post by Rudi »

I'm not sure if what you are needing is possible?
(But then again, Excel has surprised me too many times in the past to be confident of this statement!)
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Reference a Table in another workbook

Post by syswizard »

Well, my approach is only workable if the reference workbook has defined a reference for ALL ROWS and ALL COLUMNS with the array-entered table reference.
When you do that, you get #NA's of course, but it does effectively dynamically resize when the source workbook is opened.
BUT WOW IS IT EVER SO SLOW.
So running a macro like the one you created upon workbook open would be the way to go.