Dynamic Pathnames for External Links

Hi Guys,

Here's something im at a dead end with.

I am designing a spreadsheet for work.

each of the people i work with have their own workbook with their name as the title, John Smith.xls.

There are about 20 of us on the team with their own workbook with the same design.

We record information on these weekly and so there are 20 files in the folder for Week 1, 20 in Week 2 etc.

I want to make a master spreadsheet with will collect all of the information on these sheets and list them on a master spreadsheet every week.

So the external data i am linking to is found in a location like

='C:\Spreadsheets\Week26\[John Smith.xls]Sheet1'!$A$1

Here is my problem,

I need the 'Week 26' and the 'John Smith' to be changeable and preferabilly linked to a cell using the INDIRECT formula so I can change all references to Week 26, 27, 28 etc and also change the workbook name it is referencing.

I have attempted to do this in the following way

I broke down the pathname to seperate cells

Cell 1: 'C:\Spreadsheets\
Cell 2: A reference linking to a cell containing value: Week 26\
Cell 3: A reference linking to a cell containing value: [John Smith.xls]
Cell 4: Sheet1'!$A$1

I then used the CONCATENATE formula to link these into a single cell as a text string.

I then tried to use INDIRECT to link to the filemane in the CONCATENATE cell.

This did not work.

I need a way to make parts of the filename variable and linked to one cell.

I also need to do this while the other workbooks are closed.

I have found some information on INDIRECT.EXT which as far as I can make out involves installing mods to Excel to make these work, I cannot do this as the spreadsheet will be used across our network and will eventually be used by thousands of users.

Please help me find a solution to this problem as it is starting to give me a headache!

Any help would be muh appreciated!

Hi

INDIRECT does not work with closed workbooks (as you have discovered).

