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 ?
Reference a Table in another workbook
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Reference a Table in another workbook
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...
I hope it helps.
I have used it successfully to transfer large data blocks very quickly and effectively.
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 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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Reference a Table in another workbook
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 ?
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 ?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Reference a Table in another workbook
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Reference a Table in another workbook
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.
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.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Reference a Table in another workbook
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!)
(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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Reference a Table in another workbook
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.
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.