linking workbooks to create a list

Microsoft Microsoft office professional...
April 6, 2010 at 11:52:21
Specs: Windows XP
i would like to create links between workbooks so that the destination workbook places the data in a list from different source workbooks. i.e. I will start with a generic template "Template Approval Form". I will fill out all information, save it as "Smith Approval Form". I will open up "Template" again, save it as "John Approval Form". I would like information from "Smith" and John" Approval Forms to be linked into "Tracking Database" destination workbook and placed in a list. Is this possible?

See More: linking workbooks to create a list

April 6, 2010 at 13:40:50
re: "Is this possible?"

The easy answer is "probably".

However, we are going to need some more details.

For example, what exactly do you mean by "the destination workbook places the data in a list from different source workbooks"?

Since your source workbooks appear to some kind of "form template", I assuming you don't want an exact replica of each form in the "Tracking Database" destination workbook. (Actually, that would probably be the easiest to do...just create a copy of the template and add it as a new sheet to the Tracking Database workbook.)

So if you want a "list", you'll need to tell us where the data is coming from (layout wise) and where it's going to (layout wise).

Do you see what I'm getting at?

Report •

April 6, 2010 at 13:50:41

If you have your Workbook "Tracking Database.xls" open,
Open another workbook, e.g., "Smith Approval Form.xls"
Right-click on the cell to link to, say A1 on Sheet1 and Copy.

Back in the Tracking Database.xls workbook, select the cell to hold the information, right-click and select Paste Special ...
Then click the Paste Link button.
The cell will show the linked data.
It will contain this formula:
='[Smith Approval Form.xls]Sheet1'!$A$1
and when "Smith approval Form.xls" is closed, the cell will also contain the path to the workbook.


Report •

April 6, 2010 at 15:52:27
Thank you for your responses, however I'm afraid I'm going to make it a bit complicated.
to give you a bit of background:
I will be reviewing at least 500 applications per year, all using the same template "Template Approval Form", but once information from each application is entered the file will be re-saved with the applicant's last name i.e. "Smith Approval Form". I can't simply continue to add sheets as the file would quickly become too large and unweildy; hence a separate file will be created for each applicant.
The "Tracking Database" is another workbook where certain details from each application will be entered in a list. For example, let's say cell D5 of each approval form will contain the birth date of each applicant. I want to have the birth dates of all applicants placed in column A of the "Tracking Database".
Simply pasting the link will not work, as the source workbooks, while all starting as "Template Approval Form", will be saved to different names. Therefore I would have to copy the link every time I save a new file name; in that case, I might as well just enter the info again.
Another problem is the list part; I don't know how to place a formula for linking workbooks in a list. You can type january, february, march, highlight those cells and drag down so it fills in the rest of the months, but that doesn't work to link multiple workbooks i.e. example1.xls, example2.xls, etc.

Report •

Related Solutions

April 7, 2010 at 06:10:06
Just for clarification:

You open your Template.
(Template Approval Form)

Then you fill in *various details* and save it.

But you want those *various details* to be available in a Database.
(Tracking Database)

If this is what you want, then:

Under "Data", look for "Template Wizard".
If it isn't there, click on "Tools", then on "Add-Ins".

Tick the following boxes:
Template Utilities
Template Wizard with Data Tracking

Click on OK and restart Excel.

Copy your Template.
(You will overwrite your original Template when saving.)
(Make a copy of your Database as well.)

Open your Template Copy.
Unprotect any/all sheets that will be used.

Start the Template Wizard and follow the prompts.

When done, it will ask you for a Save Name.
You can overwrite your Original Template.
Or use a different Name if you want.

Make sure you save it as a .xlt


When you need to create a new File, simply double click your Template.
The records will be saved to / updated in the Database when you Save your File.

Report •

April 7, 2010 at 08:17:59
I don't have either the Template Wizard under Data or the add-ins that you specified.

Report •

April 7, 2010 at 16:37:55

Here is a zip file with those addons:

Just extract it to somewhere on your pc.

In Excel:
Tools --> Add-Ins
Click on "Browse".
Browse to the extracted Add-Ins.
Select one and click on OK.
Browse again.
Select the other one, and click on OK again.

That should load the Add-Ins into Excel.

Report •

April 9, 2010 at 06:04:43

As an alternative, here is a macro which uses the name of every xls file in a specific directory/folder.
It then creates a series of link formulas to specified cells in each of the workbooks in that directory/folder.

The links to each workbook are created on successive rows in the Database workbook.

In this example links are created to cell "D5" on "Sheet1" in every xls file in the directory "D:\Current Data"

Additional links can easily be created.

The macro also saves the Database workbook with today's date at the end. If you want it to just save with the current filename, change this line to False:

'True is Save with date
'False is Save with current name
blnSave = True

On the Destination Worksheet in the database workbook, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Update Links' or something else suitable.
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit
Private Sub CommandButton1_Click()
Dim rngDestStart As Range
Dim objFileSys As Object
Dim objFldr As Object
Dim objFile As Object
Dim strPath As String
Dim blnSave As Boolean
Dim intDestOffset As Integer
Dim strSrcBase As String

On Error GoTo ErrHnd:

'set save option
'True is Save with date
'False is Save with current name
blnSave = True

'start row for results/links
Set rngDestStart = ActiveSheet.Range("A2")
'set destination row offset counter
intDestOffset = 0

'path to folder with source workbooks
strPath = "D:\Current Data"

'create a file system object and then a folder object
Set objFileSys = CreateObject("Scripting.FileSystemObject")
Set objFldr = objFileSys.GetFolder(objFileSys.GetFolder(strPath).Path)

'get each filename in folder
For Each objFile In objFldr.Files
    'test if it is an xls file
    If Right(objFile.Name, 4) = ".xls" Then
        'Create a link to source workbook cells in the format:
        '=[D:\Current Data\ThisWorkbook.xls]'Sheet1'!D5
        'start by creating all the formula except the cell address
        strSrcBase = "='" & strPath & "\[" & objFile.Name & "]Sheet1'!"
        'now create the first link and place it in column A in the next row
        rngDestStart.Offset(intDestOffset, 0).Value = strSrcBase & "D5"
        'increment destination row counter
        intDestOffset = intDestOffset + 1
        'add additional cell links here
        'rngDestStart.Offset(intDestOffset, 1).Formula = strSrcBase & "E6"
        'note - increase column offset for each cell linked
        'in the Offset(row, column) statement
    End If

'save with current name or save with date added
If blnSave = True Then
    'save workbook with today's date e.g., "Name dd-mm-yy.xls"
    If IsDate(Left(Right(ActiveWorkbook.Name, 12), 8)) Then
        ActiveWorkbook.SaveAs _
                Filename:=Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 12) & _
                Format(Now, "dd-mm-yy") & ".xls"
        ActiveWorkbook.SaveAs _
                Filename:=Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & _
                " " & Format(Now, "dd-mm-yy") & ".xls"
    End If
    'save workbook with current name
End If
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Change the Directory/Folder path for your source xls files, here:

'path to folder with source workbooks
strPath = "D:\Current Data"

Add additional cell links by adding more of this line:

rngDestStart.Offset(intDestOffset, 1).Formula = strSrcBase & "E6"
using a new cell address and incrementing the number ', 1)' to ', 2) etc. for each cell linked.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Click the 'Update Links' button to run the macro.


Report •

Ask Question