One way (if you don't want to use external addins) is to build a macro that will create the formulas based on the data. You will need to have the source cells (which you have) and know where the formulas have to go and then run a macro to create the formulas.

If that is an option, then come back and we can help you create the macro if required.

rylo

Originally Posted by rylo

Hi

INDIRECT does not work with closed workbooks (as you have discovered).

One way (if you don't want to use external addins) is to build a macro that will create the formulas based on the data. You will need to have the source cells (which you have) and know where the formulas have to go and then run a macro to create the formulas.

If that is an option, then come back and we can help you create the macro if required.

rylo

Rylo this sounds very interesting. what are you thinking? what knd of formulae do you propose?

many thanks

Hi

Say the range A1:A4 contains the strings
C:\Spreadsheets\,Week 26\,[John Smith.xls],Sheet1'!$A$1
(note there is no ' before the first string - this will be input by the macro.)

Then run the code

Please Login or Register  to view this content.

and it will create the linked formula in C1.

rylo

Hey Rylo,

The Macro worked great, I can def use this to link to the unopened workbooks.

There is a lot of data that will need to be collated all on the same workbook, Is there a way to change the macro to fill out a larger section automatically.

with this formula i would have to fill the rest of the cells manually for each workbook.

eg to fill the section A1:L300 from the same reference in workbook 1,

and underneath the same reference (A1:L300) from workbook 2 etc.

It may need separate push buttons running on seperate macros but ideally one button to fill out all Workbook, (i could allocate a set number of cells to each external workbook (300 rows workbook 1, same for workbook 2 etc) It doesn't matter too much if there are gaps with empty rows but is it do-able?

As an added thought, I could allocate 300 rows to each workbook ont he master sheet and have a row which will have the Workbook references (like A1, A2, A3, A4 in your macro) then one button which will use these to fill out the 300 rows beneath?

All your help is much appreciated

Hi

See if the attached file gives you some direction.

rylo

Hi

Updated the code with some comments. If this doesn't clear it up, don't hesitiate to come back with questions.

Please Login or Register  to view this content.

rylo

hi Rylo,

Ok i've been trying to get this up and running at work, had it all running in the test folder but now i tried to link it to live databasses and it is giving me a runtime error on this line:

OutSH.Cells(outrow, 1).Formula = "='" & ce.Value & ce.Offset(0, 1).Value & ce.Offset(0, 2).Value & ce.Offset(0, 3).Value

here are the mods i tried to make:

Tried to the cells that contain the Externam Reference paths need to be taken from column A - G, to make up a full pathname:

V:\D2\DF40-Shared\Payments\PA Demand Analysis\WC 23.06.08\Bryan Sarll''s Team\[Matt Coles.xls]Sheet1'!$B$3

Also the First outpu cell is A3 which i did successfully.

I also managed to attach a bit of coding to the end which sorted the data for me.

her eis the code i used, (probabilly doesnt make much sense to you but let me know if you have any questions)

again any help greatly appreciated!

hi Rylo,

Ok i've been trying to get this up and running at work, had it all running in the test folder but now i tried to link it to live databasses and it is giving me a runtime error on this line:

OutSH.Cells(outrow, 1).Formula = "='" & ce.Value & ce.Offset(0, 1).Value & ce.Offset(0, 2).Value & ce.Offset(0, 3).Value

here are the mods i tried to make:

Tried to the cells that contain the Externam Reference paths need to be taken from column A - G, to make up a full pathname:

V:\D2\DF40-Shared\Payments\PA Demand Analysis\WC 23.06.08\Bryan Sarll''s Team\[Matt Coles.xls]Sheet1'!$B$3

Also the First outpu cell is A3 which i did successfully.

I also managed to attach a bit of coding to the end which sorted the data for me.

her eis the code i used, (probabilly doesnt make much sense to you but let me know if you have any questions)

again any help greatly appreciated![QUOTE=freud1]

Originally Posted by freud1

Sub LinkMacro()
'
' LinkMacro Macro
' Macro recorded 24/06/2008 by Levi
'
Dim OutSH As Worksheet
'assign output sheet to variable
Set OutSH = Sheets("Sheet1")
'initialize the data output row
outrow = 3
'nominate the data action sheet
With Sheets("LinkDetails")
'loop through the items in column A of the data sheet
For Each ce In .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
'insert the formula into the first output cell
OutSH.Cells(outrow, 1).Formula = "='" & ce.Value & ce.Offset(0, 1).Value & ce.Offset(0, 2).Value & ce.Offset(0, 3).Value & ce.Offset(0, 4).Value & ce.Offset(0, 5).Value & ce.Offset(0, 6).Value
'autofill the first column output block with the formula
OutSH.Cells(outrow, 1).AutoFill Destination:=OutSH.Range(OutSH.Cells(outrow, 1), OutSH.Cells(outrow + 299, 1))
'increment the output row
outrow = outrow + 300
Next ce
End With
'activate the output sheet
OutSH.Activate
'determine the last row of formulas
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
'autofill from column A across to column L
Range("A3:A" & lastrow).AutoFill Destination:=Range("A3:H" & lastrow)
'
'
Range("A2:H3002").Sort Key1:=Range("H3"), Order1:=xlDescending, Key2:= _
Range("A3"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

Hi

1) can you put the formula to a string

Please Login or Register  to view this content.

then use either the debug window, or debug.print to see exactly what is in the string. Make sure the path is really correct and hasn't included anything it should have, or excluded something.

2) I'd look closely at the part with the '' (Bryan Sarll''s Team). Maybe change that directory name to remove the ' and see if that fixes things up. Those types of directories can be tricky to code.

rylo

thanks rylo,

it was the apostrophe that was doing it, took them out and it worked like a charm!

I am trying to do a similar thing as Freud, but when I put this code into the VBA module it returns the error Run-time error '-2147417848 (80010108)': Method 'Formula' of object 'Range' failed.

I am using Excel 2007, any thoughts?

Originally Posted by rylo

Hi

Say the range A1:A4 contains the strings
C:\Spreadsheets\,Week 26\,[John Smith.xls],Sheet1'!$A$1
(note there is no ' before the first string - this will be input by the macro.)

Then run the code

Please Login or Register  to view this content.

and it will create the linked formula in C1.

rylo